How to stop write transaction logs when i restore a database

Question

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

Answer 1

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?

Answer 2

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.