Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This question already has an answer here:

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.

share|improve this question

marked as duplicate by Mark Storey-Smith, Jon Seigel, Paul White, Marian, RolandoMySQLDBA Feb 10 '13 at 22:50

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

    
The log is needed for the restore to keep the database transactionally consistent. Have you confirmed the size of the log from the source database? –  Mike Fal Feb 6 '13 at 16:40
2  
Try the dirty hack described here to restore the log to a compressed folder. –  Mark Storey-Smith Feb 6 '13 at 17:07
    
My experience with NTFS file compression is that it works OK for small files, but once you start getting large (>1GB) performance drops off. (Many years ago, before built-in backup compression, I had a client who was backing up many GB to a NTFS-compressed location.) The "dirty hack" was tested with a 32 MB file. If @cosmo0's log file is indeed 220 GB or so, he may find that the restore never seems to complete. Or it might work great. Just a warning, YMMV. –  darin strait Feb 6 '13 at 22:45

4 Answers 4

up vote 2 down vote accepted

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...

share|improve this answer
    
Agreed. The original database needs to be cleaned up before they have any hope of restoring that database on an express version (10gb limit on 2008+ right?) –  DForck42 Feb 6 '13 at 21:03
    
I'm a new contractor in this company, and the SQL server is managed by... well, nobody, really. So it's a bit of an arduous process to get a backup. But I did it anyway : with a few shrinkdatabase, shrinkfile and backup log, it works. Thanks ! –  cosmo0 Feb 7 '13 at 10:33

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

share|improve this answer
    
How will skipping the check help? If he's trying to restore a 200 GB log and doesn't have the space, it'll just fail later. –  Aaron Bertrand Feb 6 '13 at 16:57
1  
OP asked how to bypass the disk space check. Just answering the question asked. Given the size listed (14GB) it won't restore to express anyway (10GB limit) –  Nic Feb 6 '13 at 18:12

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/.

share|improve this answer

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.

share|improve this answer
2  
Why do you have two answers? Could they be combined into one? –  Aaron Bertrand Feb 6 '13 at 17:11
    
2 radically different recovery scenarios. One where you have the space to create a new backup, the other you don't (or you may only have the mdf and ldf files and nothing else). The question doesn't have enough detail to decide between these basics choices of "recover some data at any cost" and "recover everything back to what it was, cleanly and assuming you have all the required resources". There are a lot of other intermediate options and variations (too numerous to mention / list), covered in the referenced web-sites. –  Grignar Grenac Feb 11 '13 at 14:37
    
To summarise, the old universal DBA caveat applies - it depends. –  Grignar Grenac Feb 11 '13 at 14:44
    
To me, it seems like you should have a single answer that meets the OP's criteria. If you don't know which scenario they're in, get clarification. Seems awkward to me to have two different answers to the same question. At the very least they could be combined into one answer with two sections. –  Aaron Bertrand Feb 11 '13 at 14:47
    
Addendum to above answer. 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. –  Grignar Grenac Feb 11 '13 at 14:53

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