May 28, 2011 at 10:48 am
I do a restore on prodcution server from my development server on a regular basis using a regular full backup but can i use a copy only backup to speed up the backup and restore process and does it change anything with the data, just want to make sure i have a exact same copy of development on production.
May 28, 2011 at 11:15 am
brom BOL: http://msdn.microsoft.com/en-us/library/ms191495.aspx
A copy-only backup is a SQL Server backup that is independent of the sequence of conventional SQL Server backups. Usually, taking a backup changes the database and affects how later backups are restored. However, occasionally, it is useful to take a backup for a special purpose without affecting the overall backup and restore procedures for the database. For this purpose, copy-only backups were introduced SQL Server 2005. The types of copy-only backups are as follows:
Copy-only full backups (all recovery models)
A copy-only full backup cannot serve as a differential base or differential backup and does not affect the differential base.
Copy-only log backups (full recovery model and bulk-logged recovery model only)
A copy-only log backup preserves the existing log archive point and, therefore, does not affect the sequencing of regular log backups. Copy-only log backups are typically unnecessary. Instead, you can create another routine, current log backup (using WITH NORECOVERY), and then use that backup together with all other previous log backups that are required for the restore sequence. However, a copy-only log backup can be created for performing an online restore. For an example of this, see Example: Online Restore of a Read/Write File (Full Recovery Model).
The transaction log is never truncated after a copy-only backup. Copy-only backups are recorded in the is_copy_only column of the backupset table.
The data content is equal than if you would have created a regular backup.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 28, 2011 at 11:28 am
A copy_only backup isn't going to speed anything up. It's a backup that simply doesn't change the differential base, nothing more.
http://sqlinthewild.co.za/index.php/2011/03/08/full-backups-the-log-chain-and-the-copy_only-option/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 28, 2011 at 12:20 pm
GilaMonster (5/28/2011)
A copy_only backup isn't going to speed anything up. It's a backup that simply doesn't change the differential base, nothing more.http://sqlinthewild.co.za/index.php/2011/03/08/full-backups-the-log-chain-and-the-copy_only-option/
Perhaps an easier to understand way of saying this is that a copy-only full backup does not become part of the backup chain with differential backups (subsequent differential backups are based on the previous full backup, not this one). Furthermore, a copy-only t-log backup does not become part of the t-log chain.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply