A copy-only backup is a backup of SQL Server which is separate from the sequence of standard SQL Server backups.
What is a copy-only backup?
Whenever you make a backup of a database, there are some changes that influence how the following backups are going to be restored (differential backups and transaction log backups both influence how the restore operation will be).
But sometimes you might need a backup that will not affect the following processes of backup or restore for that database. This is where copy-only backups come into place.
Types of copy-only backups
There are different types of copy-only backups, which are used in different scenarios.
Copy-only FULL backups – are used for all recovery models
- Such a backup cannot be used as a base for differential backups and also it does not affect the differential base.
- Doing a restore of a copy-only full backup is the same as doing a restore on any other database that has been fully backed up.
Copy-only log backups – are used only for a full recovery and bulk-logged recovery models
- This type of backup will preserve the existing point of the log archive and thus it will have no impact on the sequence of regular log backups.
- These copy-only log backups are rarely useful, but you can create a routine of backing up the log by using the WITH NORECOVERY option and use this backup along with any previous log backups that might have been required for a restore.
- But, a log backup of this type can at some point be used for making an online restore.
- In the case of a copy-only log backup, the transaction log is never truncated.
- Copy-only FULL backups – are used for all recovery models
How to create a Copy-Only Backup
There are multiple options for creating such a backup, but we will present one using T-SQL and one through an SSMS wizard.
With T-SQL is quite simple, as you just need to specify the COPY_ONLY option in the WITH clause.
BACKUP DATABASE myDatabase TO DISK = 'Z:\myDatabase.bak' WITH COPY_ONLY
Even though SSMS the process is quite simple as you just have to tick a checkbox during the standard backup process.
Below you can see where you have to check the tickbox and the rest of the process is standard.
5 thoughts on “Copy-Only backups”
Valuable information and facts. Blessed myself I stumbled upon your internet-site by mistake, for astonished precisely why this kind of twist of fate could not occurred ahead of time! I actually saved this.
Thank you for these great words of encouragement! Will definetly keep me going on writing articles on these topics.
Please re-visit to get access to more knowledge in hopefully an easy to understand format.
Great Artical…easy to understand
In copy only backup option transational log lsn takes from last full backup or diffrential backup
Thank u . Its really usefull to me