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:
- Full
- Log
- Full
- Full Copy_Only
- Log
Next the database is dropped and restored like this:
- 1st Full
- 1st Log
- 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:
- Copy_Only Full
- 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:
- 1st Full
- 1st Differential
- 2nd Full
- 2nd Differential
- Copy_Only Diff
- 3rd Differential
It then tries this restore route:
- 1st Full
- 1st Differential
- 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:
- Copy_Only Full
- 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:
- 2nd Full
- 2nd Differential
- 3rd Differential
The following select proves that this restore succeeded. This shows that a COPY_ONLY
Full backup does not interrupt the differential chain.
If the logs are full, then yes you will probably be looking at needing the same space to backup them up.
If you're running SQL Server 2008 Enterprise then you may find that enabling backup compression will save you plenty of space, unfortunately the only way to really find out how much is to run the backup (do you have a dev environment where you could restore a copy to?).
The other option if you don't need the ability to restore to a point in Time previous to when you do this, would be to:
put the database into Simple Recovery mode
Checkpoint
put database back into Full Recovery
Take Full backup
Start taking log backups.
By putting the database into Simple Recovery you break the log chain, so when you move back to Full Recovery there will be no log as such for the first log backup to take. Don't forget to take the Full Backup so that SQL Server can start a new log chain.
As mentioned this will mean you'd lose the ability to recover the database to 15:25 21/10/2012 for example. But you'd still be able to restore to a full or differential backup.
Added 03/06/2013
Sorry, I completely forgot this option as well:
Do you have any remote storage options? You can just backup the transaction logs to a UNC path. This may be a bit slower depending on your network but it'll mean you'll have a full transaction backup covering the previous period and can then start taking smaller regular backups.
Best Answer
Reference Reading / Similar Q&As
You might want to check out my answer that I posted in response to the question: Will VSS backups break logchain? (dba.stackexchange.com)
The explanation in my answer also links to the question How can I backup an SQL Server database using Windows Server Backup? (serverfault.com) which was also answered by myself.
Transaction Log Chain
When a Transaction Log (TLOG) backup is performed, the backup information is stored in the msdb database in various tables. The information stored will contain information like
backup_type
,logical_device_name
,physical_device_name
,is_copy_only
,is_snapshot
, and various..._lsn
columns (lsn = log sequence number).You can retrieve the transaction log backup chain information from your SQL Server instance via the msdb database with the following script:
Caution: The where clause currently select the AdventureWorks2012 database
Broken Transaction Log Chain
The (transaction) log chain is never broken unless one of the following conditions is met:
TRUNCATE_ONLY
COPY_ONLY
option and was then deleted from disk because the developers only needed a quick backup to analyse a situation in the database and yourFULL
backup before that was deleted by (a) backup procedure.Your Situation
In the screenshot you provided you have a
FULL
backup of the database that isis_copy_only
and shortly after aFULL
backup that is notis_copy_only
. Now what you don't know:Is the second
FULL
, non-is_copy_only
backup a snapshot or not?If you use my script from above and change the
WHERE
clause to match your database name, you might find out that thatFULL
backup that is notis_copy_only
might just be ais_snapshot
.And that might just open up a new question:
Will the
FULL
,is_snapshot
database backup of my database break the log backup chain?But...
....whichever way this goes, as long as you have an unbroken chain of
FULL
andTLOG
backups you can access, you can still restore your database to any point in time, even if you have anotherFULL
backup in-between.You can verify this with the output of my script for your database, by looking at the
first_lsn
andlast_lsn
numbers. They should match up, even when bypassing aFULL
backup.Better Be Safe Than Sorry
I have an
AdminDB2
database on one of my instances. I created aTLOG
backup, modified data, performed aFULL
backup, modified data, performed aTLOG
backup, ....Lets have a look at my backup history of my
AdminDB2
:The order is date descending
You can see the last
TLOG
backup at the top, the previousFULL
(in-between) backup at2018-04-25 17:28:48.000
, the previousTLOG
backup at2018-04-25 17:28:23.000
, and so on.To restore the
AdminDB2
database to the current point-in-time I would have to use the firstFULL
backup from2018-04-25 17:27:32.000
(because I deleted the in-betweenFULL
backup) together with all theTLOG
backups.Let's give that a try.
FULL
backup fileAdminDB2_FULL_20180425_172848.bak
on my disk (or rename it), just because it is the one in-between.FULL
backupAdminDB2_FULL_20180425_172732.bak
TLOG
backup filesOverwrite the existing database (WITH REPLACE)
Script
Output
...and the database is back ONLINE.
Summary
The backup chain only breaks when you lose the TLOG backups in-between, other than that you can restore a database from a
FULL
backup a long time ago and just add all theTLOG
backups.However...
...it is faster to have a more recent
FULL
,DIFF
andTLOG
backups handy.Additional information in response to comment: Transaction Log backup was performed with the option TRUNCATE_ONLY - when this happens, is there any way to know this by T-SQL query
Backing Up Transaction Log With Truncate_only
In previous versions of SQL Server prior to SQL Server 2008 you could use the following statement:
This has been deprecated and is no longer supported. You will receive an error message like the following:
The new method is to backup to disk
NUL
and is performed with the following command:This will return the following information:
Reference: BACKUP (Transact-SQL) (Microsoft Docs)
Your backup history will show this as:
The information for the
logical_device_name
(ldev
) andphysical_device_name
(pdev
) will both contain the valueNULL
. This is a sign that aBACKUP LOG ...
was performed with aTRUNCATE_ONLY
(new:TO DISK='NUL'
). You will have lost the ability to restore past this point using Transaction Log backups.Additional information in response to comment: Yes - this was a is_snapshot = 1 [backup]
is_snapshot
Please read the section is_snapshot in my answer to the question Use of third-party VSS backup plus native SQL backup
From my original answer:
I hope this information is sufficient.