05 October,2010 by Jack Vamvas
An emergency request was made to me to restore a database back to last nights position. Which was the full backup from Saturday and Differential of Tuesday night. The normal backup schedule is a FULL backup on Saturday and DIFFERENTIAL every night.
The Restore of the full backup was OK – but restoring the DIFFERENTIAL on Tuesday generated an error . I double checked the TSM backup logs and there had been no errors on the BACKUP. Upon investigation of the SQL Server Logs – I noticed an adhoc backup had been taken by a Developer to disk - for the purpose of Restoring to a Development Server.
What compounded the problem was that they then deleted the backup – and dropped the database from the Development Server
The command they’d executed was like :
BACKUP DATABASE MYDATABASE TO DISK = ‘E:\MYDATABASE291209_FULL.BAK’
This command breaks the normal backup chain. The DIFFERENTIAL will use the last full backup as it’s base.
Let’s look at the sequence:
If backup taken at step 3 had not been taken – then we’d be able to restore to position 4. But under the circumstances we can only restore to step 2. The alternative would be to use the COPY_ONLY argument. As such:
BACKUP DATABASE MYDATABASE TO DISK = ‘E:\MYDATABASE291209_FULL.BAK’ WITH COPY_ONLY
Adding this argument means that the backup will not affect the normal sequence of backups and restores. This also means that the backup taken with the COPY_ONLY cannot be used as a base backup ,i.e subsequent DIFFERENTIALS will assume that the COPY_ONLY backup does not exist.