Tell me more ×
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.
up vote 9 down vote favorite

We have a bak file from a client that we have transferred to our developer offices for problem investigation. The backup is currently 25GB and the restored database is about the same size however it needs 100GB to be restored. I believe this is because there database is set up to have a 75GB transaction log size. After restoring the database we can shrink the log file but is there a way to do this in the restore?

share|improve this question
3  
That I am aware of you cannot alter the database until it has completed the restore. – Shawn Melton Sep 29 '11 at 1:41
2  
I have been in the same situation and I look forward to the answer. Just to comment, there is a product: red-gate's SQL virtual restore. I have never used this, but according to the site: 'The mounted database requires near–zero additional storage over the backup file'. – StanleyJohns Sep 29 '11 at 4:34

3 Answers

up vote 8 down vote accepted

There is no way to shrink the backup as a part of the restore process. The restored database must look exactly like the source database with the only exception being that you can change the drive letters and folders around.

share|improve this answer
OK, so who downvoted me and why? Please explain what you didn't like about my answer other than I told him he's screwed. – mrdenny♦ May 12 at 17:20
2  
Chill dude, go surfing or something. – FreshPrinceOfSO 2 hours ago
up vote 14 down vote

There is a do-not-ever-do-this-in-a-live-environment hack you can use where space is limited, by restoring the log file to a compressed folder. Attempt this by compressing an existing folder and restoring to it will result in an error, so you have to cheat with a symbolic link.

  1. Create a compressed folder D:\LogCompressed\

  2. Create a symbolic link to the compressed folder mklink /D /J D:\Log\ D:\LogCompressed\

  3. Restore your database with the ldf file pointing at D:\Log\

  4. Shrink the log file to an appropriate size

  5. Detach the database, move the log file to an uncompressed folder, attach

It's dirty, it's cheating, DO NOT EVER DO IT IN LIVE, but it works. Quick test of a newly created database with a 32MB log file shows it as occupying 330kb on disk when compressed, decompress the folder and on disk size is back to 32MB.

share|improve this answer
3  
+1 for emphasis of hackiness. I'd recommend this too – gbn Oct 3 '11 at 14:57
2  
+1 for living dangerously. Sometimes you just have too !!! – RolandoMySQLDBA Nov 4 '11 at 9:17
up vote 4 down vote

I believe that the reason your backup is 25 GB and the restored database is 100 GB is not because of your transaction log. What my guess is, your database files have 100 GB of allocated space and there's 25 GB of actual data in the database.

There's a difference between allocated database file space and utilized data space. In this case, the former is 100 GB and the latter is 25 GB.

share|improve this answer
The client allocates 75GB to their transaction log. After restoring I currently change the log allocation to 1GB. If possible I would like to restore this database to a server where drives have less than 100GB of free space. Without having to restore to another server, truncate, backup and restore again. – Adam Butler Sep 29 '11 at 1:52
@Adam Butler, would it be possible to restore to the first server temporarily so that you can shrink the file sizes, then make a backup of the copy and restore that on the destination server? – DForck42 Sep 29 '11 at 15:20

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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