Useful things about data recovery that every DBA should know. Part 4 — Restore planning

In the last part of this series we are going to find an answer to the ultimate question — what backup files should be used and in what order, to restore a database to their most current state? We will examine two cases:

1. When a database is being restored on the original SQL Server instance and a backup history is available.

2. When all you have is a set of strangle named backup files.

If you have missed previous articles, they are available here:

· Useful things about data recovery that every DBA should know. Part 1 — To what point in time full backup is being restored?

· Useful things about data recovery that every DBA should know. Part 2 — What does a full backup contain?

· Useful things about data recovery that every DBA should know. Part 3 — Three differential backups tricks.

What to do, if a backup history is available?

Let me prepare a sample database with a table:

CREATE DATABASE DB;
GO

USE DB;
GO
CREATE TABLE Tab
    (col1 int IDENTITY PRIMARY KEY,
    col2 time DEFAULT SYSDATETIME(),
    col3 char(4100) DEFAULT 'a');
GO

To simulate users’ activity, I will run in a new SSMS query window this query:

USE DB
GO
WHILE 1=1
BEGIN
INSERT INTO Tab DEFAULT VALUES;
WAITFOR DELAY '00:00:05';
END

Now we are ready to go.

While the query is running, I’m going to take a set of backup with random names — some of them are full backups, others differentials or log backups. To achieve this result, you need to execute the following statements couple of times, in random order:

DECLARE @name char(36)
SET @name=NEWID()
BACKUP DATABASE DB
TO DISK = @name
WITH INIT;
GO

DECLARE @name char(36)
SET @name=NEWID()
BACKUP DATABASE DB
TO DISK = @name
WITH INIT, DIFFERENTIAL;
GO

DECLARE @name char(36)
SET @name=NEWID()
BACKUP LOG DB
TO DISK = @name
WITH INIT;
GO

As a result a set of backup files will be created in a default backup directory. Now you can cancel the inserting query. Let’s suppose that our task is to restore this database without losing a single transaction.

First thing to do is to take a look at the available backups. You will find this information by executing the following query:

SELECT RIGHT(physical_device_name,11) AS FileName,type,first_lsn, last_lsn
FROM   msdb.dbo.backupset bs 
INNER JOIN msdb.dbo.backupmediafamily bmf 
    ON bs.media_set_id = bmf.media_set_id
WHERE database_name = 'DB'
ORDER BY physical_device_name
GO

Take a closer look at the result, especially at a backup type and the first and last LSN stored in this backup:

1. Type L means a log backup, D stands for a full database backup, and I represents a differential backup.

2. Try to find a backup log chains by correlating a backup type with the first and lost log records stored in it.

FileName       type first_lsn                    last_lsn

8252AFCFFD6 L 35000000038900001 35000000040300001

857DBA4A8AB D 35000000012400207 35000000021000001

878230805C0 L 35000000040300001 35000000046000001

68F35BE8159 D 35000000033800043 35000000035700001

F97D9845E00 I 35000000026700039 35000000028500001

26CE3CA10F1 L 35000000030900001 35000000038900001

15C44827368 I 35000000043000039 35000000044800001

FDE1F993303 L 35000000012400207 35000000030900001

As we know, the restore sequence has to start with a full backup. The following query returns all full backups of this database:

SELECT RIGHT(physical_device_name,11) AS FileName,type,first_lsn, last_lsn
FROM   msdb.dbo.backupset bs 
INNER JOIN msdb.dbo.backupmediafamily bmf 
    ON bs.media_set_id = bmf.media_set_id
WHERE database_name = 'DB'
AND type='D';
-----
FileName       type    first_lsn           last_lsn
857DBA4A8AB    D    35000000012400207    35000000021000001
68F35BE8159    D    35000000033800043    35000000035700001

In my case, there are two fill backups: backup 857DBA4A8AB and 68F35BE8159. Based on first (or last) LSNs we can say that backup 68F35BE8159 was taken earlier then backup 68F35BE8159. So the backup 68F35BE8159 is the last full backup — the one from the restore sequence should be started.

Next task is to find all differential backups taken after this full backup:

SELECT RIGHT(physical_device_name,11) AS FileName, type,first_lsn, last_lsn
FROM   msdb.dbo.backupset bs 
INNER JOIN msdb.dbo.backupmediafamily bmf 
    ON bs.media_set_id = bmf.media_set_id
WHERE database_name = 'DB'
AND type='I'
AND first_lsn >=35000000035700001;
-----
FileName        type    first_lsn        last_lsn    
15C44827368    I    35000000043000039    35000000044800001

In my case, there is only one differential backup, but if there were more, only the last would be necessary.

After finding the last full and differential backups, the next step is to find all log backups and order them appropriately:

SELECT RIGHT(physical_device_name,11) AS FileName, type,begins_log_chain,first_lsn, last_lsn
FROM   msdb.dbo.backupset bs 
INNER JOIN msdb.dbo.backupmediafamily bmf 
    ON bs.media_set_id = bmf.media_set_id
WHERE database_name = 'DB'
AND type='L'
ORDER BY first_lsn;

By quick comparison the last LSN stored in the previous log backup with the first LSN stored in the next one, you can easily see a log backup chain, can’t you?

FileName       type begins_log_chain first_lsn                    last_lsn

FDE1F993303 L      1                     35000000012400207 35000000030900001

26CE3CA10F1 L     0                     35000000030900001 35000000038900001

8252AFCFFD6 L     0                     35000000038900001 35000000040300001

878230805C0 L       0                   35000000040300001 35000000046000001

What’s more, based on those LSN numbers, one can say that there was only one log backup taken after the last differential backup, so only this one need to be restored:

SELECT RIGHT(physical_device_name,11) AS FileName, type,begins_log_chain,first_lsn, last_lsn
FROM   msdb.dbo.backupset bs 
INNER JOIN msdb.dbo.backupmediafamily bmf 
    ON bs.media_set_id = bmf.media_set_id
WHERE database_name = 'DB'
AND type='L'
AND last_lsn>35000000044800001;
-----
FileName    type    begins_log_chain    first_lsn    last_lsn
878230805C0    L    0            35000000040300001    35000000046000001

Wait a moment. It seems that you are able to restore this database to the lsn 35000000046000001 only.

Let me check, if there are more recent data stored in our sample database. To do this, you should check the maximum LSN number stored in a transaction log. Fortunately, fn_dblog system function allows us to do so:

SELECT MAX([Current LSN])
FROM fn_dblog (NULL,NULL);

Unfortunately, this function returns log numbers in a different format: the first part is a VLF umber in hex, then is a 10-bytes log bloc number (also in hex), and finally the log record number (in hex format as well).

The conversion to format used in msdb.dbo.backupset table is not so difficult. I am going to use for this a code published by SQLSoldier on his blog, to create an appropriate scalar function:

CREATE FUNCTION udfConvertLSN (@LSN char(22))
RETURNS char(22)
AS BEGIN
DECLARE 
    @LSN1 varchar(11),
    @LSN2 varchar(10),
    @LSN3 varchar(5)

SET @LSN1 = LEFT(@LSN, 8);
SET @LSN2 = SUBSTRING(@LSN, 10, 8);
SET @LSN3 = RIGHT(@LSN, 4);

-- Convert to binary style 1 -> int
SET @LSN1 = CAST(CONVERT(VARBINARY, '0x' +
        RIGHT(REPLICATE('0', 8) + @LSN1, 8), 1) AS int);

SET @LSN2 = CAST(CONVERT(VARBINARY, '0x' +
        RIGHT(REPLICATE('0', 8) + @LSN2, 8), 1) AS int);

SET @LSN3 = CAST(CONVERT(VARBINARY, '0x' +
        RIGHT(REPLICATE('0', 8) + @LSN3, 8), 1) AS int);

-- Add padded 0''s to 2nd and 3rd string
RETURN CAST(@LSN1 as varchar(8)) +
    CAST(RIGHT(REPLICATE('0', 10) + @LSN2, 10) AS varchar(10)) +
    CAST(RIGHT(REPLICATE('0', 5) + @LSN3, 5) AS varchar(5));
END
GO

Now we are ready to check the current LSN:

SELECT MAX(dbo.udfConvertLSN([Current LSN]))
FROM fn_dblog (NULL,NULL);
------
35000000047400020

This number is clearly higher than the last LSN stored in a last differential backup (35000000044800001). Thus, before start a restore sequence you should take a log-tail backup:

DECLARE @name char(36)
SET @name=NEWID()
BACKUP LOG DB
TO DISK = @name
WITH INIT, NO_TRUNCATE, NORECOVERY;
GO

Note – NORECOVERY is used to assure that no transaction can be added after this backup was taken, and the NO_TRUNCATE option allows you to take a log backup of a damaged database, so in real life you probably will never use both this options together.

Now, if you check an available log chain backup, you will find that you will be able to restore this database without losing a single transaction:

SELECT RIGHT(physical_device_name,11) AS FileName, type,begins_log_chain,first_lsn, last_lsn
FROM   msdb.dbo.backupset bs 
INNER JOIN msdb.dbo.backupmediafamily bmf 
    ON bs.media_set_id = bmf.media_set_id
WHERE database_name = 'DB'
AND type='L'
ORDER BY first_lsn;
-----
FileName    type    begins_log_chain    first_lsn    last_lsn
FDE1F993303    L    1            35000000012400207    35000000030900001
26CE3CA10F1    L    0            35000000030900001    35000000038900001
8252AFCFFD6    L    0            35000000038900001    35000000040300001
878230805C0    L    0            35000000040300001    35000000046000001
C486B5CCB27    L    0            35000000046000001    35000000048000001

The final restore sequence looks like this:

1. Restore the 68F35BE8159is full backup with no recovery

2. Then restore the 15C44827368 differential backup with no recovery

3. Then restore the 878230805C0 log backup with no recovery

4. Finally, restore the C486B5CCB27 transaction log backup with recovery

And if you check what SSMS will do, you will find exactly the same backups are going to be restored:

image

What if a set of backup files have to be restored on a different SQL Server?

Restoring a set of backup files on a different SQL Server looks almost exactly the same, with one important exception — all information needed has to be collected from backup files, not a msdb database.

The first and last LSNs can be retrieved by executing RESTORE HEADERONLY statement:

RESTORE HEADERONLY
FROM DISK = '4F64BFCC-0F31-4552-8971-168F35BE8159';

Unfortunately, this format is not very actionable — it would be a whole lot easier if those information was stored in a table. Let me create a table (this one is for SQL Server 2012):

CREATE TABLE #Backup_Information(
 BackupName nvarchar(128),
 Backupdescription varchar(255),
 backuptype int,
 expirationdate datetime,
 compressed int,
 position int,
 DeviceType int, 
 UserName varchar(128),
 ServerName varchar(128),
 DatabaseName varchar(128),
 DatabaseVersion int,
 DatabaseCreationDate datetime,
 BackupSize int,
 FirstLsn varchar(100),
 LastLsn varchar(100),
 CheckpointLsn varchar(100),
 DatabaseBackupLSN numeric(25,0),
 BackupStartDate datetime,
 BackupFinishDate datetime,
 SortOrder int,
 [CodePage] int,
 UnicodeLocaleId int,
 UnicodeComparisonStyle int,
 CompatibilityLevel int,
 SoftwareVendorId int,
 SoftwareVersionMajor int,
 SoftwareVersionMinor int,
 SoftwareVersionBuild int,
 MachineName varchar(128),
 Flags varchar(200),
 BindingId varchar(200),
 RecoveryForkId varchar(200),
 Collation varchar(200),
 FamilyGUID uniqueidentifier,
 HasBulkLoggedData bit,
 IsSnapshot bit,
 IsReadOnly bit,
 IsSingleUser bit,
 HasBackupChecksums bit,
 IsDamaged bit,
 BeginsLogChain bit,
 HasIncompleteMetaData bit,
 IsForceOffline bit,
 IsCopyOnly bit,
 FirstRecoveryForkID uniqueidentifier,
 ForkPointLSN numeric(25,0),
 RecoveryModel nvarchar(60),
 DifferentialBaseLSN numeric(25,0),
 DifferentialBaseGUID uniqueidentifier,
 BackupTypeDescription nvarchar(60),
 BackupSetGUID uniqueidentifier,
 CompressedBackupSize bigint,
 containment tinyint);
GO

And then insert into this table necessary information:

INSERT INTO #Backup_Information
EXEC('RESTORE HEADERONLY
    FROM DISK = ''4F64BFCC-0F31-4552-8971-168F35BE8159''');
GO

INSERT INTO #Backup_Information
EXEC('RESTORE HEADERONLY
    FROM DISK = ''C2A09D49-9CE1-466D-BDC4-A15C44827368''');
GO

INSERT INTO #Backup_Information
EXEC('RESTORE HEADERONLY
    FROM DISK = ''3AB9FECC-991B-4CB8-AE4C-7878230805C0''');
GO

INSERT INTO #Backup_Information
EXEC('RESTORE HEADERONLY
    FROM DISK = ''68EDACB1-F747-45ED-8449-0C486B5CCB27''');
GO

Now, we get almost the same data as in the first scenario. Only one important piece is missing — the backup file name:

SELECT BackupTypeDescription, FirstLsn,LastLsn
FROM #Backup_Information;
----
BackupTypeDescription    FirstLsn            LastLsn
Database               35000000033800043    35000000035700001
Database Differential    35000000043000039    35000000044800001
Transaction Log        35000000040300001    35000000046000001
Transaction Log        35000000046000001    35000000048000001

BTW. Backup name can be obtained by executing RESTORE FILELISTONLY statement, this one also returns backup type and create LSN.

Hope this help

Share this article ...

Google Plus
Ihren LinkedIn Kontakten zeigen



This entry was posted in Administration, Data Recovery, SQL Server and tagged , , by Marcin Szeliga. Bookmark the permalink.
Marcin Szeliga

About Marcin Szeliga

Since 2006 invariably awarded Microsoft Most Valuable Professional title in the SQL category. A consultant, lecturer, authorized Microsoft trainer with 15 years’ experience, and a database systems architect. He prepared Microsoft partners for the upgrade to SQL Server 2008 and 2012 versions within the Train to Trainers program. A speaker at numerous conferences, including Microsoft Technology Summit, SQL Saturday, SQL Day, Microsoft Security Summit, Heroes Happen {Here}, as well as at user groups meetings. The author of many books and articles devoted to SQL Server.

Comments are closed.