Restoring from backup but want a new transaction log

[bessebo]

I have always restored from our production environment into our test environment but now I am running into a space issue. Our transaction log in our production environment is about 50GB because it needs that much every time we rebuild indexes on the weekend.

In our test environment I don't rebuild indexes and I really don't care about the transaction log so I want it to be small.

How can I restore from my full backup but have a small transaction log? Every time I try to restore the database it says that there is insufficient space on the drive that the transaction log is supposed to reside. Any help would be appreciated.

Regards,
Bessebo

 

[mrdenny]

You can't. In order to restore the database you have to restore it to the origional size that it was when the backup is taken. The only way to do this would be to shrink the production files backup the database, then expand out the production files again.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[bessebo]

Thanks. That is what I thought. I appreciate your help.

Regards,
Bessebo

 

[SQLBill]

You COULD...detach the database, copy the .mdf file, move the copy to the new server. Re-attach the original database. On the new server attach the copied file using sp_attach_single_file_db. This command is for attaching a data file when the log file is missing. It's supposed to create a new, empty one. It does have its problems some times.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[bessebo]

Thanks SQLBill. I will keep this option up my sleeve for future reference...