Transaction log backup vs. Tail-log backup
-
Hello!
Can anybody to explain what's the odds between the first and the second? I really don't understand what Tail-log do(or don't) what Transaction log don't(or, vice versa, do)...
Thanks In Advance!
-
i've not heard of Tail log backups before - i think you may be referring to Tee-Log backups (or T-log) which stands for transaction log..... so they are the same thing
T-log backups copy the inactive portion of the transaction log to a backup file for retoring from at a later date. this inactive portion of the log is rmeoved and prevents the log from growing to a large size
MVDBA
-
Tail is a term used when trying to recovering a database to a point in time and it reffers to whatever is on the transaction log. I assume they call it tail because it will be your last Tlog backup before you start the recovery.
In summary
Backup the tail = backup transaction log.
Cheers,
* Noel -
OK, I see the problem.
So - I just tell where from my question came.I just read official course from MS - Course 2780A: Maintaining a Microsoft SQL Server 2005 Database. There are 2 quotations:
----quotation1----
Transaction log backup
Transaction log backups record any database changes. You typically back up transaction
logs when you perform full database backups. Note the following facts about
transaction log backups:
¦ You should not back up a transaction log unless you have performed a full
database backup at least once.
¦ You cannot restore transaction logs without a corresponding database backup.
¦ You cannot back up transaction logs when using the Simple Recovery model.
When you back up the transaction log, SQL Server does the following:
¦ Backs up the transaction log from the last successfully executed BACKUP LOG
statement to the end of the current transaction log
¦ Truncates the transaction log up to the beginning of the active portion of the
transaction log and discards the information in the inactive portion
The active portion of the transaction log starts at the point of the oldest open
transaction and continues to the end of the transaction log.
-------------------------------------------------------------------------
...and...
----quotation2----
Tail-log backup
A tail-log backup is a transaction log backup that includes the portion of the log that has
not previously been backed up (known as the active portion of the log). A tail-log backup
does not truncate the log and is generally used when the data files for a database have
become inaccessible but the log file is undamaged.
------------------------------------------------------------------------------------
I grasp these "novels" like this: log of any DB may be backuped in 2[different] manners: Transaction log backup(ordinary) OR Tail-log backup(special?). First of all - is IT correct? Or log may be backuped in one way only?
-
Not correct.
There is only one command and it is 'Backup Log....'
The Tail - log backup is just the last one you do before beginning a point in time recovery. ( It is also the last log restored ). I believe part of the key to this backup being the 'last' backup is that the database is down at this point. (i.e. there are not going to be anymore transactions taking place until after the recovery process is completed).
I envision a scenario where some component other than the device holding the log file fails. This requires a recovery up to the point in time of the failure. In this case the log file is still available, and you need to do one more backup of this (tail) of the log before you begin applying all the previos log backups in order.
-
A "tail log backup" refers to this situation:
The database has a failure and isn't usuable. You would like to be able to restore to the point in time just before the failure so you try to backup the transaction log. If the backup is successful, you will have captured transactions up to the point of failure and you can do a restore of the last full backup and apply all tlog backups including your "tail log" backup. If the backup is not successful, the best you can do is recover to the last good scheduled tlog backup and you lose the transactions that are in the "tail" of the transaction log.
Hope this helps
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
-
Almost none of the above is more correct than the official word from Micros~1:
http://msdn2.microsoft.com/en-us/library/ms179314.aspx
-
I give my thanks to all. With you help I completely understood the main idea behaind "magic" term "Tail-log".
Thanks once again.... -
The Tail-Log does not truncate the log when back up, that the difference.
-
Hi,
I'm in the process of documenting and testing all the recovery scenarios possible.
And I came across something I don't undertsand in regards to the backup of the tail log.
My test script can be seen below, but what I do is a full backup, do some transactions while they run perform two log backups, and when the transactions have finished a backup of the tail of the log. I expected to see that the tail log was restored, but instead I get this error:
Result when applying the Backup of the tail
Msg 4326, Level 16, State 1, Line 1
The log in this backup set terminates at LSN 78000000441300001, which is too early to apply to the database. A more recent log backup that includes LSN 81000000490400001 can be restored.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
This puzzles me a bit, it tels me that there exist a more recent log backup, any ideas ?
Script used:
-- ********** STEP 1 **********
USE [master]
GO
ALTER DATABASE [TestDB] SET RECOVERY FULL WITH NO_WAIT
GO
ALTER DATABASE [TestDB] SET RECOVERY FULL
GO
-- ********** STEP 1A **********
USE [TestDB]
GO
/****** Object: Table [dbo].[TblA] Script Date: 03/23/2009 16:04:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TblA]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[TblA](
[A] [nvarchar](50) NULL
) ON [PRIMARY]
END
GO
/****** Object: Table [dbo].[TblB] Script Date: 03/23/2009 16:04:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TblB]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[TblB](
[varchar](50) NULL
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[TblC] Script Date: 03/23/2009 16:04:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TblC]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[TblC](
[C] [varchar](50) NULL
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
-- ********** STEP 2 **********
Use TestDB
go
Truncate table dbo.TblA;
Truncate table dbo.TblB;
Truncate table dbo.TblC;
Select 'Before test results' as Description
select count(*) Result_TableA from dbo.TblA;
select count(*) Result_TableB from dbo.TblB;
select count(*) Result_TableC from dbo.TblC;
-- Due to the truncate expected result is 0 rows in all three tables
-- ********** STEP 3 **********
backup database TestDb to Disk = 'F:\Mssql\Backup\Testdb.bak' With INIT
-- ********** STEP 4 **********
-- go to a comand prompt and run the following script:
-- F:\Mssql\Scripts\Common\GenerateTransactions.cmd
-- While the scrips are running run two log backups as described
-- in step 4A and 4B
-- ********** STEP 4A **********
Backup Log TestDb to Disk = 'F:\Mssql\Backup\TestDb-Log-1.bak'
with init
-- Wait 2 minutes, and run the second transaction log backup
-- ********** STEP 4B **********
-- when issuing this command note the record numbers being inserted at the
-- time of the backup
Backup Log TestDb to Disk = 'F:\Mssql\Backup\TestDb-Log-2.bak'
with init
-- when the three windows that have popped up disapears continue in this script
-- ********** STEP 5 **********
Select 'Mid test results - transactions in the DB' as Description
select count(*) Result_TableA from dbo.TblA;
select count(*) Result_TableB from dbo.TblB;
select count(*) Result_TableC from dbo.TblC;
-- you should now see that you have 25.000 records in each table
-- ********** STEP 6 **********
-- perform backup of the tail of the transaction log.
use Master
Go
Backup Log TestDb to Disk = 'F:\Mssql\Backup\TestDb-Tail-Log.bak'
with NORECOVERY;
-- Now the DB is in recovery mode, and thus unaccessable
-- for users.
-- ********** STEP 7 **********
use Master
go
-- Restore full backup
Restore database testdb from disk = 'F:\Mssql\Backup\Testdb.bak' With NoRecovery;
-- try performing a refresh on the TestDB database and you will see
-- it has state restoring since it has not been recovered yet.
-- Restore first transaction log backup
Restore database testdb from disk = 'F:\Mssql\Backup\TestDb-log-1.bak' With NoRecovery;
-- Restore Second transaction log backup
Restore database testdb from disk = 'F:\Mssql\Backup\TestDb-log-2.bak' With NoRecovery;
-- Restore backup of the tail log
Restore database testdb from disk = 'F:\Mssql\Backup\TestDb-Tail-log.bak' With noRecovery;
-- Perform recovery
Restore database testdb With Recovery;
-- try doing another refresh, now you will see the database is back
-- online since we have noe recovered the database
-- ********** STEP 7 **********
Use TestDB
Go
Select 'After restore results - all transactions secured due to sucessful backup of the tail log ' as Description
select count(*) Result_TableA from dbo.TblA;
select count(*) Result_TableB from dbo.TblB;
select count(*) Result_TableC from dbo.TblC;
-- Expected result is 25.000 rows in each of the three tables
Best regards
Soren Udsen Nielsen
-
Hi,
Found the issue myself :-D, I had no With Init on the backup of the tail log, so the file contained several earlier tests, and the restore got completely confused.
With that fixed it all ran fine, and expected result is all data being recovered.
//SUN
-
So what command with the tail log part amended...
😀
-
Hi Tracey
Well like this:
Backup Log TestDb to Disk = 'F:\Mssql\Backup\TestDb-Tail-Log.bak'
with NORECOVERY, INIT;
//SUN
-
So what command with the tail log part amended...
😀
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply