June 3, 2003 at 2:30 pm
We have a backup/restore process that basically moves a copy of our production database to another server every night. The issue is that we really only need the database and couldn't care less about the log file because we run the backup in a Simple recovery mode. So in order to save space, I'd like to find a way to restore the database and also truncate/shrink the log file. Will I have to shrink the Production log file before I restore in order to do this? Or can I do it in one step somehow?
Thanks
Darren
Darren
June 3, 2003 at 3:48 pm
First, I would care about the log file. It's easy to throw away if you don't need it, but if younever capture it, you may be asking for trboule.
second, if you are restoring, you don't need the log file.
Steve Jones
June 3, 2003 at 5:48 pm
We do care about it in production, but when we restore it on another server, we don't want it. So how do I do the restore without the log?
Darren
Darren
June 4, 2003 at 5:37 am
The answer depends on what you are actually backing up...
.mdf and .ldf files:
Use the sp_attach_single_db_file command to attach the .mdf
actual backup files created by BACKUP DATABASE command.
Use RESTORE DATABASE command with the WITH RECOVERY option. That will only restore the database. RESTORE LOG command is used to restore the database logs.
Refer to the Books OnLine, use the Index tab and enter RESTORE DATABASE, then select the TRANSACT-SQL choice.
-SQLBill
June 4, 2003 at 12:50 pm
I think the attach option will work for us.
Here is how I see it working
1) Backup our Production database
2) detach the previous day's Staging database (this is the restore of Production)
3) copy the .mdf from Production location to Staging location.
4) Re-attach the .mdf from production to staging database.
Do you agree that will work? I of course will be testing this with Northwind!
Thanks for all the help, I appreciate it.
Darren
Darren
June 4, 2003 at 12:56 pm
I would restore the production database and shrink the log file. Moving large MDF file can take a longer time.
June 4, 2003 at 1:49 pm
I might have to stick with that approach. I tried testing my approach with Northwind. It wouldn't allow me to copy the .mdf because it says it's in use. If that means I need to detach it to copy it, I can't do that. I'm not going to be able to detach production for any amount of time.
Thanks
Darren
Darren
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply