up vote 0 down vote favorite
share [g+] share [fb]

In searching the ServerFault for situations similar to mine, I can't find one.

We have a "training" and a live database for an application. The "training" database is used for development and experimentation. I frequently backup the live database to a bak file then restore that database to the "training" database to refresh the data. (There is no issues in doing this as it is vendor recommended.) Could I eliminate that step by restoring from the live database to the "training" database?

Restore from Live

link|improve this question
82% accept rate
feedback

1 Answer

up vote 1 down vote accepted

Short answer is no. You could do something with T-SQL to automate this for you, or you might be able to do something with replication or mirroring to meet the requirements. These are all good options to look into, but you can't get there in one step via backups. You could also script the backup/filecopy/restore, which would make the process easier - as long as your major complaint isn't available disk space for your BAK file or the time it takes to transfer it.

Further details for the short answer - if you look up the syntax of the BACKUP DATABASE T-SQL command, you see this:

backup_device = { { logical_device_name | @logical_device_name_var } | { DISK | TAPE } = { 'physical_device_name' | @physical_device_name_var } }

That backup device can be one of two things- DISK (file), or TAPE. No option exists for backup to another database.

link|improve this answer
Thank you, the process isn't difficult nor takes very long. But anything that cuts down time and steps is better in my eyes. So I had to ask. – Mike Wills Mar 3 '10 at 20:14
Yeah, then definitely look into scripting it. You are already taking nightly backups of the live DB, perhaps via a Maintenance Plan? If not, start doing so :-) Then, you can have a scheduled task to copy the BAK file if the live and test DBs live on different servers. Then you can also script a restore task - either do it in a maintenance plan, or write a T-SQL or sqlcmd script and call it via a Scheduled Task. You can also add error-checking, verifying that the file is from the correct date, etc. Do a lot of testing! – mfinni Mar 3 '10 at 20:20
feedback

Your Answer

 
or
required, but never shown
discard

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