Using scripts to restore database, error says backup set holds a backup of a database other than existing

Question

I am writing a script to automate the process of restoring one database using the .bak file of another database. I am getting the error:

The backup set holds a backup of a database other than the existing 'add_BackupDev' database.

The answers I am finding online all appear to have a solution that involves completing the restore manually and not through a script, which is not an option for me.

This is my code. The variable @LastDatabaseRestore is passing in the appropriate file path for my .bak file.

RESTORE DATABASE add_BackupDev
FILE = N'FILENAME'
FROM DISK = @LastDatabaseRestore
WITH FILE = 1,
MOVE 'add_backupDev' TO 'R:\DATA\add_BackupDev.mdf',
MOVE 'add_BackupDev_log' TO 'L:\LOG\add_BackupDev.ldf',
NOUNLOAD,
REPLACE;
GO

S3S · Accepted Answer · 2019-01-16 20:23:36Z

1

Too Long To Comment

Using the WITH REPLACE option (as you have listed) would overwrite the database with what ever database is contained within the backup. The reason you are getting that error in your script may be because you use the FILE option.

The FILE options preceding the backup device name specify the logical file names of the database files that are to be restored from the backup set; for example, FILE = 'FILENAME'

The reason you are getting the error may be because the backup set isn't the first database backup in the media set. You need to add the correct number to the FILE option in the WITH clause.

....
WITH FILE = 1,  --this may not need to be 1
....

If you don't have to explicitly specify the file name, then drop both FILE options.

RESTORE DATABASE add_BackupDev
FROM DISK = @LastDatabaseRestore
WITH
MOVE 'add_backupDev' TO 'R:\DATA\add_BackupDev.mdf',
MOVE 'add_BackupDev_log' TO 'L:\LOG\add_BackupDev.ldf',
NOUNLOAD,
REPLACE;
GO

That should restore your database.

edited Jan 16, 2019 at 20:23

answered Jan 16, 2019 at 19:23

S3S

25.2k5 gold badges29 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

istillhavetogopee Over a year ago

when I remove both file options, i get an error under the first MOVE option saying "incorrect syntax." when i remove the FILE = N'FILENAME', it tells me that it's trying to replace the .mdf and .ldf files of the database my .bak file is created from, which really makes no sense to me.

S3S Over a year ago

sorry i forgot to add back the WITH clause. I just fixed that and it should work now. And yes, it would replace them with the ones from the .bak, since you are using REPLACE. The last code snippet should work now.

istillhavetogopee Over a year ago

had some trouble getting the right file names for the move, but it works now! Thanks!

S3S Over a year ago

No worries at all! Remember you can rename them with MOVE as you MOVE them too.

Collectives™ on Stack Overflow

Using scripts to restore database, error says backup set holds a backup of a database other than existing

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related