jump to navigation

Backing up and restoring the tail-log end of a transaction log July 5, 2012

Posted by fofo in Sql Server, Sql Server 2005, SQL Server 2008.
Tags: , , , , ,
trackback

In this post I would like to demonstrate with a hands-on example how to backup and restore the tail of the transaction log.In this post I will also demonstrate how to take full and differential backups and how to restore them. I will also talk about the various SQL Server backup types, recovery models and how these models affect our backup and restore strategy.

But why we take backups in the first place? Disaster-Recovery is the first thing that comes to mind.We also use backups in Log Shipping,Database Mirroring,Replication. We do backups when we want to transfer our database from the development to the production server.Finally we take backups when moving or upgrading a database.

One could argue that we take backups when we want to manage the size of the transaction log.The only operation that clears the transaction log of a database that is in FULL or BULK logged recovery model is a transaction log backup. Some people are performing a backup before performing a database repair or an upgrade of any kind.

Before we proceed I would like to explain the various recovery models. In the Simple Recovery Model every time I have a checkpoint operation or the .ldf file is full, the .ldf file is automatically truncated.In this model the transaction log is not backed up.You can not have point in time data recovery with this model.Data is recoverable only to the most recent backup.

The Simple Recovery Model  is only suitable for small databases and databases with few transactions.We could use this recovery model if we want to have a small transaction log and for databases that are read-only or in development stage.

The Full Recovery Model is the default recovery model.In this model the transaction log grows rapidly since all database transactions are logged in at a very detailed level.If I have a catastrophe in my database I can rollback to the time where the catastrophe occurred.This recovery model is suitable for databases with thousands of transactions per day and where no loss of data is tolerated.

The Bulk Recovery Model works almost identically with the Full Recovery Model.It logs everything in extents=8pages and all bulk tasks (bcp,bulk insert) are logged minimally.We can go back to the time of failure but it will take more time to get the database back on-line.This model would be very suitable for databases with large bulk tasks.The disadvantage of this model is that you cannot go back at a specific point in time.

Having looked into the recovery models,let’s have a look on the various SQL Server backup types.

Let’s see what happens when we take a full database backup.The first thing to say is that you need always a full database backup.This is the base for all subsequent backups. You cannot have a differential backup,transaction log backup if you do not have at least one full backup.With a full database backup you backup all your data.You can have a full backup with all the recovery models that you choose to have for your database.Every full backup I perform in a database contains in it all the data previously fully backed up.
The backup operation is an online operation and the first thing it happens is to log that a backup was started. Then we write the data in the backup file.Then we do log any changes that occur as we backup.Finally the backing up of the data is finished and we log that fact.If you’re performing only full backups, you might lose some data in the event of a system crash—specifically, any changes made since the last full backup.

A differential backup is cumulative backup. It includes all data and structures that have changed since the last full backup, regardless of when that last full backup was made, or how many previous differential backups have been run.You can have a differential backup with all the recovery models.

The Transaction Log backup only works for full or bulk-logged recovery models.Each T-Log backup contains all the transactions that are in my database since the last T-Log backup.Always perform a full back up before you take a T-log backup.The time taken for a T-log backup is little and there is no performance penalty.An important thing to remember is that for every t-log back up I need the previous backup to have a restore.

I know a lot of people that are confused about the whole backup and restore process of a database. There are many issues that confuse people but the one I have found the most confusing for people is the use of Tail-log backup. Well, a tail-log backup is a transaction log backup that holds all the records that have not been backed up. So before we begin our restoration process,because we want to recover our SQL Server database to its latest point in time we must back up the tail of its transaction log.This will be the last back up.One thing that I want to immediately highlight is that you can not have a transaction log backup or tail-log backup unless the database is in either FULL or BULK-LOGGED recovery model.

How often you should back up a database depends on how long you have to restore it. In general, the more often you back up a database and the type of backup you take, the shorter the restore time.
You can tailor backups and restores for each database. The kind of backup you decide to use will depend on the size of the database and the amount of transactional activity.

Having only full backups is not a good strategy.If we have only one full database backup taken on 12 a.m every day for a database and our system crashes on 5 p.m, then we lose 5 hours of data.

If you can’t tolerate any data loss on restore, use the full plus log backup strategy.With this strategy we have a full database backup taken every day and then numerous T-Log backups at short periods of time. This method guarantees against data loss and works well for databases that are updated frequently. Although using this strategy increases your database’s complexity and maintenance, the total amount of time necessary to back up the database will decrease.So one might think that this is the appropriate restore strategy.At some point we will have thousands of files.It will take forever to restore thousands T-Log files.In order to have a good restore strategy for our average highly transactional database we need to include differential backups in our strategy. We could have for example 1 full backup every week, 7 differential backups every night and 336 (a week has 168 hours and I take 2 T-Log backups per hour) T-Log backups.

