2

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.

Improve this question
1
  • 1
    Perform your initial backup WITH INIT.
    – Aaron Bertrand
    Commented May 20, 2016 at 7:50

2 Answers 2

Reset to default
3

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.

Improve this answer
1

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

Improve this answer
3
  • Thanks, that's what I did, after I realized what the problem was. Chad Mattox's answer helped me understand that, so I'll leave it as the accepted answer
    – nikie
    Commented May 23, 2016 at 5:37
  • Glad to hear that everything works for you. For backup itself, another thing I'd recommend is to add COMPRESSION keyword if your sql server edition allows it, i.e. BACKUP DATABASE LiveDB to disk ='[tempfile]' with COPY_ONLY, INIT, COMPRESSION;
    – jyao
    Commented May 24, 2016 at 21:20
  • This is likely appropriate for this specific question, but the file may contain good backups that you don't want overwritten. Either answer gets the job done, using HEADERONLY restore, and File-X allows existing backups to be kept, using INIT overwrites existing backups. So it all depends on if the existing backups are of any value or not.
    – Chad Mattox
    Commented May 31, 2016 at 17:50

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.