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.