Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

Edit:

Added T-SQL statements:

Restoring the full backup

RESTORE DATABASE DB_TEST FROM DB_TEST_Device WITH REPLACE, NORECOVERY;    
GO 

Restoring the diff. backup

RESTORE DATABASE DB_TEST FROM DB_TEST_Device_Delta WITH RECOVERY;     
GO

**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

share|improve this question
    
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 May 21 '12 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? –  user1003954 May 21 '12 at 14:00
1  
Run RESTORE FILELISTONLY|HEADERONL‌​Y FROM <backup_device>, for both backup files and add the output to your question. –  Mark Storey-Smith May 21 '12 at 18:36
add comment

1 Answer

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.

share|improve this answer
    
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. –  user1003954 May 21 '12 at 14:47
    
Are you trying to restore to an existing database? If so are you using the WITH REPLACE option? –  jgardner04 May 21 '12 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. –  user1003954 May 21 '12 at 14:53
    
I have added more information on the WITH REPLACE option to my answer. –  jgardner04 May 21 '12 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? –  user1003954 May 21 '12 at 15:08
show 3 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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