SQL Server – Cannot Restore Database Due to Stuck Rollback

Yeah, you need that .ldf file. In the future, don't delete that.

If your database is running in full recovery, you need to run log backups regularly. "Regularly" is a term that depends on your particular environment, but hourly during business hours is a common choice. Basically, it only purges the transactions out of the log file when it's backed up. If it's never backed up, the log grows until it consumes the entire disk, as you've seen.

In the future, set the database to simple recovery, shrink the transaction log, and then back it up immediately, as NathanC suggested.

As for now: I don't know what kind of backups you have beyond the one you restored from, but I agree with NathanC that you need to let it sit and wait for the restore to finish. It's chugging through that log file. Once it finishes, truncate the log and set it to simple like your other databases, or implement log backups.

That won't work. As soon as you recover the database after restore, that begins a new timeline for the database.

If this is an on-going process, one possible solution is to get the team generating the backups to take a differential every day which will have fairly low impact on effort and system load. This is in addition to what they currently have. However, it will require some careful management of the files to ensure you have the right sequence of full backup, differential and tlogs. The benefit is, you will not have more than the 20 tlog files to apply if you ever need to restore the database to the latest version. You also don't lose existing PITR based on the 15-20 tlog backups taken daily (I'm assuming there is some PITR requirement given the number of log backups).

If you want to further reduce the number of log files to apply, you can increase the number of differential backups per day but I'd wager the added complexity in managing the files far outweighs the benefits. If you script all your tasks (sounds like you are), execution and scrutinizing for issues is fairly easy to do. If you're using the SSMS GUI, well, you really shouldn't.

** To make it clear, this assumes you already reset the backup chain with a full database backup at some interval. Having just a single full DB backup and only taking differentials and tlog backups from there on is not a viable solution. In most situations, the differential will eventually grow to a large size, possibly bigger than the base (full DB) backup if you have lots of write activities. An example of a basic backup cycle might look like: - Full DB backup - every Sunday 2am - Differential DB backup - every day - 8pm Transaction log backup - every 15minutes This cycle has potential data loss of up to 15 minutes if there are no other protections (e.g. AlwaysOn) in place.

If this is just a 1-time thing, i.e. you won't get any more tlog backups then you could reduce your recovery time when the goal is to get to the latest point-in-time available. Just restore the DB, differential and apply all the logs. Once done, take a backup of that and keep it along with all the other backup files you have. This buys you rapid recovery when you need to get to the latest version available since you only need to do a single DB restore. However, don't delete all the other backup files in case you need to restore to a specific point-in-time other than the most recent.