Restoring a backed up database fails

Question

I have backed up a database (full backup) and was attempting to restore as a new database (different name). However when attempting to restore it I get the following error:

The transaction log for database 'L-Test' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases.

Not really sure why that's happening. The size of the database is only 37.50MB and the logfile is set to auto grow as well.

Answer 1

Keep in mind that the backup does not necessarily reflect the size of the database (including the log) on disk - even if compression is not used.

You are likely trying to expand a log file that matches the size on the original database, and there is not enough space on the disk. You should check the size that the restore operation is trying to restore to looking at the size column for the files in the following query:

RESTORE FILELISTONLY FROM DISK = 'path:\to\file.bak';

This may or may not match the current size of the original source database, depending on when the backup was taken and what type of operations have been performed against that database in the meantime.

If you don't have that much space to spare on the disk you're trying to restore to, you have multiple options:

1. free space on the drive;
2. use WITH MOVE to place the data and/or log files on a different drive with sufficient space; or,
3. shrink the log file on the original database (this may require performing a log backup or a checkpoint to be effective, depending on recovery model) and then take a new, COPY_ONLY backup and restore that.