June 2, 2010 at 11:57 pm
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.
June 3, 2010 at 5:50 am
It amazes me that someone would expect you to restore backups with no alternative cataloging system to identify this information. Automated backups through the maintenance plan or a third party tool usually add date and time to the file names. Other methods should be documented (tapes labeled appropriately, etc.) and if nothing else, the create / modify date in the file directory should tell you when the backups where made.
But assuming a hurricane hit the workplace, all previous employees ran off to Tahiti, and you're the new guy working in a vacuum with these backups dumped in your lap... have you looked at table backupfile?
Also, it seems to me that physical device name may lend you a clue. There's definitely a pattern of naming. Lastly, SQL Server simply will not allow you to restore devices out of order. So if one Differential / Transaction Log won't restore, try the next one.
But the best way, honestly, is to have documented what backup device gets used when and make sure EVERYONE knows it. That way you don't have to guess.
June 3, 2010 at 6:32 am
You are absolutely right!! My only point was to identify/understand the LSN sequence looking at the system catalog! Is that possible or not. You have put in all the words which i have no more words to say. You explained me so well, that other person can understand what you mean.
But my basic reason for put that post was to understand the LSN sequence.
May be i haven't put in right words!!
Thanks 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply