0

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?

2

Unfortunately, no.

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

  • 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. – Robin Day Jul 22 '11 at 10:45
1

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/

  • 2
    This would fragment the production LDF = not a good idea... – gbn Jul 22 '11 at 10:55
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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