After doing some research I cannot seem to find an answer to this question.
Background
I am attempting to setup a backup plan that fits the following three requirements:
- Reliability of backups, having nightly full backups
- Transaction log backups that could be restored from
- Low amount of disc space used
- The backups must be accessible locally for an auditing tool
So to fit those needs I am thinking full backups weekly, differential daily, and transaction hourly. Then each night a copy_only backup would run that can be shipped offsite, this backup is done so that the log chain is not broken, and we have reliable nightly full backups offsite, without having to eat up so much local disc space.
Question
Is it possible to restore from the copy_only backup, and restore the transaction logs after.
Let me just give an example so you know what I am talking about.
Using the below list I am wondering if it is possible to restore FullbackupCOPY_ONLYC.bak followed by TransactionbackupG.trn, TransactionbackupH.trn, finally TransactionbackupI.trn
> ---List of Backups---
FullbackupA.bak 01/01/2013 00:00:00
> DifferntialbackupA.bak 02/01/2013 00:00:00
FullbackupCOPY_ONLYA.bak 02/01/2013 00:00:00
> TransactionbackupA.trn 02/01/2013 01:00:00
> TransactionbackupB.trn 02/01/2013 02:00:00
> TransactionbackupC.trn 02/01/2013 03:00:00
> DifferntialbackupB.bak 03/01/2013 00:00:00
FullbackupCOPY_ONLYB.bak 03/01/2013 00:00:00
> TransactionbackupD.trn 03/01/2013 01:00:00
> TransactionbackupE.trn 03/01/2013 02:00:00
> TransactionbackupF.trn 03/01/2013 03:00:00
> DifferntialbackupC.bak 04/01/2013 00:00:00
FullbackupCOPY_ONLYC.bak 04/01/2013 00:00:00
> TransactionbackupG.trn 04/01/2013 01:00:00
> TransactionbackupH.trn 04/01/2013 02:00:00
> TransactionbackupI.trn 04/01/2013 03:00:00
Maybe this whole setup is not reasonable I am fairly new to SQL Server and am trying to learn as I go. Any advice/help would be appreciated.
Best Answer
A full backup in SQL Server 2008 does not break the log chain. It only resets the differential base-lsn.
You also can restore log backups after restoring from a copy only. The following script demos that:
It creates a database and a table and inserts 50 rows into that table. In between those inserts several backups are taken in this order:
Next the database is dropped and restored like this:
The following
SELECT
demonstrates that the restore was successful.This shows that neither a
COP_ONLY
nor a normal Full Backup break the log chain.Then the database is dropped again and restored like this:
Afterwards the
SELECT
demonstrates success again.This demonstrates that you can use a
COPY_ONLY
full backup as the base of your Log Restore.Differential tests
I created a
DIFFERENTIAL
version too:This takes backups in this order:
It then tries this restore route:
Step 3 fails with this error:
This shows that a normal full backup breaks the differential chain.
Next the database is dropped and this restore flow is attempted:
Step 2 fails with the same error as step 3 above. This shows that a copy only backup cannot be used as the base for a differential restore.
Then the database is dropped again and the following restore is executed:
The following select proves that this restore succeeded. This shows that a
COPY_ONLY
Full backup does not interrupt the differential chain.