You could see that the restoration failed with the following error message “This differential backup cannot be restored because the database has not been restored to the correct earlier state” which means that the order of database files restore is not matching and there is a difference in the Log Sequence Number (LSN). Next step will be to check why this error has happened. By executing the below mentioned TSQL query you can verify the LSN values for every backup performed.
SELECT
DATABASE_NAME AS DBNAME,
TYPE,
FIRST_LSN,
DATABASE_BACKUP_LSN,
RECOVERY_MODEL
FROM MSDB.DBO.BACKUPSET
WHERE DATABASE_NAME = ‘ADVENTUREWORKSDW2008’
As you could see in the above snippet that when the special full backup was taken the Database Backup LSN value changed from the initial value of 79000000004300037 (Green Box) to 79000000008300037 (Red Box) which means the database backup sequence was broken.
Let’s see what will happen when you take the special full database backup using COPY_ONLY option. Execute the below TSQL code in the same sequence to perform the database backups and while performing the special full backup you will be using the COPY_ONLY option.
/* 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
/* Special Full Backup @ 11 AM Performed Using COPY_ONLY Option*/
BACKUP DATABASE AdventureWorksDW2008
TO DISK=’C:DatabaseBackupsAdventureWorksDW2008_Special.BAK’
WITH COMPRESSION, COPY_ONLY — SPECIFIED COPY_ONLY OPTION
/* 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
Restore AdventureWorksDW2008 Database
Next step will be to restore the AdventureWorksDW2008 database using the below mentioned TSQL code and recover the database.
/* 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
From the above snippet you could see that the AdventureWorksDW2008 database is restored successfully. Now let us check what is the value for Database Backup LSN when you have taken the special database backup using the COPY_ONLY option. The values are highlighted within the green box in the below snippet.
