A new feature added to SQL Server 2005 is the ability to create copy-only backups. The advantage to this type of backup is that it doesn't interfere with your regular scheduled backup sequences, if you need to create another backup file outside of your regular backup processing.
Let's say you need to create a full backup in the middle of the day in order to refresh your development or test environments. In most cases if you issue another full backup in between doing your regularly scheduled backups, it does not cause an issue with your transaction log backup sequence numbers. But, it can throw off your differential backups. Also, if you need to run an additional transaction log backup during your regular processing, this could cause an issue when you need to restore your backup files.
So basically, the copy-only function allows you to run a non-regular scheduled backup at any time without causing an issue with the log sequence numbers stored in the backup files.
You probably think these items need to be run in conjunction, i.e. all regular or all copy_only, but this is not the case. The only time you need to use the copy_only option is when you want to run a backup outside of your scheduled process and you don't want to have to include this in your restore process.
Example backup in SQL Server 2005
Here is an example of a backup process and also the interjection of some copy_only backups in between. Also, we are issuing some transactions in between each backup to update data. When this process is complete we will have the following:
- 1 Full backup
- 2 Differential backups
- 6 Log backups
- 1 Full copy_only backup
- 1 Log copy_only backup
Click here to view the backup process example and copy-only backups.
Restoring regular backup files
If we want to restore to the most recent ...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
point in time for the regular backups when restoring the databases, we would issue the following.
Note: There is nothing different here from what you would normally do when restoring your database backups.
RESTORE DATABASE dbutil3 FROM DISK = 'c:\backup\dbutil_full.bak'
WITH MOVE 'dbutil' TO 'c:\sqldata\dbutil3.mdf', MOVE 'dbutil_log' TO
'c:\sqldata\dbutil3_log.ldf', NORECOVERY
RESTORE DATABASE dbutil3 FROM DISK = 'c:\BACKUP\dbutil_diff2.bak'
WITH MOVE 'dbutil' TO 'c:\sqldata\dbutil3.mdf', MOVE 'dbutil_log' TO
'c:\sqldata\dbutil3_log.ldf', NORECOVERY
RESTORE LOG dbutil3 FROM DISK = 'c:\BACKUP\dbutil_log5.trn'
WITH MOVE 'dbutil' TO 'c:\sqldata\dbutil3.mdf', MOVE 'dbutil_log' TO
'c:\sqldata\dbutil3_log.ldf', NORECOVERY
RESTORE LOG dbutil3 FROM DISK = 'c:\sqldata\dbutil_log6.trn'
WITH MOVE 'dbutil' TO 'c:\backup\dbutil3.mdf', MOVE 'dbutil_log' TO
'c:\sqldata\dbutil3_log.ldf', RECOVERY
Restoring COPY ONLY backup files
If we want to restore the copy_only backups, you would think you would issue the following to restore just the copy_only backups.
RESTORE DATABASE dbutil2 FROM disk = 'c:\backup\dbutil_full_copy.bak'
WITH MOVE 'dbutil' TO 'c:\sqldata\dbutil2.mdf', MOVE 'dbutil_log' TO
'c:\backup\dbutil2_log.ldf', NORECOVERY
RESTORE LOG dbutil2 FROM disk = 'c:\BACKUP\dbutil_log1_copy.trn'
WITH MOVE 'dbutil' TO 'c:\sqldata\dbutil2.mdf', MOVE 'dbutil_log' TO
'c:\sqldata\dbutil2_log.ldf', RECOVERY
But when running the above commands you get this error message:
Msg 4305, Level 16, State 1, Line 1
The log in this backup set begins at LSN 28000000033600001, which is
too recent to apply to the database. An earlier log backup that
includes LSN 28000000033300001 can be restored.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
To resolve this, you need to issue the following restores. As you can see we need to run the regular transaction log backup #5 that occurred after the full copy_only backup and prior to our copy-only log backup, in order for the restore process to work.
RESTORE DATABASE dbutil2 FROM disk = 'c:\backup\dbutil_full_copy.bak
'
WITH MOVE 'dbutil' TO 'c:\sqldata\dbutil2.mdf', MOVE 'dbutil_log' TO
'c:\sqldata\dbutil2_log.ldf', NORECOVERY, replace
RESTORE LOG dbutil2 FROM disk = 'c:\backup\dbutil_log5.trn'
WITH MOVE 'dbutil' TO 'c:\sqldata\dbutil2.mdf', MOVE 'dbutil_log' TO
'c:\sqldata\dbutil2_log.ldf', NORECOVERY
RESTORE LOG dbutil2 FROM disk = 'c:\BACKUP\dbutil_log1_copy.trn'
WITH MOVE 'dbutil' TO 'c:\sqldata\dbutil2.mdf', MOVE 'dbutil_log' TO
'c:\sqldata\dbutil2_log.ldf', RECOVERY
Summary
The copy-only option is a new feature of SQL Server 2005 allowing you to run these special backups without affecting the sequence numbers stored in the backup files. There are ways around the copy-only feature. If you issue another full backup during the day and need to do a recovery, just use that full backup and any differential or log files that occurred after. If you issue a special differential backup, you would again just use this backup when doing a restore. Also, if you issued a special transaction log backup you would just need to include this as well. I guess there is some situation where this would make sense, but overall there are other ways to get around this problem. See if you can find a problem where this would be the perfect solution.
ABOUT THE AUTHOR:
Greg Robidoux is the president and founder of Edgewood Solutions LLC, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and has delivered presentations at regional SQL Server users' groups and national SQL Server events. Robidoux, who also serves as the SearchSQLServer.com Backup and Recovery expert, welcomes your questions.
Copyright 2007 TechTarget
