What do these Copy Only Backup options mean?

Question

I am currently trying to backup an empty SQL Server 2008 R2 database that I designed for a project that is getting shelved for the time being. I was going through the back up procedure through the SQL Management Studio when I noticed there was an option to make a Copy Only Back Up. I looked it up to see what it was but I didn't fully understand the options I was getting.

http://technet.microsoft.com/en-us/library/ms191495.aspx

I read the entry above as well as other entries and I keep seeing the phrase "independent of the sequence of conventional SQL Server backups."

Can anyone elaborate what this statement means or more about Copy Only Backups in general? I'm not sure if it's the backup I should do in this case? (My first reaction is no)

Answer 1

It's a full dump of a database, where you intent to take that dump and load it into some OTHER sql server instance. e.g. It's a nice way of making a complete copy of a DB without having to take down the db, detach the db, copy the .mdf files, re-attach, etc...

Naturally, since you're not using this "backup" as an actual backup, you don't want it to interfere with your normal backup schedules, hence the copy-only functionality. It's a full backup, but will not reset the backup schedule, so your normal next incremental/snapshot backup will work as usual.

This mechanism is necessary since the built-in hotcopy/migration tools in MSSMS are basically useless and can't handle its own databases in many cases.

Answer 2

Normally when you take a backup, it starts (or continues, depending on the type of backup that you took) what is called a log chain. Let's say that you need a copy of your database and, for whatever reason, you can't use your normally scheduled backups for this purpose. Let's walk through the scenario where you don't use a copy_only backup

1. Normal full backup
2. A bunch of differential backups
3. Another full backup (to make your copy database)
4. More differential backups
5. Delete the backup from step 3 (you know... to save space)
6. Disaster on your actual database that necessitates restore from backup

In this case, you can only restore to the last differential backup made in step 2 because the differential backups made in step 4 depend on the full backup from step 3. Now, if the backup in step 3 were a copy_only backup, you'd be fine because you're not re-establishing a log chain (which is to say that the differential backups in step 4 depend on the full backup from step 1.