Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

Thanks!

share|improve this question

migrated from stackoverflow.com Nov 11 '11 at 2:28

This question came from our site for professional and enthusiast programmers.

1  
any specific reason you want to drop the database instead of overwriting it? –  SqlSandwiches Nov 11 '11 at 3:15
add comment

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        
BACKUP DATABASE [ABC] TO  DISK = N'C:\temp\ABC.bak' WITH NOFORMAT, NOINIT,
        NAME = N'ABC-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
        GO
--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
        GO

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)

share|improve this answer
add comment

SqlSandwiches is right, there's 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;
GO

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.

share|improve this answer
1  
+1 for considering the size of the DB. –  SqlSandwiches Nov 14 '11 at 2:28
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.