11

I have a legacy database whose nightmarish lack of design I will not get into here, but the files on the server are (relatively) enormous. I have:

MyDatabase.mdf: 24.8GB
MyDatabase.ldf: 114.6GB

This database is backed up to a .bak file every night and shipped over to our reporting server, where it's restored. The .bak file is much smaller at only 1.8GB.

However, when I try to restore it on the reporting server, it fails due to insufficient space. There are around 100GB free on the server, and it's trying to eat up the entire 139.4GB that the files consumed on the original server. Unless my knowledge of compression is horrifically wrong, I'm fairly confident that the 1.8GB file is not actually expanding by 7400%.

My Question: Is there any way to tell SQL Server to restore this backup file without reserving that space upfront? I do not care about any of the logs; I just need the data to be there. I understand databases from the development and schema perspective, but I am by no means any sort of DBA.

This is on SQL Server 2008 R2. Thanks for any help or suggestions.

|improve this question

migrated from stackoverflow.com Mar 8 '12 at 23:12

This question came from our site for professional and enthusiast programmers.

11

The backup contains two pieces of information:

The actual data in the backup is those 1.8 GB. But the metadata describes the layout of files as on the original production server. While it looks like compression, is not actual compression. Is just metadata vs. content. the problem is that during RESTORE the reporting machine creates a layout as described in the metadata, therefore 114.6GB + 24.8GB. Now for the real question: can you restore it w/o recreating the original DB file layout? No. However, there is another solutions to your problem: bring the original DB back in check. Shrinking the Transaction Log covers the steps you need to do, since the log is your major pain point. Next I would deploy log shipping instead of backup/restore.

|improve this answer
  • Thank you; shrinking the transaction log did the trick perfectly. – eouw0o83hf Mar 8 '12 at 23:24
  • 1
    Log shipping in lieu of log backups?? – Thomas Stringer Mar 9 '12 at 3:24
  • 4
    @Shark: log shipping instead of full backups, as a mean to deliver the DB to reporting machine. – Remus Rusanu Mar 9 '12 at 4:00
  • Oh okay I completely missed that part of the question. Thanks for the clarification!! – Thomas Stringer Mar 9 '12 at 4:07
0

SQL Server 2012 allows to use just the database mdf file and to attach it to SQL Server: http://msdn.microsoft.com/en-us/library/ms174385.aspx. SQL Server creates a brand new ldf file where you can specify the size. Then:

  • use Simple recovery model to keep your log file from growing, or
  • use Full recovery model and make regular transaction log backups. This will also keep the log file from growing
|improve this answer

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.