In this article, we look at the non-traditional copy-only backups and explain the steps needed to create them with SSMS
A copy-only backup can be defined as a form of SQL Server backup which is independent of the conventional sequence of SQL Server backups. Usually, using a backup can make significant changes in a database, that can affect how later backups would be restored. However, sometimes it can turn out to be really useful to use a backup without affecting the original documents or its restore procedures in the database. Thankfully Microsoft provides a perfect solution for such scenarios in the form of Copy-only; using which user can create a copy of their backup and make the experimental changes in it instead of the original backup.
Key types of Copy-only Backups:
• Copy-only for Full backups (for all recovery models)
The user cannot use a copy-only backup as a differential backup or differential base, as it does not affect or relate to the differential base.
Users can use the same method as they would use to restore any other form of full backup, for restoring full copy-only backup.
• Copy-only for log backups (for full recovery models and bulk-log recovery models only)
A copy-only backup for log backups only preserves the existing log at the archive point; therefore, it does not affect or relate to the sequencing of the regular log backups. Most users consider Copy-only backups for logs to be typically unnecessary. Instead, they prefer creating a new routine for log backup (by using WITH NORECOVERY) or by using that new backup together with old or previous log backups which are necessary for sustaining the restore sequence.
Note: Transaction log does not truncate after making or using the copy-only backup. Always keep a tool that can recover mdf databases handy while exercising this option.
Also, Copy-only backup is stored or recorded in the is_copy_only column on the back_up_set table.
Difference between Copy Only and Full Backups
One of the key differences between Copy-Only and Full backups comes from whether the Log Sequence Number(LSN), or more specifically whether the DatabaseBackupLSN, is updated on the SQL Server or not.
When a user takes a Full backup, he/she automatically updates its DatabaseBackupLSN. After creating a full backup, if the user takes a Differential backup, then that backup should have a DatabaseBackupLSN that should match with Full backup, if their DatabaseBackupLSN doesn’t match then the SQL won’t link them together.
Steps to Create Copy-Only Backups using SSMS
Through this step by step guide, the user can learn to create a copy-only backup for a Sales database that will be independently backed up on the disk at the same location as the default backup.
Step 1: Go to Object Explorer, and connect to SQL Server Database Engine’s instance and then expand the instance in the database.
Step 2: Now user needs to the Expand Databases that he/she wants to copy, right-click on Database file, bring your cursor to the Tasks, and then select Back Up.
Step 3: Now in order to locate the new backup copy go to the General page by selecting it in the Source section and check out the Copy-only backup check box.
Step 4: Click OK and your Copy-only backup will be saved.
Author Introduction:
Victor Simon is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including access recovery and sql recovery software products. For more information visit www.datanumen.com
Leave a Reply