Create Backups without Breaking the Database Backup Sequence

Development or Testing teams often request Database Administrators to provide a copy of a full database backup of a production database. When such a request comes in, I have seen in many organizations that the junior database administrators will quickly perform a full database backup and copy the backup files to the specified shared location and finally they go ahead and remove the special full backup files from the production server to save the disk space. However they don’t realize that this action could break the current database backup sequence especially when your database backup plan consists of a combination of differential and transactional log backups. In this article first you will see an example where you end up breaking the database backup sequence due to negligence. And finally you will see how to leverage the COPY_ONLY backup option which was introduced in SQL Server 2005 to perform an adhoc full database backups. When you use the COPY_ONLY backup option the database backup sequence will not break.

Introduction to COPY_ONLY Backup
In SQL Server 2005 Microsoft introduced a new type of database backup called COPY_ONLY. Using this backup mechanism, database administrator can perform Full, Differential and Transactional Log backup of all the databases without breaking the Database Backup Log Sequence Number (LSN).

How COPY_ONLY Backup Works
When you are performing the database backup using the COPY_ONLY option it basically keeps the existing log archive point intact. As a result the existing database backup sequence is not broken which means your existing database backup files can be used for recovering the database exactly to the time of failure. The Full, Differential or Transactional log backups when performed using COPY_ONLY option will not break the log sequence. However, it is very important to have a Full database backup available with you as Differential and Transactional Log backups are dependent on Full backups.

Database Backup Restoration Sequence
While recovering the database at the time of disaster; you need to first restore the Full backup with NORECOVERY, followed by restoring the latest Differential backup with NORECOVERY and then you need to restore all the transactional log backups in the order of creation with NORECOVERY. Finally you need to restore the tail log backup with RECOVERY option.

The below table mentions a typical database backup plan which is been followed in many organizations.



Now let us take the database backups by executing the below mentioned TSQL at the specified time mentioned in the above table. In this example you can see that a special full database back up is taken at 11 AM and then on the same day a disaster strikes at 4 PM. Our aim will be to use the database backup files which were created throughout the day and restore AdventureWorksDW2008 database. All the databases backups are created using the database backup compression feature which was introduced in SQL Server 2008. If you want to know more about database backup compression feature introduced in SQL Server 2008 then you can refer to my earlier article titled How to configure and Use Database Backup Compression in SQL Server 2008.