Is it possible to restore sql-server bak and shrink the log at the same time?

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?

Answers 3

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

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

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

Related Questions