How to create a copy only backup in SQL Server

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'

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

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



Ing. Eduardo Castro Martínez, PhD – Microsoft SQL Server MVP

Costa Rica

Technorati Tags: SQL Server

LiveJournal Tags: SQL Server Tags: SQL Server

Note: Cross posted from Eduardo Castro.



%d bloggers like this: