I have a live database which i would like to create a backup and restore script that will take the "live" database every night, create a back, then drop the existing "testing" database (if any) and restore the same .bak file (on the same server) into a new database, this way, i always have the most up-to-date database to test on.
here are the steps:
- back up database ABC (call it abc.bak)
- drop (or clean/clear) database abc_test
- restore abc.bak into abc_test
Please let me know if anyone know or have a script that does so.
Thanks!
Best Answer
You could set up a simple job to backup the database to a single location and then restore it. It would look something like this
You might want to delete the file before you back it up. You could do this with powershell (my recommendation), xp_delete_file (undocumented SP), or xp_cmdshell (not sure if it is enabled on your system or not)