Create Backups without Breaking the Database Backup Sequence

/*  Transactional Log Backup @ 3 AM */
BACKUP LOG AdventureWorksDW2008
TO DISK='C:\DatabaseBackups\AdventureWorksDW2008_Log1.TRN'
WITH COMPRESSION

/*  Differnetial Backup @ 6 AM */
BACKUP DATABASE AdventureWorksDW2008
TO DISK='C:\DatabaseBackups\AdventureWorksDW2008_Diif1.BAK' 
WITH DIFFERENTIAL, COMPRESSION

/*  Transactional Log Backup @ 9 AM */
BACKUP LOG AdventureWorksDW2008
TO DISK='C:\DatabaseBackups\AdventureWorksDW2008_Log2.TRN'
WITH COMPRESSION

/*  Request For Special Full Backup @ 11 AM */
BACKUP DATABASE AdventureWorksDW2008
TO DISK='C:\DatabaseBackups\AdventureWorksDW2008_Special.BAK'
WITH COMPRESSION

/*  Differnetial Backup @ 12 PM */
BACKUP DATABASE AdventureWorksDW2008
TO DISK='C:\DatabaseBackups\AdventureWorksDW2008_Diif2.BAK' 
WITH DIFFERENTIAL, COMPRESSION

/*  Transactional Log Backup @ 3 PM */
BACKUP LOG AdventureWorksDW2008
TO DISK='C:\DatabaseBackups\AdventureWorksDW2008_Log3.TRN'
WITH COMPRESSION

/*  Disaster @ 4 PM */
/*  Tail  Log Backup */
BACKUP LOG AdventureWorksDW2008
TO  DISK = N'C:\DatabaseBackups\AdventureWorksDW2008_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:\DatabaseBackups\AdventureWorksDW2008_Full.BAK' WITH NORECOVERY

/*  Restore Latest Differential Backup of AdventureWorksDW2008 Database  */
RESTORE DATABASE AdventureWorksDW2008
FROM DISK ='C:\DatabaseBackups\AdventureWorksDW2008_Diif2.BAK' WITH NORECOVERY

/*  Restore Transaction Log Backup In Sequence After The Last Differential Backup of AdventureWorksDW2008 Database  */
RESTORE DATABASE AdventureWorksDW2008
FROM DISK ='C:\DatabaseBackups\AdventureWorksDW2008_Log3.TRN' WITH NORECOVERY

/* Restore the Transactional Tail Log Backup To Recovery The Database  */
RESTORE DATABASE AdventureWorksDW2008
FROM DISK ='C:\DatabaseBackups\AdventureWorksDW2008_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.