SQL Command Restore Appending not Replacing

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

SQL Command Restore Appending not Replacing

Author

Topic

ITTrucker
Yak Posting Veteran

64 Posts

Posted - 2015-02-18 : 14:56:31

We have a command file that calls a sequence of sqlcmd -i [Path]\[File.sql] commands to automatically backup and restore our production database to a testing environment on a weekly basis. The script does a full backup and then does a restore, but on the restore it looks like it's doing a partial overwrite but then also some appending of records.

Restore SQL file:
IF EXISTS (SELECT * FROM master.dbo.sysdatabases WHERE name = 'Testing')
ALTER DATABASE Testing
SET SINGLE_USER WITH ROLLBACK IMMEDIATE

USE Master

RESTORE DATABASE Testing FROM DISK='C:\Backup\Production.bak'WITH REPLACE

ALTER DATABASE Testing SET Multi_user
GO

When the restore is complete, there are records in the Testing database that don't exist in Production. If I do a fresh restore to a new database, the records don't exist, so those tables must have had some sort of diff done or appending done or something to keep those records around during the restore.

I'd like this to be a full wipeout and restore so Testing is an exact mirror of production at the start of the week.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-18 : 15:01:23

It's not possible for the restore command to do a partial restore or append. A restore is a complete overwrite. Period.

If you are seeing different data in the restored database, then you either have another script making those changes or someone/something is making those changes after the restore. Or maybe it's not using the correct backup file.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

ITTrucker
Yak Posting Veteran

64 Posts

Posted - 2015-02-18 : 15:12:15

There's a bunch of commands in the file, I guess I have some more digging to do. (I was hoping the lack of space before the WITH would have meant something)

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-18 : 15:24:46

I didn't notice the missing space, but now that you mention it you need to verify that your script completed successfully. SQL either threw a syntax error due to the missing space, or SQL corrected it (older versions were kind enough to continue when little syntax problems existed). If it threw a syntax error, then the restore would have failed and that could explain the issue.

You can verify if the restore even ran by checking the server's Error Log. It'll show what file was used too.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

ITTrucker
Yak Posting Veteran

64 Posts

Posted - 2015-02-23 : 10:27:05

It looks like someone must have set the identity insert and inserted rows with an old ID and forgotten about it, (even though everyone says they didn't).

The restore this weekend was a full restore, there were no extra records/tables anywhere (so the person who swore that their test tables existed after the restore must have been mistaken)

The missing space in the command looks like it got parsed correctly and I checked the logs, it's been working fine for a while.

I guess I got a little panicked over nothing (other than the phantom inserts).

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources