THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Tibor Karaszi

COPY_ONLY backups and SSMS

Here's a suprising one:

SQL Server 2005 introduced the COPY_ONLY option of the BACKUP command. This is relevant for

  • Database backups. The backup will not intervene with your differential backups (will not reset the DCM pages).
  • Log backups. The backup will not break the log chain (empty the log).

There was a post in the newsgroup where a gentleman wanted to do a restore from a backup taken with COPY_ONLY. An he claimed that the GUI would not list that backup member from the backup file. I basically replied that my guess what that he didn't drive the backup GUI correctly (typing the RESTORE command worked just fine, of course). I also said that the backup you produce shod no evidence of being taken using the COPY_ONLY option, since this option only affects what happens with the source database (see above). Needless to say, I was incorrect (else you wouldn't read this blog post).

It turns out that SSMS does not list backups taken using the COPY_ONLY option. Here's a part of my reply from the newsgroup discussion (edited):

Let me try it and see if I get the same behaviour in the GUI as you describe:

BACKUP DATABASE pubs
TO DISK = N'C:\pubs.bak'
WITH INIT, COPY_ONLY

Right-click Databases folder, Restore Database, Type in "pubs" for database name, select "from device", press "..."
Backup media: File
File name: C:\pubs.bak, OK
OK
... and indeed, there is nothing listed!

OK, lets do the same except I don't specify COPY_ONLY... And now the backup is listed! So, my apologies. I was incorrect. I'm surprised that the backup somehow indicated it was done using COPY_ONLY. Let me try something else:

BACKUP DATABASE pubs
TO DISK = N'C:\pubs.bak'
WITH INIT

BACKUP DATABASE pubs
TO DISK = N'C:\pubs.bak'
WITH NOINIT, COPY_ONLY

RESTORE HEADERONLY FROM DISK = N'C:\pubs.bak'

Yes, RESTORE HEADERONLY does indicate whether the backup was done using COPY_ONLY. I see a difference in the "flags" column as well as the "IsCopyOnly" column. And the restore dialog only show the first backup. Let me now try the other way around:

BACKUP DATABASE pubs
TO DISK = N'C:\pubs.bak'
WITH INIT, COPY_ONLY

BACKUP DATABASE pubs
TO DISK = N'C:\pubs.bak'
WITH NOINIT

Now the restore dialog only show the second backup in the backup file (position 2). I get the same result if I type in some other database name to restore into (a non-existing database).

So, the restore dialog does indeed refuse to list backups done using COPY_ONLY. So here's another reason to type the RESTORE command instead of relying on how the GUI developer believe the restore should be done... :-)

Published Saturday, September 15, 2007 9:52 AM by TiborKaraszi
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

James Luetkehoelter said:

Great post. I've been burned by not having COPY_ONLY many times. I find it very frustrating that the GUI for backup and restore still has sooooooo many pitfalls (defaulting to recovery, striping backups if more than one file is listed, etc). COPY_ONLY and MIRROR TO are some of my favorite improvements in 2005.

September 16, 2007 11:28 PM
 

TiborKaraszi said:

Thanks James. I agree, there could be done much with the GUI part of the backup and restore experience. To name some of my "favourites":

Striping by mistake is far to easy, striping should be under an "Advanced" button.

CHECKSUM isn't exposed, nor in the maint plans. RESTORE VERIFYONLY doesn't do a "real" verification uless you did the backup with CHECKSUM. CHEKSUM should be the default in the GUIs.

I'm not sure that defaulting to RECOVERY is a bad thing to be honest (think about the inexperienced users). But if you selected "Prompt before restoring each backup", then I'd like the restores to be done using STANDBY and the user should be able to say "OK, I'm done now, don't restore any more backups and let me use the database".

Backups done with COPY_ONLY not showing up through the UI (this post) was a new one to me.

I'll Connect above...

September 17, 2007 2:22 AM
 

TiborKaraszi said:

September 17, 2007 2:24 AM
 

kai said:

Hi TiborKaraszi

I would like to do some correction with the article:

   * Database backups. The backup will not intervene with your differential backups (will not reset the BCM pages). *** This should be DCM Pages*** as I read from BOL May 2007 data and extent

Anyway the blog are great and help me a lot with my daily work as dba?

keep it up a good work :)

October 16, 2007 5:51 AM
 

TiborKaraszi said:

Thanks for the correction and the kind words, kai!

Yes, that should indeed have been DCM and not BCM (BCM pages is something different, used when in bulk logged recovery mode). I've changed the blog post accordingly. Good catch!

:-)

Tibor

October 17, 2007 11:45 AM
 

Openstrings said:

Thanks for the great blog. I'm newish to SQL and would like to know what the purpose of the N is in "TO DISK = N'C:\pubs.bak'". SQL documentation does not specify using an N?

March 18, 2008 4:18 AM
 

TiborKaraszi said:

Thanks :-). The N in front of a string means that the string to follow is Unicode.

March 18, 2008 4:33 AM
 

openstrings said:

Thanks!

March 18, 2008 4:35 AM
 

MarkRovendro said:

You might want to take a look at the SQL Server 2008 Management Studio. It appears they have resolved the issue of seeing 'Copy Only' backups in addition to adding a switch on the General page for performing a Copy Only backup.

Since you can manage 2005 databases with the new GUI is might be of some value for those more GUI oriented.

January 26, 2009 11:31 AM
 

TiborKaraszi said:

Cool. I see now that SSMS 2008 does it right when select "From Device". And as well the "Copy Only" option in the backup dialog. Thanks for the update, Mark.

January 27, 2009 7:28 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement