Need to back up a database with out breaking the log sequence? Use Copy only backup

Sometime we face a situation where users of a database want us to take an ad-hoc full backup. This can create lots of constraints from Infrastructure point of view. For example taking a full backup breaks the backup sequence and hence following differential backups or log backups consider the ad-hoc backup as the latest full backup till next automated full backup is taken. This can add unnecessary confusion and complexity of the established backup protocol for the database.

This issue is resolved from SQL Server 2005 onwards where we can take a full (and log also) backup with out breaking the conventional “backup sequence”.  This new technology is called “Copy only backup”.

You can use copy only backup using one of the following way:

TSQL (Applicable for SQL 2005 onwards)

-- Note: All other options of backup is supported BACKUP DATABASE 'Database which is backed up'

TO  DISK = 'Complete path of backup file'

WITH  COPY_ONLY

GO

Management Studio (Applicable for SQL 2008 onwards)

Check the copy only backup option.

Also note that copy only log backup is possible but widely not used unless you are using it for online restore technology.