Creating a foolproof disaster recovery solution is imperative for every business. After the Recovery Point Objective (RPO) and Recovery Time Objective (RTO) are defined, a database backup plan should be created to match these objectives. Most DBAs tend to automate the majority of tasks related to database backup plans. Regular database backup schedules are set in order to create continuous backup chains, that can later be used to recover a database in the case of a disastrous event. By setting the backup schedule, the continuity of the backup process is ensured, and most of the job is performed automatically on a regular basis.
There are many situations when backup files need to be created outside of the regular backup schedule. These backup files can be created manually, in a regular way, with the usual BACKUP DATABASE command. But using this approach can prove not always helpful, in the case of a disaster, if we need to restore a backup file that is created after this manual out-of-the-schedule backup operation. Let’s look at the few examples where manual database backup performed out of the regular schedule affects the daily routine:
Database uses simple recovery model
The full database backup is taken daily (at midnight for example) and regular differential backups are taken every hour. This way, the RPO of one hour is ensured. The disaster occurred around 7:30 AM, and DBAs reasonably decided to restore the last differential database backup that was created at 7:00 AM. Unfortunately, the restore operation failed with the following message: “This differential backup cannot be restored because the database has not been restored to the correct earlier state”.
Further investigation of backup history showed that another full backup was taken that day at 6 AM. This manual database backup ended the backup chain that starts with the full backup file at 12 AM and started the new chain. All differentials created before 6 AM use the 12 AM backup as their base. All differentials created after the manual backup at 6 AM use this backup file as a base. Therefore, the 12 AM full backup cannot be used to restore 7 AM differential backup.
In this case, if 6 AM manual full database backup got lost, all further differentials would be useless, and recovery would not be possible for the time up until the next full backup.
Picture 1: Effects of unscheduled full database backup on the differential log backup chain
Database uses full recovery model
One of the greatest advantages of full recovery model databases is the ability to recover a database to a specified point in time, provided that all recovery resources are available. This also brings us to the main drawback of the full recovery model: it is necessary to perform regular transaction log backups to prevent uncontrolled growth of the database’s transaction log. Storing and maintaining log backups requires additional resources, but once the process gets automated, the benefits often outweigh the effort.
Unlike in the previous case, taking a full database backup outside of the backup schedule won’t affect the transaction log backup chain. This is logical, since a full database backup operation doesn’t truncate the transaction log of a database, and all transaction log backups contain transactions committed since previous transaction log backup only. The only exception to this rule is the first full database backup taken after the database is created. This doesn’t mean that we cannot use the last full database backup as a starting point when performing point in time recovery. We can use any other available full backup, provided that we have available all transaction log backups created after that full backup, up to recovery point.
In the example, shown in picture 2, an unscheduled full database backup is taken at 9 AM. If the disaster occurred at 12 PM, both 12 AM and 9 AM backup files can be used as the starting point of the restore sequence. Restore sequence 2 would be much faster, but if the manual backup file got lost, a restore would still be possible with the 12 AM backup file and subsequent log backups.
Picture 2: Effects of unscheduled full database backup on the transaction log backup chain
For databases in full recovery model, the problem might occur if someone took transaction log backup outside of the regular backup schedule, and misplaced it. The missing log backup creates a gap in the backup chain, and all transactions that were stored in this file will not be possible to recover.
For example, for the low traffic databases, the following backup schedule can be used:
– Full database backups are created daily at 12 AM
– Transaction log backups are created every 3 hours
If someone took out-of-the-schedule transaction log backup at 10:30 AM, it will contain all transactions that were committed between 9 AM and 10:30 AM. If this file gets misplaced, these transactions will be lost indefinitely, and point in time restore will not be possible for this period.
Picture 3: Effects of unscheduled transaction log backup on the transaction log backup chain
Solution
The best way to avoid described issues when taking backup files out of the scheduled time frame is to use copy-only backups. Copy-only backups are independent backup files that do not disrupt the regular Log Sequence Number (LSN) routine. This means that backup chains and restore sequences won’t be affected by the copy-only backup. There are two types of copy-only backups: full database backups, and transaction log database backups. Differential copy-only backups are not supported.
Copy-only full backups can be used with databases in any recovery model. When copy-only full backups are restored, the process and effects are the same as if the regular full backup is restored. The difference is that copy-only full backup cannot be used as the base for future differential backups as shown in picture 4.
Picture 4: Effects of copy-only full backup on differential log backup sequence
If used with the sequence of transaction log backups, copy-only full backup acts in the same way as regular backup. In that case, the situation is identical to the one described in picture 2.
Copy-only transaction log backups work in the same way, but can only be used with databases in full and bulk-logged recovery model. They contain all transactions that were committed since previous regular transaction log backup, and they never truncate the transaction log. This means that the next regular transaction log backup will be able to capture all transactions since previous regular transaction log backup. This way, there is no risk of potential data loss.
Picture 5: Effects of copy-only transaction log backup on the transaction log backup chain
Copy-only backup files can be easily created by using T-SQL scripts or through user interface in SSMS.
Create copy-only backups with T-SQL scripts
The query that is used to create copy-only full database backup is similar to the usual backup query. The only difference is WITH COPY_ONLY parameter. For the AdventureWorks2014 database, the following query can be used:
-- Query 1: Copy-only full database backup BACKUP DATABASE AdventureWorks2014 TO DISK = 'E:\Backup\CopyOnly_AdventureWorks2014.bak' WITH COPY_ONLY;
Running the query produces the backup file at the specified location:
To create copy-only transaction log backup, use the BACKUP LOG command:
-- Query 2: Copy-only transaction log backup BACKUP LOG AdventureWorks2014 TO DISK = 'E:\Backup\CopyOnly_AdventureWorks2014.trn' WITH COPY_ONLY;
The backup file is created in the same way as in the previous example, but this time the TRN file type is used.
Create copy-only backups in SQL Server Management Studio
To create copy-only backup files by using SSMS GUI, perform the following steps:
-
Expand the Databases node in tree view of Object Explorer. Right click on the database that needs to be backed up, and click Tasks/Back up… in the context menu.
-
In the General tab of Back Up Database, specify the database and backup type of the copy-only file. Make sure to check the box for Copy-only backup. Note that selecting Differential backup type disables the Copy-only backup control.
-
In the Destination section, the name of the last created backup file will be specified. To avoid overwriting this file, click on the Add button, and provide different file path for the backup. Make sure to include the proper name for the backup file in the file path. If needed, use the browse button to locate the backup destination manually.
-
Click OK button to complete the process with the default settings. The action completes with the success message:
The backup file is located at the specified file path.
- Copy-Only Backups (SQL Server)
- Point in Time Recovery with SQL Server
- What is backup and restore in SQL Server disaster recovery?
|
Quick tip: Copy-only backups are supported in all SQL Server versions, starting with SQL Server 2005. However, the copy-only backup files cannot be created through SSMS 2005 user interface, but only with T-SQL script. All later versions of SSMS have the copy-only option available. |
Related pages:
November 7, 2017
