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:

  1. back up database ABC (call it abc.bak)
  2. drop (or clean/clear) database abc_test
  3. restore abc.bak into abc_test

Please let me know if anyone know or have a script that does so.



2 Answers 2


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

--First we back up the DB        
        NAME = N'ABC-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
--Then we restore the DB
        RESTORE DATABASE [ABC_test] FROM  DISK = N'C:\temp\ABC.bak' WITH  FILE = 1,  MOVE N'ABC' TO N'C:\DBFiles\ABC_test.mdf',  
        MOVE N'ABC_log' TO N'C:\DBFiles\ABC_test_log.ldf',  NOUNLOAD,  REPLACE,  STATS = 10

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)


There is no need to drop the current database and create a new one.

The RESTORE DATABASE command allows you to replace an existing database.

Another solution would be for you to create a snapshot of the current database (if you're using SQL 2005+). An example from MSDN:

CREATE DATABASE AdventureWorks2008R2_dbss1800 ON
( NAME = AdventureWorks2008R2_Data, FILENAME = 
'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\AdventureWorks2008R2_data_1800.ss' )
AS SNAPSHOT OF AdventureWorks2008R2;

The advantage of this solution is that is very fast to create a snapshot and doesn't take the full space size of the complete db. The disadvantage is that you need to take care of it so that it doesn't stay on the server indefinitely, because it will keep growing, as the source db will be updated. This snapshot database is read-only, so you won't be able to write data on it, only run reports, selects..etc. But is a fast solution when need to test something with prod data.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.