Home / server / Questions / 118869
Accepted
Mike Wills
Asked: 2010-03-04 11:35:10 +0800 CST

Restore From Database instead of File

  • 772

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 http://dl.dropbox.com/u/2732434/CopyDatabase_hide.png

sql-server copying

1 Answers

  1. Best Answer

    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.

    • 1