Broken BACKUP and RESTORE sequence?

Have you ever had the to scratch your head because your database backups won't work even if you are practically sure that you have tested them properly? Imagine this - you have a regular FULL and DIFFERENTIAL database backups working together as part of your backup process. Your FULL backups run every Sunday and your DIFFERENTIAL backups run everyday. Then, all of a sudden on a particular Wednesday, your developers decided to restore a FULL backup of your production databases into your test environment to test a functionality. What happens? The backup sequence is broken. DIFFERENTIAL backups contain the changes since the last FULL backup. If you are following the sequence and working with the backups generated by your backup process, you would have done some tests using the Sunday FULL backup and the latest DIFFERENTIAL backup. Not in this case. You would need the FULL backup created on that specific Wednesday (which unfortunately was deleted after being restored on the test environment). Let me show you a sample code to demonstrate


USE master
GO

--FULL DATABASE BACKUP on Sunday
BACKUP DATABASE AdventureWorks
TO DISK = 'D:\AdventureWorks_FULL.BAK'
WITH INIT, FORMAT, STATS=10
GO

USE master
GO

--FULL DATABASE BACKUP ON Wednesday CREATED BY YOUR DEVELOPERS
BACKUP DATABASE AdventureWorks
TO DISK = 'D:\AdventureWorksD.BAK'
WITH INIT, FORMAT, STATS=10
GO

USE master
GO

--DIFFERENTIAL DATABASE BACKUP AFTER THE LAST FULL BACKUP
BACKUP DATABASE AdventureWorks
TO DISK = 'D:\AdventureWorks_DIFF.BAK'
WITH DIFFERENTIAL, INIT, FORMAT, STATS=10
GO

If you're the DBA, you might be thinking that you still have your backup sequence intact and this is what you might have done should you need to restore

--RESTORE SEQUENCE STARTS
RESTORE DATABASE AdventureWorks
FROM DISK = 'D:\AdventureWorks_FULL.BAK'
WITH REPLACE, NORECOVERY, STATS=10
GO

--FAIL DUE TO INCONSISTENT RESTORE SEQUENCE
RESTORE DATABASE AdventureWorks
FROM DISK = 'D:\AdventureWorks_DIFF.BAK'
WITH REPLACE, NORECOVERY, STATS=10
GO

Now, there are a couple of ways to solve this problem. One is to create a trigger on your MSDB database to trap events written on your backupset system table and check for the type column for D values. At least you get to be notified for any FULL database backups outside of your normal backup process. In SQL Server 2005, it's as easy as using the COPY_ONLY option in the BACKUP command. This specifies that the backup is a copy-only backup, which does not affect the normal sequence of backups. Make sure that your operational DBAs and developers who work on SQL Server 2005 know how to use this option whenever thay run ad-hoc backups. Here's how to use the COPY_ONLY clause on your BACKUP commands (with emphasis on COPY_ONLY)

--FULL DATABASE BACKUP on Wednesday
BACKUP DATABASE AdventureWorks
TO DISK = 'D:\AdventureWorksD.BAK'
WITH INIT, FORMAT, STATS=10, COPY_ONLY
GO