1

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.

| improve this question | |
  • 1
    Well, the incorrect thing to have done was delete the log file..setting the recovery model to "Simple" and truncating the log would've done the trick. Give it some time to finish. – Nathan C Oct 7 '14 at 14:06
  • Duplicate of: stackoverflow.com/questions/520967/… – Evan Anderson Oct 7 '14 at 14:27
  • When you issued the restore command did you use WITH RECOVERY or WITHNORECOVERY? – Kris Gruttemeyer Oct 7 '14 at 14:56
  • It was through the interface, and we had checked the "WITH RECOVERY" option. – M Schenkel Oct 7 '14 at 18:18
  • 1
    Next time please do some research before just blindly deleting a file you don't know you need. – Aaron Bertrand Oct 15 '14 at 3:00
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.

| improve this answer | |
  • 1
    Yes - after running it for a long time it worked. To me it just seem hung up. Thanks all. – M Schenkel Oct 7 '14 at 23:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.