COPY_ONLY backup option more obvious in SQL Server 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).
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.