Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

3 Answers 3

up vote 2 down vote accepted

Unfortunately, no.

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

share|improve this answer
    
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

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/

share|improve this answer
2  
This would fragment the production LDF = not a good idea... –  gbn Jul 22 '11 at 10:55

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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