Thursday, July 15, 2010

Backup with Copy_Only Option

Copy_Only option was introduced as one of the backup option starting with SQL Server 2005. It is a pretty useful backup option that I have been using it for a while now. When you back up your database or log with Copy_Only option, it will back up the database or log, and more importantly it will not disturb the backup sequence/routine that you might already have.

How is this useful?

Let say that you have established a routine in which you do a full backup on your database every once a week on Monday morning at 7:00 AM. You also have set up a differential backup to be done once a day at 8:00 PM. Then come Wednesday morning, your application vendor/developer manager is asking for the latest backup of your database to help to troubleshoot an application problem.

What to do?

There are several things that you can do. First you can immediately take a differential backup, and send the Monday’s full database backup and the newly created differential backup to your application vendor/developer manager.

Alternatively, you can also immediately take a full database backup, and send that full database backup to your application vendor/developer manager. However, one thing that you need to do is you need to make sure that you keep that full database backup. It is because the subsequent differential backups will depend on that full database backup that you’ve just take. (I guess a little bit of refresher is in order here. Differential backup contains changes that happen since the last full backup.) It’s just one more thing to worry about.

This is where the Copy_Only option can come in handy. In this scenario, you can use the Copy_Only option when taking the full database backup. The advantage of this is that this will not affect the subsequent differential backups. Those differential backups will still be based on the Monday’s full backup that you have. Let say that on Friday morning, you encountered a data corruption on that database. And you want to restore up to the Thursday differential backup. You can just restore the database from the Monday’s full backup and then restore the Thursday differential backup, and you should be golden.

Demonstration (Without Copy_Only option)

For this demonstration I am using AdventureWorks database. Let say that I have the following backup routine:

  • Full Backup #1
  • Differential Backup #1
  • Differential Backup #2
  • Full Backup #2
  • Differential Backup #3

I can use the following script:

USE [master];

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backup\AW\Full_Backup_1.bak'
WITH NAME = 'Full Backup #1';

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\ Backup\AW\Diff_Backup_1.bak'
WITH DIFFERENTIAL, NAME = 'Differential Backup #1'

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\ Backup\AW\Diff_Backup_2.bak'
WITH DIFFERENTIAL, NAME = 'Differential Backup #2'

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\ Backup\AW\Full_Backup_2.bak'
WITH NAME = 'Full Backup #2';

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\ Backup\AW\Diff_Backup_3.bak'
WITH DIFFERENTIAL, NAME = 'Differential Backup #3'

Now, if we try to restore the database from Full Backup #1 and then restore the Differential Backup #3, using the following script:

USE [master];

RESTORE DATABASE AW_Restore
FROM DISK = 'C:\Backup\AW\Full_Backup_1.bak'
WITH NORECOVERY,
MOVE 'AdventureWorks_Data' TO 'C:\Data\AW\AdventureWorks_Data.mdf',
MOVE 'AdventureWorks_Log' TO 'C:\Data\AW\AdventureWorks_Log.ldf';

RESTORE DATABASE AW_Restore
FROM DISK = 'C:\Backup\AW\Diff_Backup_3.bak'
WITH RECOVERY;

As expected, we will get the following error:

This differential backup cannot be restored because the database has not been restored to the correct earlier state.

Now, let’s look at why this is happening. Try running the following query:

SELECT name, type, is_copy_only, first_lsn, differential_base_lsn
FROM msdb..backupset
WHERE database_name = 'AdventureWorks'
ORDER BY backup_start_date ASC

The result is as follow (you might get a different set of result):

nametypeis_copy_onlyfirst_lsndifferential_base_lsn
Full Backup #1D0260000000007500037NULL
Differential Backup #1I0260000000009700034260000000007500037
Differential Backup #2I0260000000011200001260000000007500037
Full Backup #2D0260000000011600037NULL
Differential Backup #3I0260000000013700034260000000011600037


You will notice that the Full Backup #1 has first_lsn of 260000000007500037. (LSN is Log Sequence Number). And if you noticed on the differential_base_lsn, the Differential Backup #1 and Differential Backup #2 are also having the same LSN of 260000000007500037. This would indicate that the Differential Backup #1 and Differential Backup #2 are based on Full Backup #1. You will need Full Backup #1 in order to restore from Differential Backup #1 or Differential Backup #2.

Notice also that once you take the Full Backup #2, the subsequent differential back up (Differential Backup #3), has a different base LSN than the Differential Backup #1 or Differential Backup #2. In order to restore from Differential Backup #3, you need Full Backup #2.

Demonstration (With Copy_Only Option)

Let say that I have the following backup routine:

  • Full Backup #1
  • Differential Backup #1
  • Differential Backup #2
  • Full Backup #2 (With Copy_Only Option)
  • Differential Backup #3

I can use the following script:

USE [master];

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backup\AW\Full_Backup_1.bak'
WITH NAME = 'Full Backup #1';

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backup\AW\Diff_Backup_1.bak'
WITH DIFFERENTIAL, NAME = 'Differential Backup #1'

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backup\AW\Diff_Backup_2.bak'
WITH DIFFERENTIAL, NAME = 'Differential Backup #2'

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backup\AW\Full_Backup_2.bak'
WITH NAME = 'Full Backup #2 (With Copy_Only)', COPY_ONLY;

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backup\AW\Diff_Backup_3.bak
WITH DIFFERENTIAL, NAME = 'Differential Backup #3'

Now, if we try to restore the database from Full Backup #1 and then restore the Differential Backup #3, using the following script:

USE [master];

RESTORE DATABASE AW_Restore
FROM DISK = 'C:\Backup\AW\Full_Backup_1.bak'
WITH NORECOVERY,
MOVE 'AdventureWorks_Data' TO 'C:\Data\AW\AdventureWorks_Data.mdf',
MOVE 'AdventureWorks_Log' TO 'C:\Data\AW\AdventureWorks_Log.ldf';

RESTORE DATABASE AW_Restore
FROM DISK = 'C:\Backup\AW\Diff_Backup_3.bak'
WITH RECOVERY;

It went successfully. So what happen? Let’s try running the following query:

SELECT name, type, is_copy_only, first_lsn, differential_base_lsn
FROM msdb..backupset
WHERE database_name = 'AdventureWorks'
ORDER BY backup_start_date ASC

Here’s the result:

nametypeis_copy_onlyfirst_lsndifferential_base_lsn
Full Backup #1D0260000000015600037NULL
Differential Backup #1I0260000000017800034260000000015600037
Differential Backup #2I0260000000019300001260000000015600037
Full Backup #2 (With Copy_Only)D1260000000019500001NULL
Differential Backup #3I0260000000019700001260000000015600037

As you can see, unlike a regular full database backup, when we use the Copy_Only option, the subsequent differential (Differential Backup #3) is still based on the Full Backup #1. Looking at the differential_base_lsn column, you can see that.

Take Away

You can use Copy_Only option with your backup for that “one off” situation, where you need to take a backup without disturbing your routine backup strategy.

Some Notes

  • You can use Copy_Only option when you are doing full database backup or transaction log backup. You can not do it with the differential backup. If you put the Copy_Only option when you do the differential backup, SQL Server will ignore the Copy_Only option.
  • You can not use the full backup that you take with the Copy_Only option as the base to restore subsequent differential backup.
  • However, you can use the full backup that you take with the Copy_Only option as the base to restore subsequent log backup(s).
Posted by at
Labels: , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)