8

I have a backup that's about 6Gb. It's a "light" backup of the original (with the log tables purged), which is about 14Gb.

I try to restore the backup on my SQL Express local server. It fails with a message like : System.Data.SqlClient.Error: insufficient disk space. It asks for 227,891,019,776 bytes, which is absolutely crazy, and almost as big as my whole hard drive.

As found on other sites, I tried RESTORE FILELISTONLY FROM DISK = 'backupfile.bak'.

The data file (column Size) is 6,888,226,816 in size, but the log file is 221,006,987,264. The column BackupSizeInBytes returns 6,259,736,576 and 0.

So, if I understand correctly, restore checks that I have enough space to restore the "theorical" size of the log file before proceeding, disregarding the actual log file size ?

How can I bypass that ? It's a bit difficult to get a backup, so if I can resolve my problem without having to return on the production server, it would be great.

Thank you !

Oh BTW, I am on SQL Server 2008 R2 Express.

Aaron Bertrand
  • 181,950
  • 28
  • 405
  • 624
thomasb
  • 193
  • 1
  • 1
  • 9

4 Answers4

8

Note that the backup size does not include empty pages, but when you actually perform the restore, the data and log files will be over 200 GB, because it has to restore exactly what the source system had (including a 200+ GB log file, regardless of how full it was).

If you don't want to risk data loss, you need to correct that at the source (e.g. shrink the log file to something reasonable), take another full backup, and restore that. I'm not sure I understand why it's difficult to get a backup - this is a pretty standard operation, and should be a service provided by anyone you're paying to host SQL Server.

You should also fix the source database to either (a) be in the right recovery model or (b) take log backups more frequently. Your log file is ludicrous because you are in full recovery and never take log backups. If you need point in time recovery, start backing up your log. If you don't, switch to simple. The log file should manage itself if you have it configured correctly. If you don't, then when it gets like this, shrinking should be a one-time operation, and then you should fix the configuration so you're not doing this again next week...

Aaron Bertrand
  • 181,950
  • 28
  • 405
  • 624
3

DBCC TRACEON(3104) will bypass disk space checks for restore processes.

Nic
  • 4,063
  • 1
  • 16
  • 22
1

There hadn't been any transaction log backups, to allow the log to be re-used. So it has to get all of that empty log to get to the parts where there may be some transactions it needs to recover.

So assuming you're prepared to suffer some (possibly quite a lot of) data loss, then you could recover it without the log. This is not recomended.

Here's a link to the broad topic of SQL Server database recover, http://www.sqlskills.com/blogs/paul/category/disaster-recovery/. You are not alone: http://www.sqlskills.com/blogs/paul/search-engine-qa-23-my-transaction-log-is-full-now-what/. Finally, to a few commands that may help in this situation, again not recomended (you will almost certainly suffer data loss) http://blog.sqlauthority.com/2010/04/26/sql-server-attach-mdf-file-without-ldf-file-in-database/.

Grignar Grenac
  • 99
  • 3
0

If you still have the original database online. and, you don't have log-shipping, replication or make any use of your transaction log backups. Then you can run ALTER DATABASE {name} SET RECOVERY SIMPLE, that empties the transaction log (which would later allow you to shrink that file). Then run BACKUP DATABASE {name} TO DISK='{file}'. Copy the file to your new server and RESTORE DATABASE {new_name} TO DISK='{file}'. You may need to re-map the logical files to new locations, using WITH .. MOVE {x} TO {new_file}, see URL http://msdn.microsoft.com/en-us/library/ms177429.aspx for some more syntax. After the backup you can run ALTER DATABASE {name} SET RECOVERY FULL, to return the original database to using the full recovery model (you should also do this for your new copy). You may also find the following useful if these steps goes awry: http://www.sqlskills.com/blogs/paul/category/backuprestore/.

If you wanted to free up space on disk to put the new backup on (after the SET RECOVERY and before the BACKUP). You should execute use {database_name}; EXEC sp_helpdb {database_name} then look in the result for the row with column that has usage value "log only". Then use that as the value for {filename} in dbcc shrinkfile('{filename}', 1024), which should reduce the .LDF file to 1GB (i.e. size is in MB). DO NOT USE shrinkfile on datafiles, only log files, see Paul Randall site referenced above for more details on the latter.

billinkc
  • 16,143
  • 4
  • 54
  • 89
Grignar Grenac
  • 99
  • 3