Database Recovery Stuck on "Restoring"

Question

Scenario:

• Sql Server Database routinely backed up with Backup Statement below.
• The "Recovery Model" was set to "Full" (we usually have them set to "Simple").
• The ldf file, which had gotten quite large, was deleted using Windows Explorer (after first stopping Sql Server).
• ldf file deletion did not go into recycle bin because it was too large.

BACKUP DATABASE MYDATABASE to disk = 'E:\BACKUPS\BACKUP.BAK' WITH FORMAT, MEDIANAME = 'MYDATABASE', NAME = ' FULL MYDATABASE BACKUP'

We are now trying restore one of our backups (200MB file size). The restore progress proceeds at expected rate. But it sticks at 100%, and the database status remains Restoring. We notice that once the progress hits 100% the mdf file is approximately the right size. And the ldf file shows 60GB filesize. This was about the size of the file when it was deleted. But obviously this file did not come out of the 200MB bak file.

Is there anything we can do? I am not a Sql Server DBA, but I presume the problem was we were not backing up a "Full Recovery" database properly.

Answer 1

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.