Search code examples
sqlsql-serverdatabase-administration

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

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
Solution

  • 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.