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

Question

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?

Solution 1

Unfortunately, no.

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

Solution 2

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.

Solution 3

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/

Comments

[Robin Day]

Thought as much, but the move is a good idea! I can actually restore the log file to somewhere else shrink it and move it back.

[gbn]

This would fragment the production LDF = not a good idea...