If you want to see a good backup (restore) strategy, have a look at this picture from sqlmag.com

Now we are ready to go on with our hands-on example.I will be using the SQL Server 2012 Express Edition

If you have an instance of SQL Server running in your machine,simply connect to it.

In an new query window type


USE master
GO

CREATE DATABASE sampledatabase
GO

ALTER DATABASE sampledatabase SET RECOVERY FULL

USE sampledatabase
GO

CREATE TABLE SampleData
(
id INT PRIMARY KEY ,
col1 tinyint NOT NULL ,
col2 BIT NOT NULL ,
col3 BIT NOT NULL ,
RegistrationDate DATETIME NOT NULL
)

GO

INSERT [dbo].[SampleData] ([id],[col1], [col2], [col3], [RegistrationDate])
VALUES (94495, 25, 1, 1, '02-03-2008')

INSERT [dbo].[SampleData] ([id],[col1], [col2], [col3], [RegistrationDate])
VALUES (94496, 26, 1, 1, '02-04-2008')

INSERT [dbo].[SampleData] ([id],[col1], [col2], [col3], [RegistrationDate])
VALUES (94497, 27, 1, 1, '02-05-2008')

BACKUP DATABASE sampledatabase TO DISK ='C:\sqldata\backups\sampledata_full.bak' WITH INIT,STATS;
GO

INSERT [dbo].[SampleData] ([id],[col1], [col2], [col3], [RegistrationDate])
VALUES (94498, 29, 1, 1, '02-03-2009')

INSERT [dbo].[SampleData] ([id],[col1], [col2], [col3], [RegistrationDate])
VALUES (94499, 28, 1, 1, '02-04-2009')

INSERT [dbo].[SampleData] ([id],[col1], [col2], [col3], [RegistrationDate])
VALUES (94500, 30, 1, 1, '02-05-2009')

SELECT * FROM sampledata

BACKUP DATABASE sampledatabase TO DISK = 'C:\sqldata\backups\sampledata_diff.bak' WITH DIFFERENTIAL
GO

INSERT [dbo].[SampleData] ([id],[col1], [col2], [col3], [RegistrationDate])
VALUES (94501, 29, 1, 1, '02-03-2010')

INSERT [dbo].[SampleData] ([id],[col1], [col2], [col3], [RegistrationDate])
VALUES (94502, 28, 1, 1, '02-04-2010')

INSERT [dbo].[SampleData] ([id],[col1], [col2], [col3], [RegistrationDate])
VALUES (94503, 30, 1, 1, '02-05-2010')

BACKUP LOG sampledatabase TO DISK ='C:\sqldata\backups\sampledata_Log1.bak' WITH INIT,STATS;
GO

INSERT [dbo].[SampleData] ([id],[col1], [col2], [col3], [RegistrationDate])
VALUES (94505, 29, 1, 1, '02-08-2010')

INSERT [dbo].[SampleData] ([id],[col1], [col2], [col3], [RegistrationDate])
VALUES (94506, 28, 1, 1, '02-09-2010')

INSERT [dbo].[SampleData] ([id],[col1], [col2], [col3], [RegistrationDate])
VALUES (94507, 30, 1, 1, '12-05-2010')

BACKUP LOG sampledatabase TO DISK ='C:\sqldata\backups\sampledata_Log2.bak' WITH INIT,STATS;
GO

INSERT [dbo].[SampleData] ([id],[col1], [col2], [col3], [RegistrationDate])
VALUES (94508, 29, 1, 1, '02-28-2011')

INSERT [dbo].[SampleData] ([id],[col1], [col2], [col3], [RegistrationDate])
VALUES (94510, 28, 1, 1, '02-27-2011')

INSERT [dbo].[SampleData] ([id],[col1], [col2], [col3], [RegistrationDate])
VALUES (94511, 30, 1, 1, '12-12-2011')

BACKUP LOG sampledatabase TO DISK ='C:\sqldata\backups\sampledata_Log3.bak' WITH INIT,STATS;
GO

INSERT [dbo].[SampleData] ([id],[col1], [col2], [col3], [RegistrationDate])
VALUES (100000, 30, 1, 1, '12-02-2012')

