28

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?

Improve this question
2
  • 3
    That I am aware of you cannot alter the database until it has completed the restore.
    – user507
    Sep 29, 2011 at 1:41
  • 3
    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, 2011 at 4:34

3 Answers 3

Reset to default
16

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.

Improve this answer
0
34

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.

Improve this answer
1
  • 2
    @Mark : This hack works, but not perfect. Even if the log file takes far less space than its real size, you cannot restore database unless the disk really have free space as much as the real size of the database. Besize, while restoring, the actural size of the log file goes down slowly from the real size, its painful. However, its still a very good hack. Thanks.
    – chenwq
    Oct 11, 2013 at 3:11
9

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.

Improve this answer
2
  • 1
    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, 2011 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, 2011 at 15:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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