log sequence - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

log sequence

Author

Topic

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2010-06-03 : 01:58:48

Hi,

I was trying to analyze LSN sequence, and i want to know the order to restore the files.
Assuming, that i dont have access to the source database server where the database exists
and backup history is available. only i have the output from the source server.

Example, i have a database "db1"

use master
go
create database db1
go
use db1
go
create table t1
(id int,
name varchar(10)
)
go
insert into t1
select 1,'A'
go
--full bkp
backup database db1
to disk = 'c:\db1_full01.bak'

insert into t1
select 2,'B'

--log backup 1
backup log db1
to disk = 'c:\db1_log01.trn'

insert into t1
select 3,'C'

--log backup 2
backup log db1
to disk = 'c:\db1_log02.trn'

insert into t1
select 4,'D'

--log backup 3
backup log db1
to disk = 'c:\db1_log03.trn'

-- differential bkp
backup database db1
to disk = 'c:\db1_diff01.bak'
with differential;

insert into t1
select 5,'E'

--log backup 4
backup log db1
to disk = 'c:\db1_log04.trn'

-- differential bkp
backup database db1
to disk = 'c:\db1_diff02.bak'
with differential;

insert into t1
select 6,'F'

--log backup 5
backup log db1
to disk = 'c:\db1_log05.trn'

SELECT a.database_name,
a.type,
CASE a.type WHEN 'D' THEN 'FULL BACKUP'
WHEN 'I' THEN 'Differential Backup'
WHEN 'L' THEN 'Transaction Log bkp'
WHEN 'F' THEN 'File or Filegroup bkp'
WHEN 'G' then 'Differential File bkp'
WHEN 'P' THEN 'Partial bkp'
WHEN 'Q' THEN 'Differential partial' END as backupType,
m.physical_device_name,
a.backup_size/1024/1024/1024 AS backupSizeGB,
a.backup_size/1024/1024 AS backupSizeMB,
DATEDIFF(mi, a.backup_start_date, backup_finish_date) AS durationInMinutes,
a.backup_finish_date as last_backup,
a.first_lsn,
a.last_lsn,
a.checkpoint_lsn,
a.database_backup_lsn
FROM msdb.dbo.backupset a
inner join msdb.dbo.backupmediafamily m
ON a.media_set_id = m.media_set_id
WHERE a.database_name = 'db1' --------------change the database name
and a.type IN('D','L','I')
order by a.backup_finish_date

please find the attached screen shot for Log sequence nos.

Looking at the output i can easily decide which one to restore first and which one next.

Example, the database has crashed and looking at the output of the above and all with the available
backups i can easily identify which order of backups to be restored.
in the scenario, First ,
1. Restore Full bkp
2. Restore Differential bkp 2
3. log bkp 05

But assume, i dont have the above information and i was given only the backups to be restored and
am left with only the backups then how can i identify the order of restoration.Am using, RESTORE HEADERONLY FROM DISK = '<<BKP PATH>>' TO know the log sequence.

restore headeronly from disk = 'c:\db1_full01.bak'
restore headeronly from disk = 'c:\db1_log01.trn'
restore headeronly from disk = 'c:\db1_log02.trn'
restore headeronly from disk = 'c:\db1_log03.trn'
restore headeronly from disk = 'c:\db1_diff01.bak'
restore headeronly from disk = 'c:\db1_log04.trn'
restore headeronly from disk = 'c:\db1_diff02.bak'
restore headeronly from disk = 'c:\db1_log05.trn'

But am unable to trace out the ORDER of the backups taken. How can i identify that. As per my knowledge, i know differential goes back to the previous FULL bkp (cummulative), and log bkp goes back to previous log bkp.

Normally, the differential backup has to take the last lsn or first lsn of the full bkp but i can see a new LSN number being generated for each differential.

Then how can i identify the ORDER. am confused!!!

Can anybody help me out to understand the LOG SEQUENCE and how to identify the order if have given only
backups and can say what all the VALID backups and what all INVALID backups.

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2010-06-03 : 02:08:52

Forgot to attach log seq's

physical_device_name first_lsn last_lsn
c:\db1_full01.bak 23000000006400174 23000000013500001
c:\db1_log01.trn 23000000006400174 23000000014400001
c:\db1_log02.trn 23000000014400001 23000000014500001
c:\db1_log03.trn 23000000014500001 23000000014600001
c:\db1_diff01.bak 23000000014600038 23000000016300001
c:\db1_log04.trn 23000000014600001 23000000016400001
c:\db1_diff02.bak 23000000016400005 23000000016700001
c:\db1_log05.trn 23000000016400001 23000000016800001

Kristen
Test

22859 Posts

Posted - 2010-06-03 : 03:40:38

Not really the answer to your question, but we name our backup files including Date and Time in the filename, and then just restore them in order using the filename.

If we restore a TLog file that is too early SQL ignores it, so it does no harm (other than time)

If we miss one then SQL complains

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2010-06-03 : 04:43:18

Hey Kristen,

Thanks for the suggestion.

But we identify looking at the log sequence numbers ???

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources