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