Subscribe

SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

Powered by TypePad
Copy only backup in SQL server 2005 and not break the backup chain

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:

 

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


Author: Jack Vamvas (http://www.sqlserver-dba.com)

******** *******

Subscribe to SQLServer-DBA.com

Latest posts delivered to you daily

Delivered by FeedBurner

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer