/* Full Backup @ 12 AM */
BACKUP DATABASE AdventureWorksDW2008
TO DISK=’C:DatabaseBackupsAdventureWorksDW2008_Full.BAK’
WITH COMPRESSION
/* Transactional Log Backup @ 3 AM */
BACKUP LOG AdventureWorksDW2008
TO DISK=’C:DatabaseBackupsAdventureWorksDW2008_Log1.TRN’
WITH COMPRESSION
/* Differnetial Backup @ 6 AM */
BACKUP DATABASE AdventureWorksDW2008
TO DISK=’C:DatabaseBackupsAdventureWorksDW2008_Diif1.BAK’
WITH DIFFERENTIAL, COMPRESSION
/* Transactional Log Backup @ 9 AM */
BACKUP LOG AdventureWorksDW2008
TO DISK=’C:DatabaseBackupsAdventureWorksDW2008_Log2.TRN’
WITH COMPRESSION
/* Request For Special Full Backup @ 11 AM */
BACKUP DATABASE AdventureWorksDW2008
TO DISK=’C:DatabaseBackupsAdventureWorksDW2008_Special.BAK’
WITH COMPRESSION
/* Differnetial Backup @ 12 PM */
BACKUP DATABASE AdventureWorksDW2008
TO DISK=’C:DatabaseBackupsAdventureWorksDW2008_Diif2.BAK’
WITH DIFFERENTIAL, COMPRESSION
/* Transactional Log Backup @ 3 PM */
BACKUP LOG AdventureWorksDW2008
TO DISK=’C:DatabaseBackupsAdventureWorksDW2008_Log3.TRN’
WITH COMPRESSION
/* Disaster @ 4 PM */
/* Tail Log Backup */
BACKUP LOG AdventureWorksDW2008
TO DISK = N’C:DatabaseBackupsAdventureWorksDW2008_TAIL_LOG.TRN’
WITH NO_TRUNCATE, COMPRESSION
GO
As there was a disaster at 4 PM you need to recover the AdventureWorksDW2008 database. The database can be restored using the below mentioned TSQL code which uses the initial database full backup which was taken at 12 AM.
/* Restore Initial Full Backup of AdventureWorksDW2008 Database */
RESTORE DATABASE AdventureWorksDW2008
FROM DISK =’C:DatabaseBackupsAdventureWorksDW2008_Full.BAK’ WITH NORECOVERY
/* Restore Latest Differential Backup of AdventureWorksDW2008 Database */
RESTORE DATABASE AdventureWorksDW2008
FROM DISK =’C:DatabaseBackupsAdventureWorksDW2008_Diif2.BAK’ WITH NORECOVERY
/* Restore Transaction Log Backup In Sequence After The Last Differential Backup of AdventureWorksDW2008 Database */
RESTORE DATABASE AdventureWorksDW2008
FROM DISK =’C:DatabaseBackupsAdventureWorksDW2008_Log3.TRN’ WITH NORECOVERY
/* Restore the Transactional Tail Log Backup To Recovery The Database */
RESTORE DATABASE AdventureWorksDW2008
FROM DISK =’C:DatabaseBackupsAdventureWorksDW2008_TAIL_LOG.TRN’ WITH RECOVERY
You will see that when the above script was executed the database restoration failed after restoring the initial full backup and the database was left in Restoring…. state as shown in the below snippet. The database restoration script basically fails while restoring the AdventureWorksDW2008_Diif2.BAK backup file with the below mentioned error message.
Msg 3136, Level 16, State 1, Line 2
This differential backup cannot be restored because the database has not been restored to the correct earlier state.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
