I have a “Live” database and a “Development” database and occasionally, I want to replace the entire contents of the development database with the live data. Ideally, without taking the live database offline. Based on similar questions here and on other forums, I’ve pieced together this TSQL script:
USE MASTER; BACKUP DATABASE LiveDB TO DISK = '[tempfile]' WITH COPY_ONLY GO ALTER DATABASE DevDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO RESTORE DATABASE DevDB FROM DISK = '[tempfile]' WITH MOVE 'Live_db' TO N'...Dev.mdf', MOVE N'Live_db_log' TO N'...Dev_log.ldf', REPLACE, RECOVERY, NOUNLOAD GO ALTER DATABASE DevDB SET MULTI_USER
The script runs, I get no error messages, but if I query any table in the development database afterwards, I see the old, unchanged development data.
I get a success message (in German), which translates to:
RESTORE DATABASE has successfully processed 49809 pages in 16.393 seconds (23.737 MB/s).”
Immediately after the restore, I execute:
SELECT * FROM [LiveDB].dbo.SomeTable; SELECT * FROM [DevDB].dbo.SomeTable
…and I see two completely different results. It looks as if the DevDB hasn’t changed at all. I see the same rows I saw before the restore operation.
The live data is not changing; that particular table hasn’t changed in the last 2 weeks at least.
What am I doing wrong? I thought
WITH REPLACE would force
RESTORE to overwrite the development database.
I suspected multiple backups might exist in this backup file. If you run:
RESTORE HEADERONLY from disk = [tempfile]
this will show all the backups that are in the file. You are not restoring the latest backup, use
FILE=X to restore the appropriate backup.
I agree with Chad Mattox’s answer.
But to simplify the process, I’d recommend you to use the following t-sql to do the backup
BACKUP DATABASE LiveDB TO DISK = '[tempfile]' WITH COPY_ONLY, INIT;
By adding INIT in your backup script, you will always overwrite the existing one and thus simplify your restore script (by no need to find the latest File [X])