How to create a copy only backup in SQL Server

- July 14, 2010

If you have scheduled regular backups in SQL Server, there is sequence LSN for every backup you made, if you perform a temporal full backup without the copy only option that sequence will affected and you may face problems during a recover, because during a recovery you may be asked for the temporal full backup you made and if you don’t have it you will face problems during recovery. The following script creates a backup with the copy only option.

-- Create full backup with Copy only option enabled
BACKUP DATABASE AdventureWorks2008R2
TO DISK = 'C:\AdventureWorks.bak'
WITH COPY_ONLY
GO

You can use the following script to find out the LSN backup sequence number:

SELECT database_name, backup_start_date, is_copy_only,
first_lsn
FROM msdb..backupset
WHERE database_name = 'AdventureWorks2008R2'
ORDER BY backup_start_date DESC
GO