Sql-server – Delete Only Log Backup History in SQL Server

As with all things in SQL Server, it depends.

First thing you need to do is make sure you understand what each type of backup does.

Books Online has all the gooey details, but here's my summary.

A FULL backup contains everything within the database. A DIFFERENTIAL backup is cumlative NOT incremental. In your example, if your database failed on the 12th, then you'd only need to restore the full backup from the 1st and then the most recent differential on the 12th, then followed by all the transaction log backups upto the failure. A TRANSACTION LOG backup is only needed for databases using the full or bulk-logged recovery model. If you're using the simple recovery model then transaction log backups are not needed.

Now that we've cleared that up...Designing a backup schedule really depends on how much data you need to recovery and how fast you need to recover it in the event of a diaster. I would recommend starting with a full backup each day. You can always reduce the frequency later. Remember the differential backup is cumlative since the last full, so depending on the amount change going on in your database the differential could be larger than the full backup after a few days. If you do a full backup each day, then you may not need to use differentials at all; however you could still do it once a day and schedule it at 12 noon. The transaction log backup only backs up the log. The frequency of the log backup will determine how much data you're willing to lose in the event of a failure. If you run your log backup every 15 minutes, then you would expect to lose up to the last 15 minutes of data that changed. 15 minutes is a good frequency, but every 30 minutes works perfectly for my environment.

As I said earlier, it all depends on your environment. After you've designed and setup your backup schedule, remember to test it on an alternate server. Practice restoring your full, diff, and log backups so that you know everything works like you designed it.

Books Online has some good info if you plan use Maintenance Plans, but if you really want flexibility then check out Ola Hallengren's backup scripts.

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:

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 COPY_ONLY full backup as the base of your Log Restore.

Differential tests

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 base 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.