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 need to stop write transaction logs when i restore a database. I need to do this because i restore a production database into a test database. I do this each day.. the problem is that transaction logs become very big! Is it possible to restore a database without write the transaction logs ? Thanks

share|improve this question

migrated from stackoverflow.com Nov 16 '11 at 18:27

This question came from our site for professional and enthusiast programmers.

    
You cannot entirely stop SQL Server's transaction log. No way. What you can do is put your database into simple recovery mode once it's restored. That creates a lot less transaction logs (but it still does create some). –  marc_s Nov 16 '11 at 16:59
    
Given that the "test database" is on the programmer's PC most likely being used for development, not on a production server, I think this question may be on-topic. –  Ian Ringrose Nov 16 '11 at 17:04
add comment

2 Answers

up vote 2 down vote accepted

Why are the transaction logs so big? It means the transaction logs are big in your production server, and this is what you need to pursue. They may be not been backed up properly, or they may had grown once accidentally and now are huge file but not in use, or it may be that they really have to be that big? Which one is it?

share|improve this answer
add comment

As others have said, you can't restore the database without also restoring the log files to their original sizes at the time the backup was made.

You can, however, modify your restore script to also set the database to simple recovery, and then shrink the log file considerably. I do this in my scripts for reloading our test databases. It ends up looking like this:

ALTER DATABASE database_name SET RECOVERY SIMPLE
USE database_name
DBCC SHRINKFILE('log_file_logical_name', 64)

Use sp_helpfile if you need to check the logical name of the log file.

share|improve this answer
add comment

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.