Home / server / Questions / 293001
Accepted
Robin Day
Asked: 2011-07-23 01:59:07 +0800 CST

SQL Server 2008 - Restore database without restoring transaction log (LDF)

  • 772

I have a production database with Full recovery mode where the MDF and LDF files are approx 80GB each.

We have a test server that we restore this database to on a regular basis for dev / test purposes, etc.

At the moment the restore has happened, we set the recovery model to Simple and shrink the log file as the server is low on disk space.

Is there a way of restoring the database straight to Simple mode so that the large LDF file is never created?

sql-server sql-server-2008 restore

3 Answers

  1. Best Answer

    Unfortunately, no.

    The best you can do is move the file: not resize it and not change any db settings

    • 2

  2. You could always script the log file to be shrunk down to nothing out of hours, then take a backup, and then resize back. Whether that works depends on the load on the server during this operation.

    One alternative is to use a third party tool such as Red Gate SQL Compress on your dev/test server - though adding more storage may be more standard:

    http://www.red-gate.com/products/dba/sql-storage-compress/

    • 1

  3. One thing you could do is attach just the MDF by itself. that is, if you have a copy of the mdf somewhere detached already:

    http://msdn.microsoft.com/en-us/library/ms174385.aspx

    the sp_attach_single_file_db command can be used to attach the database from an mdf and creates an empty ldf for you. in order to get a copy of the mdf, you'd have to either restore the original and detach it (might defeat the purpose?) or take the original offline and copy the mdf. so, perhaps it might not fit your scenario, but it might help others in a similar situation.

    • 1