SELECT TOP 5*
FROM sampledata
ORDER BY id DESC

USE master
GO

ALTER DATABASE sampledatabase SET OFFLINE WITH NO_WAIT;

--simulate the disaster by deleting the .mdf file

ALTER DATABASE sampledatabase SET ONLINE

--this will result in a nasty error since there is no database file

BACKUP LOG sampledatabase TO DISK ='C:\sqldata\backups\sampledata_TailLog.bak' WITH INIT,NO_TRUNCATE;
GO

RESTORE DATABASE sampledatabase FROM DISK='C:\sqldata\backups\sampledata_full.bak' WITH NORECOVERY, REPLACE
RESTORE DATABASE sampledatabase FROM DISK='C:\sqldata\backups\sampledata_diff.bak' WITH NORECOVERY

RESTORE DATABASE sampledatabase FROM DISK='C:\sqldata\backups\sampledata_Log1.bak' WITH NORECOVERY

RESTORE DATABASE sampledatabase FROM DISK='C:\sqldata\backups\sampledata_Log2.bak' WITH NORECOVERY

RESTORE DATABASE sampledatabase FROM DISK='C:\sqldata\backups\sampledata_Log3.bak' WITH NORECOVERY

RESTORE DATABASE sampledatabase FROM DISK='C:\sqldata\backups\sampledata_TailLog.bak' WITH NORECOVERY

RESTORE DATABASE sampledatabase WITH RECOVERY

USE sampledatabase
GO

SELECT TOP 5*
FROM sampledata
ORDER BY id DESC

Let me explain what I am doing in the T-SQL script above.

1) In lines 1-21 I create a sample database,set the recovery model to FULL and create a sample table in it.

2) In lines 23-30 I insert 3 records in the table.

3) In lines 32-33 I take a full database backup.

4) In lines 35-42 I insert 3 more records in the table.

5) In lines 46-47 I take a differential backup.

6) In lines 49-56 I insert 3 more records in the table.

7) In lines 58-59 I take another transaction log backup.

8) In lines 61-68 I insert 3 more records in the table.

9) In lines 70-71 I take another transactional log backup.

10) In lines 73-80 I insert 3 more records in the table.

11) In lines 82-83 I take another transaction log backup.

12) In lines 85-86 I insert a new record in the table.

13) In lines 88-90 Ι perform a simple “Select” to see the newly inserted record.

14) In lines 92-95 I take the database offline.Then I go to the local path and delete the .mdf file.In my case the path is C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA. As you can understand I am simulating a disaster.

15) In line 99 I try to take the database online but I receive a nasty error as expected.

16) In lines 103-104 I take a tail-log backup.This is the first step in the restore process.

17) In lines 106-117 I restore with the correct sequence (full,differential,3 t-log backups,final tail-log backup). Everything works fine.

18) In lines 122-124, I am looking for the last record (id=100.000) that was inserted after the last t-log backup but was picked up from the tail-log backup. So despite the catastrophe I had, my database is back online and there is no data loss.

Please note that not everyone has the permission to backup a database.This permission is limited by default to members of the sysadmin fixed server role,members of the db_owner and db_backupoperator fixed database role.

Hope it helps!!!

Comments»

1. Dot Net Rules : Backing up and restoring the tail-log end of a transaction log - July 5, 2012

[…] Server backup types, recovery models and how these models affect our backup and restore strategy. (read more) Share Posted: Πέμπτη, 5 Ιουλίου 2012 1:17 μμ από το μέλος […]

2. Carl Davenport - July 5, 2012

After study a few of the blog poasts on your website now, and I truly like your way of blogging. I bookmarked it to my bookmark website list and will be checking back soon. Pls check out my web site as well and let me know what you think.

fofo - July 5, 2012

you can subscribe to my blog as well so as soon as I blog about something, you receive an e-mail…

3. Cesario - February 4, 2016

This is a great explanation that helped a lot understand the tail of the log. Thank you!

4. Deepak Kalra - February 23, 2016

Reblogged this on AX Technical World and commented:
Very good Explanation about SQL Server Backup types,Recovery Models and tail backup.can really help a person to understand backup and restore databases concepts.

5. Deepak Kalra - February 23, 2016

backup (restore) strategy picture is missing

6. Johnson Welch - April 6, 2016

Thank you for posting this. I recently was attempting to recover a database in sql server 2008 with no recovery, and I take few tips from here http://www.sqlserverlogexplorer.com/sql-server-norecovery-option/. But, Now after reading you blog i have no doubts.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: