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