Copy only backup in SQL server and not break the transaction log backup chain

 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:

1)MYDATABASE271209_FULL.BAK 

2)MYDATABASE281209_DIFF.BAK

3)MYDATABASE291209_FULL.BAK 

4)MYDATABASE301209_DIFF.BAK

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.

Read More

SQL Server - Predict SQL BACKUP DATABASE finish time with sys.dm_exec_requests

SQL Server – Restore Database Estimated Finish Time

SQL Server - Display restore history for a single database

SQL Server - Display sql backup history for a single database

SQL Server - Move MDF/LDF files to a designated path

SQL Server - BackUp All Databases

SQL Server BACKUP folder in windows registry