Recently I have been looking at the COPY_ONLY feature for taking backups of a database. COPY_ONLY was introduced in SQL Server 2005, and allows you to take ad-hoc backups of either the log or a full backup without breaking the backup chain for differential backups. The COPY_ONLY backup is not part of the restore log: so when restoring from a full backup you can ignore it. The same here can also be said for log COPY_ONLY backups; it’s an ad-hoc backup that does not alter the transaction log at all; there’s no clearing down of the log.

It’s important to note here that a full backup that is not COPY_ONLY will not break the log chain; you can take a full backup and it will not affect the log chain at all, it will only break the differential chain. Additionally, you can restore any logs taken against the database to the COPY_ONLY version to roll it forward from a restore.

As an example, I’m going to restore a database and make some changes to it, taking a COPY_ONLY backup, making some more changes to the database, and then dropping and restoring both the original backup and the COPY_ONLY backup and restoring the log to it.

You need to have to restore a copy of the database you recently backed up on the server. I’m using a copy of AdventureWorks because it’s small and quick to backup/restore, and because it;s easy to clear the backup history to make the example below easy to follow.

Restore AdventureWorks from a full backup taken on this server, making sure it is in full recovery mode:

USE [master]

RESTORE DATABASE [AdventureWorks2012] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\AdventureWorks2012.bak' WITH FILE = 2, NOUNLOAD, STATS = 5

<a href="https://phoenixultd.files.wordpress.com/2015/09/1.png"><img class="aligncenter size-full wp-image-3579" src="https://phoenixultd.files.wordpress.com/2015/09/1.png" alt="1" width="348" height="330" /></a>

GO

You can check the recovery model through the UI or SQL:

SELECT name AS [Database Name],

recovery_model_desc AS [Recovery Model]

FROM sys.databases

GO

Make some changes, like drop an index

USE [AdventureWorks2012]

GO

/****** Object: Index [AK_Employee_LoginID]  Script Date: 18/08/2015 08:52:44 ******/

DROP INDEX [AK_Employee_LoginID] ON [HumanResources].[Employee]

GO

Now run a copy_only backup and make another change before backing up the log.

BACKUP DATABASE [AdventureWorks2012]

TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\copy_only.bak'

WITH COPY_ONLY, NOFORMAT, NOINIT, NAME = N'AdventureWorks2012-Full Database Backup'

GO

USE [AdventureWorks2012]

GO

/****** Object: Index [IX_PersonPhone_PhoneNumber]  Script Date: 18/08/2015 08:54:27 ******/

DROP INDEX [IX_PersonPhone_PhoneNumber] ON [Person].[PersonPhone]

GO

BACKUP LOG [AdventureWorks2012]

TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\log.trn'

WITH NOFORMAT, NOINIT, NAME = N'AdventureWorks2012-Full Database Backup'

GO

Check the backup history tables in MSDB for the Adventureworks database. You can see that both the copy only and log backup have the same backup_lsn number as the full backup you used to restore this database from. After you have checked this drop the database.

SELECT

[name],

[backup_start_date],

[type],

[first_lsn],

[database_backup_lsn]

FROM

[msdb].[dbo].[backupset]

WHERE

[database_name] = N'adventureworks2012';

GO

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'AdventureWorks2012'

GO

USE [master]

GO

ALTER DATABASE [AdventureWorks2012] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

USE [master]

GO

DROP DATABASE [AdventureWorks2012]

GO

Now let’s restore the copy-only and the log backup: this confirms that we can restore the log to the copy only backup that was taken.

USE [master]

RESTORE DATABASE [AdventureWorks2012]

FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\copy_only.bak'

WITH FILE = 1

, STANDBY = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\AdventureWorks2012_RollbackUndo_2015-08-18_08-58-39.bak',

NOUNLOAD, STATS = 5

GO

RESTORE LOG [AdventureWorks2012]

FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\log.trn'

WITH FILE = 1, NOUNLOAD, STATS = 10

GO

Drop the database again, and now restore the original backup file and the log backup. Both will run successfully.

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'AdventureWorks2012'

GO

USE [master]

GO

/****** Object: Database [AdventureWorks2012]  Script Date: 18/08/2015 09:02:00 ******/

DROP DATABASE [AdventureWorks2012]

GO

USE [master]

RESTORE DATABASE [AdventureWorks2012] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\AdventureWorks2012.bak'

WITH FILE = 2,

STANDBY = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\AdventureWorks2012_RollbackUndo_2015-08-18_09-02-53.bak',

NOUNLOAD, STATS = 5

 

GO

 

RESTORE LOG [AdventureWorks2012]

FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\log.trn'

WITH FILE = 1, NOUNLOAD, STATS = 10

GO

It doesn’t make a great deal of sense to have full backups running outside of your typical backup strategy, as it makes the restore strategy confusing. It’s a bit of a cliche to say that you need to test your restore strategy before the event of a disaster, but it really is true. The key is to always have an unbroken chain of log backups since your last FULL backup to restore to, and only use ad-hoc backups, be they COPY_ONLY or not, when you really need to. And be aware as to what will break your log backups: COPY_ONLY log backups will not break the log chain, but What will break the log chain are ad hoc non-COPY_ONLY log backups, and they must be stored along with other log backups so that they can be restored to. And if you’ve just inherited a server, check for any jobs[1] that run log backups: if these are not stored in the same location as your other log backups, you’re in trouble when you come to restore!

[1] It’s also worth having a check of any alerts configured for the server: I once came across an alert that was configured for severity 17 to kick off an ad-hoc log backup to a totally different location to the normal backups. What made this worse was that this database was log shipped! So if this alert was ever triggered, then log shipping would be broken, and no one would have had a clue why!