Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

  1. Reliability of backups, having nightly full backups
  2. Transaction log backups that could be restored from
  3. Low amount of disc space used
  4. 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.

share|improve this question
2  
Why haven't you actually tried it before posting? –  spaghettidba Mar 19 '13 at 23:04
    
@spaghettidba, why so negative? The OP said he is new, so cut him a break. Backups in SQL Server are fairly complex and sometimes even experienced DBAs get the rules around them wrong. –  Sebastian Meine Mar 20 '13 at 1:55
    
@Sebastian while I agree with you, it is increasingly frustrating to see "what will happen if I do this?" or "which is faster, a or b?" questions with no effort applied to actually trying it on their own. We usually expect that people try things, and then come with problems, instead of littering the site with "what will happen if I do x?" questions. –  Aaron Bertrand Mar 20 '13 at 2:01
    
@Sebastian you're right, I'll try to be less "tranchant" in the future. However, trying the backup/restore solution would have saved a lot of time and increased the knowledge of the OP more than any baked solution we may post. –  spaghettidba Mar 20 '13 at 12:08
    
I think the danger of "just try it" is that you can sometimes stumble across a solution that seems to work, but you miss out on a crucial "Yes, but..." point. It's reasonable to be a little extra cautious about backup/restore procedures. How many posts on here are about "My DB is corrupt, I have one .bak that won't restore, and I don't have any old mdf files"? –  db2 Mar 20 '13 at 12:33

2 Answers 2

up vote 3 down vote accepted

A full backup in 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:

CREATE DATABASE BakTst13;
GO
ALTER DATABASE BakTst13 SET RECOVERY FULL;
GO
USE BakTst13;
GO
CREATE TABLE dbo.tst(id INT IDENTITY(1,1));
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13 
TO DISK = 'BakTst13_Full_1' WITH INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP LOG BakTst13 
TO DISK = 'BakTst13_Log_1' WITH INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13 
TO DISK = 'BakTst13_Full_2' WITH INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13 
TO DISK = 'BakTst13_Full_C' WITH COPY_ONLY,INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP LOG BakTst13 
TO DISK = 'BakTst13_Log_2' WITH INIT,FORMAT;
GO
USE tempdb;
GO
DROP DATABASE BakTst13;
GO
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Full_1' WITH NORECOVERY;
RESTORE LOG BakTst13 FROM DISK='BakTst13_Log_1' WITH NORECOVERY;
RESTORE LOG BakTst13 FROM DISK='BakTst13_Log_2' WITH RECOVERY;
GO
SELECT * FROM BakTst13.dbo.tst;
GO
DROP DATABASE BakTst13;
GO
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Full_C' WITH NORECOVERY;
RESTORE LOG BakTst13 FROM DISK='BakTst13_Log_2' WITH RECOVERY;
GO
SELECT * FROM BakTst13.dbo.tst;
GO
DROP DATABASE BakTst13;

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:

  1. Full
  2. Log
  3. Full
  4. Full Copy_Only
  5. Log

Next the database is dropped and restored like this:

  1. 1st Full
  2. 1st Log
  3. 2nd Log

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:

  1. Copy_Only Full
  2. 2nd Log

Afterwards the SELECT demonstrates success again.

This demonstrates that you can use a COP_ONLY full backup as the base of your Log Restore.


EDIT: Because of Aarons concern I created a DIFFERENTIAL version too:

CREATE DATABASE BakTst13;
GO
ALTER DATABASE BakTst13 SET RECOVERY FULL;
GO
USE BakTst13;
GO
CREATE TABLE dbo.tst(id INT IDENTITY(1,1));
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13 
TO DISK = 'BakTst13_Full_1' WITH INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13 
TO DISK = 'BakTst13_Diff_1' WITH DIFFERENTIAL,INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13 
TO DISK = 'BakTst13_Full_2' WITH INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13 
TO DISK = 'BakTst13_Diff_2' WITH DIFFERENTIAL,INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13 
TO DISK = 'BakTst13_Full_C' WITH COPY_ONLY,INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13 
TO DISK = 'BakTst13_Diff_3' WITH DIFFERENTIAL,INIT,FORMAT;
GO
USE tempdb;
GO
DROP DATABASE BakTst13;
GO
RAISERROR('------> Starting restore F1, D1, D2',0,1)WITH NOWAIT;
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Full_1' WITH NORECOVERY; 
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Diff_1' WITH NORECOVERY;
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Diff_2' WITH NORECOVERY;--<--Fails!
GO
DROP DATABASE BakTst13;
GO
RAISERROR('------> Starting restore FC, D3',0,1)WITH NOWAIT;
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Full_C' WITH NORECOVERY;
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Diff_3' WITH NORECOVERY;--<--Fails!
GO
DROP DATABASE BakTst13;
GO
RAISERROR('------> Starting restore F2, D2, D3',0,1)WITH NOWAIT;
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Full_2' WITH NORECOVERY; 
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Diff_2' WITH NORECOVERY;
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Diff_3' WITH RECOVERY;
GO
SELECT * FROM BakTst13.dbo.tst;
GO
DROP DATABASE BakTst13;

This takes backups in this order:

  1. 1st Full
  2. 1st Differential
  3. 2nd Full
  4. 2nd Differential
  5. Copy_Only Diff
  6. 3rd Differential

It then tries this restore route:

  1. 1st Full
  2. 1st Differential
  3. 2nd Differential

Step 3 fails with this error:

Msg 3136, Level 16, State 1, Line 4
This differential backup cannot be restored because the database has not been restored to the correct earlier state.

This shows that a normal full backup breaks the differential chain.

Next the database is dropped and this restore flow is attempted:

  1. Copy_Only Full
  2. 3rd Differential

Step 2 fails with the same error as step 3 above. This shows that a copy only backup cannot be used as the bas for a differential restore.

Then the database is dropped again and the following restore is executed:

  1. 2nd Full
  2. 2nd Differential
  3. 3rd Differential

The following select proves that this restore succeeded. This shows that a COPY_ONLY Full backup does not interrupt the differential chain.

share|improve this answer
    
+1 but the OP has differential backups in the question as well. So if you are going to present an answer that demonstrates this technique while ignoring any differential backups that might have been taken, you might also address whether or not those differential backups are going to serve any purpose if the copies are going to be used for restores instead of the fulls. In this case the OP only takes diffs after all the log backups for some reason, but the next reader might not and might expect their diffs to still be useful in combination with copy_only. –  Aaron Bertrand Mar 20 '13 at 2:06
    
Thank you for this, I was having issues getting it to work on my own. I didn't want this discussion to be about my error. –  dv10t Mar 20 '13 at 15:51

In response to Aaron's comment, here's what happens when differentials are involved.

CREATE DATABASE BakTst13;
GO
ALTER DATABASE BakTst13 SET RECOVERY FULL;
GO
USE BakTst13;
GO
CREATE TABLE dbo.tst(id INT IDENTITY(1,1));
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10 -- 10 rows so far
GO
BACKUP DATABASE BakTst13 
TO DISK = 'BakTst13_Full_1' WITH INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10 -- 20 rows so far
GO
BACKUP DATABASE BakTst13 
TO DISK = 'BakTst13_Diff_1' WITH INIT,FORMAT,DIFFERENTIAL
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10 -- 30 rows so far
BACKUP LOG BakTst13 
TO DISK = 'BakTst13_Log_1' WITH INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10 -- 40 rows so far
GO
BACKUP DATABASE BakTst13 
TO DISK = 'BakTst13_Full_2' WITH INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10 -- 50 rows so far
GO
BACKUP DATABASE BakTst13 
TO DISK = 'BakTst13_Full_C' WITH COPY_ONLY,INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10 -- 60 rows so far
GO
BACKUP DATABASE BakTst13 
TO DISK = 'BakTst13_Diff_2' WITH INIT,FORMAT,DIFFERENTIAL
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10 -- 70 rows so far
GO
BACKUP LOG BakTst13 
TO DISK = 'BakTst13_Log_2' WITH INIT,FORMAT;
GO


USE tempdb;
GO
DROP DATABASE BakTst13;
GO
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Full_2' WITH NORECOVERY;
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Diff_2' WITH NORECOVERY;
RESTORE LOG BakTst13 FROM DISK='BakTst13_Log_2' WITH RECOVERY;
GO
SELECT COUNT(*) FROM BakTst13.dbo.tst; -- Must be 70: log chain not broken
GO
DROP DATABASE BakTst13;
GO
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Full_C' WITH NORECOVERY;
RESTORE LOG BakTst13 FROM DISK='BakTst13_Log_2' WITH RECOVERY;
GO
SELECT COUNT(*) FROM BakTst13.dbo.tst; -- Must be 70
GO
DROP DATABASE BakTst13;

In a word: yes, you can use a COPY_ONLY backup to restore further log backups. What you cannot do is use the COPY_ONLY backup as a differential base. That means you will not be able to restore any differential backups on the restored COPY_ONLY backup:

DROP DATABASE BakTst13;
GO
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Full_C' WITH NORECOVERY;
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Diff_2' WITH NORECOVERY;

If you try this, you will get an error:

Processed 160 pages for database 'BakTst13', file 'BakTst13' on file 1.
Processed 2 pages for database 'BakTst13', file 'BakTst13_log' on file 1.
RESTORE DATABASE successfully processed 162 pages in 0.009 seconds (139.811 MB/sec).
Msg 3136, Level 16, State 1, Line 2
This differential backup cannot be restored because the database has not been restored to the correct earlier state.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

I agree differential backups can be hard to understand and can fool even experienced DBAs. In fact, yesterday I saw on this page a reply that was stating that COPY_ONLY backups break the log chain, which is obviously wrong. I wonder how a reply can be deleted, since it's not here any more.

share|improve this answer
    
Even though you posted this an hour ago, SE just showed it to me after I posted my update. I am going to leave my update in anyway because it adds two additional "test" cases. As for deleting answers, there is a delete link right next to the edit link under your answer. It is probably worth a discussion if it is a good practice, but it is possible to delete your own answers. –  Sebastian Meine Mar 20 '13 at 14:16
    
@Sebastian Thanks for clarifying. Actually, I have a "vote to delete this post" link next to the edit link. Maybe it's a privilege unlocked with more rep than I have. –  spaghettidba Mar 20 '13 at 14:19
    
must be 500 then, because I see it... –  Sebastian Meine Mar 20 '13 at 14:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.