Systems Engineering and RDBMS

COPY_ONLY Option in SQL Server 2005 Backups

Posted by decipherinfosys on April 8, 2008

As you already know, when you take back-ups in SQL Server, the LSN (Log Sequence Number) gets stored in the backup file. These LSNs are referenced when you are doing a restore of the database. The reason why these LSNs are stored in the backup files is to make sure that when we do the restore, the files are restored in a proper sequence. If you try to restore out of sequence, you will get an error. So, is this even an issue? Yes – it is an issue in certain scenarios. Say, you are doing a full backup every night and then you do a transaction log backup every 4 hours. If you need to do a special one off backup during the day, that one-off back up will now become a part of the restore sequence meaning you would need to restore this full back-up and then the subsequent transaction log backups in order to do the restore.

That is the reason why MSFT added a new option in SQL Server 2005 that allows us to take such special one-off backups without effecting the LSNs and the sequence order. This option applies to the full backups as well as the transaction log backups. As you might recall, when doing differential backups, it backs up all the data pages since the last full backup that was taken and thus it does not effect the LSNs.

The new option is called COPY_ONLY. Let’s follow this up with an example – we will simulate the error first and then use the option mentioned above to see how this new option helps us resolve the issue.

/**************************************
Let’s do the back-ups first:
We will do a full back-up first
and then two transaction log back-ups
***************************************/
USE master
GO
BACKUP DATABASE PROD TO DISK=’c:\PROD_full.BAK’ WITH INIT
BACKUP LOG PROD TO DISK=’c:\PROD_log1.TRN’ WITH INIT
BACKUP LOG PROD TO DISK=’c:\PROD_log2.TRN’ WITH INIT
GO

The output is as shown below:

Processed 85936 pages for database ‘PROD’, file ‘PROD’ on file 1.
Processed 2 pages for database ‘PROD’, file ‘PROD_log’ on file 1.
BACKUP DATABASE successfully processed 85938 pages in 40.661 seconds (17.313 MB/sec).
Processed 9 pages for database ‘PROD’, file ‘PROD_log’ on file 1.
BACKUP LOG successfully processed 9 pages in 0.277 seconds (0.253 MB/sec).
Processed 0 pages for database ‘PROD’, file ‘PROD_log’ on file 1.
BACKUP LOG successfully processed 0 pages in 0.251 seconds (0.000 MB/sec).

/**************************************
Let’s do the Restores now:
We will take the full back-up first
and then the second txn log backup
i.e. we will try to restore out of
sequence
***************************************/
RESTORE DATABASE PROD FROM DISK=’C:\PROD_full.BAK’ WITH NORECOVERY
/*NOTE: We are restoring the second txn log back-up now without restoring the first one*/
RESTORE LOG PROD FROM DISK=’C:\PROD_log2.TRN’ WITH RECOVERY

And this time, we will get this error:

Processed 85936 pages for database ‘PROD’, file ‘PROD’ on file 1.
Processed 2 pages for database ‘PROD’, file ‘PROD_log’ on file 1.
RESTORE DATABASE successfully processed 85938 pages in 95.397 seconds (7.379 MB/sec).
Msg 4305, Level 16, State 1, Line 3
The log in this backup set begins at LSN 300000001078800001, which is too recent to apply to the database. An earlier log backup that includes LSN 300000001078200001 can be restored.
Msg 3013, Level 16, State 1, Line 3
RESTORE LOG is terminating abnormally.

Now, let’s do the same steps again but this time, we will take the first transaction log back-up using the COPY_ONLY option:

/*****************************************************
Let’s do the back-ups first:
We will do a full back-up first
and then two transaction log back-ups
The first one is with the COPY_ONLY option
and the second one is the regular one

******************************************************/
USE master
GO
BACKUP DATABASE PROD TO DISK=’c:\PROD_full.BAK’ WITH INIT
BACKUP LOG PROD TO DISK=’C:\PROD_log1.TRN’ WITH COPY_ONLY
BACKUP LOG PROD TO DISK=’c:\PROD_log2.TRN’ WITH INIT
GO

And now, let’s do the restore in the same fashion as above and see whether it goes through this time around:

/*****************************************************
Let’s do the Restores now:
We have taken the first transaction log backup using the
COPY_ONLY option so restoring the second one should
maintain the LSNs
******************************************************/
RESTORE DATABASE PROD FROM DISK=’C:\PROD_full.BAK’ WITH NORECOVERY
/*NOTE: We are restoring the second txn log back-up now without restoring the first one*/
RESTORE LOG PROD FROM DISK=’C:\PROD_log2.TRN’ WITH RECOVERY

And this time, it goes through even though we had taken that special one-off backup in the middle of the sequence – here is the output:

Processed 85936 pages for database ‘PROD’, file ‘PROD’ on file 1.
Processed 1 pages for database ‘PROD’, file ‘PROD_log’ on file 1.
RESTORE DATABASE successfully processed 85937 pages in 103.495 seconds (6.802 MB/sec).
Processed 0 pages for database ‘PROD’, file ‘PROD’ on file 1.
Processed 4 pages for database ‘PROD’, file ‘PROD_log’ on file 1.
RESTORE LOG successfully processed 4 pages in 0.193 seconds (0.145 MB/sec).

As you can see from above, this is a good addition to the backup set in SQL Server 2005 – especially for the production environments and even for the development environments at times when the CTO/CIP/VP comes asking for a fresh backup that can be used for a demo…using this option, such special one-off back-ups can be taken without effecting your back-up and restore routines.

One Response to “COPY_ONLY Option in SQL Server 2005 Backups”

  1. […] all types of backups: full backups, differential backups, file/filegroup backups, partial backups, copy only backups as well as transaction log […]

Sorry, the comment form is closed at this time.

 
%d bloggers like this: