Create Backups without Breaking the Database Backup Sequence


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.


Continues…

Pages: 1 2 3 4




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |