COPY_ONLY backup option more obvious in SQL Server 2008

June 20, 2008

I feel like I keep finding reasons to use SQL Server 2008 Management Studio with SQL 2005 instances, and here’s another – the COPY_ONLY option now appears on the Backup dialog box (others include the DROP & CREATE scripting option, multi-server queries and the Select Top 1000 Rows menu entry).copyonly

It’s not a new option (it just wasn’t part of the dialog box), but there’s an option when backing up a database called COPY_ONLY. It goes in the WITH section. It means that the backup you’re taking won’t be included in your ordinary backup/restore sequence. Let’s paint a scenario.

Your system takes a full database backup on a Sunday morning. Monday, Wednesday and Friday you do differential backups. Everything’s great, each of the differential backups only looks at changes since Sunday, when the last full backup was taken.

Except that one Thursday, one of the developers wants to refresh the test environment with a recent copy of the production system. Instead of finding Sunday’s backup and using that, they make a new backup, and restore that onto test. Everything works, and that new backup gets deleted.

Which means that Friday’s differential backup is based on the Thursday backup instead of Sunday. When a disaster occurs on Saturday, you’re in a bit of strife.

If the developer had used the COPY_ONLY option, then Friday’s differential would still’ve been based on Sunday’s backup. That’s the whole point of COPY_ONLY – it doesn’t reset all the flags that indicate which data should be included in the differential.

Like I said earlier, it’s not new – it’s was introduced in SQL 2005. I don’t know why it didn’t appear in the SQL 2005 dialog box – it didn’t even turn up in a service-pack. Since so many people learn about features through the user-interface, it really could’ve helped educate users about this useful option.

This Post Has 6 Comments

  1. thommo

    is there anyway of doing the copy only backup in SQL 2005 then?

  2. Rob Farley

    Yes, like I said:

    “It’s not a new option (it just wasn’t part of the dialog box)…”

    You need to put “WITH COPY_ONLY” on your backup.

    Rob

  3. alex hatcher

    nice story, but you got bigger problems than that.

    BACKUP DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles.

    So, you are okay with developers having SYSADMIN or db_owner of DBs in production?

  4. robfarley

    Hi Alex,

    Ok, so put “Junior DBA” in there instead of “Developer”. I’d expect that a junior DBA would at least have db_backupoperator rights.

    The situation still applies. A backup which might seem safe can still wreck the restore sequence, but this can be avoided using COPY_ONLY.

    Rob

  5. Adam

    If you want to allow developers to make backups and you do not want to disrupt the restore sequence then you can do what I did.

    Using copy_only:
    Create a stored procedure and give execute permissions to the developer group. Next when you create the stored procedure include execute as self in the create script so the stored procedure can run backups. This way developers dont need sysadmin or db_backupoperator rights and can still run backups. I have the sproc outputting backups to a share that the developers have access to.

  6. robfarley

    Adam,

    Yes, this is a very valid way of doing it. One of the things I love about EXECUTE AS is being able to make stored procedures to do things that users wouldn’t normally have access to.

    Rob

Leave a Reply

LobsterPot Blogs

Blog posts by Rob Farley and other LobsterPot Solutions team members.

Search