We are trying to establish a maintenance plan with 2 separate backups.

One full backup over the weekend and differential backups through the week. [the differential backup has a "_diff" appended to its name].

The backups are supplied to us by an external entity we use for processing orders. My problem is with restoring the databases.

1: I tried the method here: http://msdn.microsoft.com/en-us/library/ms175510.aspx#TsqlProcedure but, I can't seem to "add" a backup file to the existing backup device on the server that contains the full backup.

2: I tried restoring them as 2 separate backups but I get this error:

The backup set holds a backup of a database other than the existing 'DBXXX' database. [possibly due to the _diff name associated with the .mdf and .ldf files]

How do I go about restoring the differential backups in this scenario. Can I add the differential backup to the existing full backup device? Is there another way out?


Added T-SQL statements:

Restoring the full backup


Restoring the diff. backup


**Full Backup Device:** OM_00987_Backup_987 D:\SQLdata\OM_00987_Backup_987.mdf D PRIMARY 32852082688 3.51844E+13 1 0 0 BAC0AD62-9CB6-46FA-8600-7852009C638A 0 0 32850247680 512 1 NULL 1.33E+17 2B366F37-7D81-4BD1-AA3C-DD9757EFF075 0 1 NULL

OM_00987_Backup_987_log E:\SQLlogs\OM_00987_Backup_987_log.LDF L NULL 516096 2.19902E+12 2 0 0 F76787F2-521B-4A75-A2F6-8977DB88E0A9 0 0 0 512 0 NULL 0 00000000-0000-0000-0000-000000000000 0 1 NULL

**Delta Backup Device:** OM_00987_Backup_987_Delta D:\SQLdata\OM_00987_Backup_987_Delta.mdf D PRIMARY 9633464320 3.51844E+13 1 0 0 E75C765B-ECEC-46AC-B8AE-5F1EB7D3C724 0 0 9629138944 512 1 NULL 1.36E+17 8C66A6C8-DE57-4B57-B0E1-DE8F3C64E68A 0 1 NULL

OM_00987_Backup_987_Delta_log E:\SQLlogs\OM_00987_Backup_987_Delta_log.LDF L NULL 516096 2.19902E+12 2 0 0 FC843B68-BC7C-478A-BE4E-D4AA4D61B30E 0 0 0 512 0 NULL 0 00000000-0000-0000-0000-000000000000 0 1 NULL

  • Are the backups from the same database? If you have a different name on the .mdf and .ldf files it would lead us to believe that you are trying to restore a diff from a different database.
    – jgardner04
    Commented May 21, 2012 at 13:47
  • Yes, they are in fact from the same database. I'm not sure why they [order processing entity] use different names. If I could ask them to use the same names on both backups, could I add them both to the same backup device? Commented May 21, 2012 at 14:00
  • 1
    Run RESTORE FILELISTONLY|HEADERONLY FROM <backup_device>, for both backup files and add the output to your question. Commented May 21, 2012 at 18:36

1 Answer 1


You will need to set up a separate backup device for the FULL and the DIFF. When you create the Backup Device it is a single file. You can't save a FULL and a DIFF in the same Backup Device.

More information about backup devices can be found here: http://msdn.microsoft.com/en-us/library/ms179313.aspx

If you are trying to restore the database over an existing database and want to replace the data you will need to use the WITH REPLACE option in the restore. More information can be found here: http://msdn.microsoft.com/en-us/library/ms186858.aspx

REPLACE should be used rarely and only after careful consideration. Restore normally prevents accidentally overwriting a database with a different database. If the database specified in a RESTORE statement already exists on the current server and the specified database family GUID differs from the database family GUID recorded in the backup set, the database is not restored. This is an important safeguard.

The REPLACE option overrides several important safety checks that restore normally performs. The overridden checks are as follows:

Restoring over an existing database with a backup taken of another database.

With the REPLACE option, restore allows you to overwrite an existing database with whatever database is in the backup set, even if the specified database name differs from the database name recorded in the backup set. This can result in accidentally overwriting a database by a different database.

Restoring over a database using the full or bulk-logged recovery model where a tail-log backup has not been taken and the STOPAT option is not used.

With the REPLACE option, you can lose committed work, because the log written most recently has not been backed up.

Overwriting existing files.

For example, a mistake could allow overwriting files of the wrong type, such as .xls files, or that are being used by another database that is not online. Arbitrary data loss is possible if existing files are overwritten, although the restored database is complete.

  • Thank you. msdn.microsoft.com/en-us/library/ms188248.aspx#TsqlProcedure mentions backing up 2 types [full and differential] to the same backup device which prompted my question about adding the diff .BAK file to the existing device. I tried using 2 different backup devices but received the error "The backup set holds a backup of a database other than the existing 'DBXXX' database." This could be because the delta backup has a different name though. Commented May 21, 2012 at 14:47
  • Are you trying to restore to an existing database? If so are you using the WITH REPLACE option?
    – jgardner04
    Commented May 21, 2012 at 14:50
  • Yes. And no, I am using the WITH NORECOVERY option with the full database backup but WITH RECOVERY for the differential backup. Commented May 21, 2012 at 14:53
  • I have added more information on the WITH REPLACE option to my answer.
    – jgardner04
    Commented May 21, 2012 at 15:03
  • thanks jgardner. Are you suggesting I use the REPLACE option? Can I use the WITH REPLACE option with the differential backup? Wouldn't that overwrite the database with the changes since the last full backup as opposed to updating them? Commented May 21, 2012 at 15:08

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.