Copy one database to another (existing) database on the same server

Posted on

Question :

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.

Answer :

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])

Leave a Reply

Your email address will not be published.