Understanding SQL LSN &
Backups-Restore (Part-2)
Why?
LSN (LOG SEQUENCE NUMBER) is
very simple sequence that exists in SQL database which helps to identify &
maintain the logs of any database. This is most easily misunderstood while
relating this with backup types & their conditions. So let’s see how this
LSN plays a major role in backup & restore of any database from backup
files. As this is a continuation from last post, I would request you to view my
last post , which will help you to understand those backup files I refer below.
How?
We
will create a new database Restore_Explanation from those backups we already
have for Backup_Explanation database.
Scenario1: Importance
of Transactional Logbackup LSN.
NOTE:
I
had used “MOVE… TO…” option while
restoring to move the data files to different location. It may throw an error
if I haven’t mentioned it while restoring “Restore_Explanation” DB from backups
of “Backup_Explanation” ,though the logical names of the files are different
the Physical Names of these files will be same as “ Backup_Explanation.mdf “
& “Backup_Explanation_log.ldf”. So, SQL will not allow you to restore our
new database successfully.
Also
you should restore all backups with “NO RECOVERY” option except last Transactional
Log backup to keep your database in recovery. Once all these are restored we
will restore last needy Transactional log backup with recovery to get the
database up to that point.
Let’s begin,
When
we try to restore a backup from backup_Explanation_Full.bak &
Backup_Explantion_log2.trn by leaving log1.trn, It throws an error message
stating mismatch of LSN in log backups which you are trying to restore.
The log in this backup set
begins at LSN 58000000007200001, which is too recent to apply to the database.
An earlier log backup that includes LSN 58000000003200001 can be restored.
The error message in red
clearly shows that SQL server is unable to restore log2 on top of full backup
we have. It will suggest you to go for earlier log backup. This is because the
First_lsn & Last_lsn of transactional log backup is not met as expected.
I will uncomment my
query before restore command of backup_Explanation_log1.trn & I will rerun
it. You may see the restore goes fine & a new database called
Restore_Explanation got created in my SQL Instance with all the required 3
tables on it.
Thus
we need to ensure that we restore correct Log backups in Sequence of their LSN
to achieve successful point in time restore of any database.
Scenario2: Importance of Differential Backup :
Now I wish to restore the same database
Restore_Explanation till ‘Table 8’
from the backups of Backup_Explanation database. To do so, I may follow 2 type
of restores like
Note:
I had used option “REPLACE” in this scenarioto restore
Replace_Explanation database from backups of Backup_Explanation, as I am going
to overwrite an existing database with same logical names & physical names
for files & keep them on same location where it is currently located.
Type1: Restoring First Full backup & followed by all
transactional log backups till log5 whose database_backup_LSN is equal to Checkpoint_LSN of the Full
backup. Thus to accomplish this task we may need following backups to be
restored.
|
Full1
|
|
Log1
|
|
Log2
|
|
Log3
|
|
Log4
|
|
Log5
|
Type2: Restoring First Full backup, followed
by Diff2 Differential backup with NO Recovery option, followed by log5 with
recovery option. As Differential backups are equal to cumulative backups of all
Log backups before Checkpoint_LSN of that particular Differential backup since
Last Full backup made. So we need the following files to complete our restore.
|
Full1
|
|
Diff2
|
|
Log5
|
On analysing Type 1 option will become tedious & tough if we have more
number of Log backups to be restored. Also we should keep track of
Transactional log backup LSNs also. But on Type2
We just want to restore only 3 files& it will be easier for our future
analysis & it is easy for remembering & to follow as well. SO I prefer
to go with Type 2 restore for this scenario.
On the message you could see the
restore got completed & there are 8 tables now presented on
Restore_Explanation database.
NOTE:
Notice that first highlight shows the full backup got restored, which will restore both MDF & LDF
files & leave the database in no recovery. The second highlighted portion
shows the differential backup got
restored, which again will add cumulative pages since last full backup for the
database on its respective MDF & LDF files & will leave the database in
no recovery as expected.
Finally
Notice on restoring log will restore its updates to the database LDF but not to MDF.
This difference is because the checkpoint would have run only during FULL
Backups & Differential Backups. Thus while restoring any FULL or
Differential backups the Restore command will check for the CHECKPOINT_LSN
created for that backup file.
Scenario 3: Restoring a Copy-only Full backup:
Restore of a database from a copy
only backup is very similar to restore a database from a full backup. The
advantage it has is that all those differential backups & log backups
happen after this copy only full backup will also have their
database_backup_LSN equal to their last orginal Full backup’s checkpoint_LSN.
Thus copy_only backup helps with a full backup which doesn’t reset the
database_Backup_LSN of following differential & Tlog backups.
Scenario 4 : A Missing FUL Backup
– Help of LSNs:
Now let’s play with a Missing
backup situation, which would have been a surprise to you J . Now If I need to restore the
copy of Backup_Explanation database till table 15 to Restore_Explanation
datbase, the easiest option will be like to restore full backup
(Backup_Explanation_FULL2.bak) & to restore backup_explanation_log9.trn on
top of it.
But due to some issue I had
deleted my full backup Backup_Explanation_FULL2.bak unknowingly . & we need
to recovery our database !!!
I
am going to retrieve my database from the last copy_only full backup as it has
most of the data I need, followed by all transactional log backups. Skipping
the full backup in between them.
|
Type
|
first_lsn
|
Last_lsn
|
checkpoint_lsn
|
database_backup_lsn
|
|
Copy_only_FULL
|
59000000001600000
|
59000000005600000
|
59000000001600000
|
57000000042400100
|
|
Log7
|
58000000045600000
|
59000000006400000
|
59000000001600000
|
57000000042400100
|
|
Log8
|
59000000006400000
|
59000000008800000
|
59000000001600000
|
57000000042400100
|
|
Full2
|
59000000012000100
|
59000000020000000
|
59000000012000100
|
57000000042400100
|
|
Log9
|
59000000008800000
|
59000000023200000
|
59000000012000100
|
59000000012000100
|
|
Log10
|
59000000023200000
|
59000000024800000
|
59000000012000100
|
59000000012000100
|
|
Log11
|
59000000024800000
|
59000000033600000
|
59000000025600100
|
59000000012000100
|
With help of following query we will
restore the database backups as mentioned.
The result of the query will be,
Thus we can
see all 15 tables got restored to Restore_Explanation database. Even though we don’t have a last successful
full backup or differential backup we still able to restore a database to point
in time only with help a full backup or Copy_only full backup before LAST FULL BACKUP (FULL2) & followed
by all Transactional Log backups.
This was possible only because the FULL backup
(FULL2) will only change the Database_Backup_LSN of following transactional log
backups, which will help only to refer a recent full backup that will help easy
restore of those following logs.
In our case ( Log9, Log10, Log 11)
Database_backup_LSNs [59000000012000100] will be the checkpoint_LSN of
Full2 full backup[59000000012000100].
But this full backup will not break the
Transactional Log LSN by any chance. Which means Last_lsn of log8 [59000000008800000] will be equal to First_lsn of log9 [59000000008800000], though they have a full backup (FULL2) between them & their
respective Database_backup_LSNs may differJ J.
Over all
scenarios:
I had attached whole script I had used for explaining scenarios fyr.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
USE [master]
--Senario1--
RESTORE DATABASE [Restore_Explanation] FROM DISK = N'D:\Backups\Backup_Explantion_Full.bak' WITH FILE = 1,
MOVE N'Backup_Explanation' TO N'c:\MSSQL2012\SQL_TFS\MSSQL11.SQL_TFS\MSSQL\DATA\RESTORE_EXPLANATION\Backup_Explanation.mdf',
MOVE N'Backup_Explanation_log' TO N'c:\MSSQL2012\SQL_TFS\MSSQL11.SQL_TFS\MSSQL\DATA\RESTORE_EXPLANATION\Backup_Explanation_log.ldf',
NORECOVERY, NOUNLOAD, STATS = 5
RESTORE LOG [Restore_Explanation] FROM DISK = N'D:\Backups\Backup_Explanation_Log1.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5 -- comment & uncomment
RESTORE LOG [Restore_Explanation] FROM DISK = N'D:\Backups\Backup_Explanation_Log2.trn' WITH FILE = 1, NOUNLOAD, STATS = 5
GO
--Senario2--
--Type1--
USE [master]
RESTORE DATABASE [Restore_Explanation] FROM DISK = N'D:\Backups\Backup_Explantion_Full.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 5
RESTORE LOG [Restore_Explanation] FROM DISK = N'D:\Backups\Backup_Explanation_Log1.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
RESTORE LOG [Restore_Explanation] FROM DISK = N'D:\Backups\Backup_Explanation_Log2.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
RESTORE LOG [Restore_Explanation] FROM DISK = N'D:\Backups\Backup_Explanation_Log3.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
RESTORE LOG [Restore_Explanation] FROM DISK = N'D:\Backups\Backup_Explanation_Log4.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
RESTORE LOG [Restore_Explanation] FROM DISK = N'D:\Backups\Backup_Explanation_Log5.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
RESTORE LOG [Restore_Explanation] FROM DISK = N'D:\Backups\Backup_Explanation_Log6.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
RESTORE LOG [Restore_Explanation] FROM DISK = N'D:\Backups\Backup_Explanation_Log7.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
RESTORE LOG [Restore_Explanation] FROM DISK = N'D:\Backups\Backup_Explanation_Log8.trn' WITH FILE = 1, NOUNLOAD, STATS = 5
GO
--Senario2--
--Type2--
USE [master]
RESTORE DATABASE [Restore_Explanation] FROM DISK = N'D:\Backups\Backup_Explantion_Full.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 5
RESTORE DATABASE [Restore_Explanation] FROM DISK = N'D:\Backups\Backup_Explantion_diff2.diff' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
RESTORE LOG [Restore_Explanation] FROM DISK = N'D:\Backups\Backup_Explanation_Log5.trn' WITH FILE = 1, NOUNLOAD, STATS = 5
GO
--Scenario3 --
RESTORE DATABASE [Backup_Explanation] FROM DISK = N'D:\Backups\Backup_Explantion_copy_only.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 5
--Scenario4 --
USE [master]
RESTORE DATABASE [Backup_Explanation] FROM DISK = N'D:\Backups\Backup_Explantion_copy_only.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 5
RESTORE LOG [Backup_Explanation] FROM DISK = N'D:\Backups\Backup_Explanation_Log7.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
RESTORE LOG [Backup_Explanation] FROM DISK = N'D:\Backups\Backup_Explanation_Log8.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
RESTORE LOG [Backup_Explanation] FROM DISK = N'D:\Backups\Backup_Explanation_Log9.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
RESTORE LOG [Backup_Explanation] FROM DISK = N'D:\Backups\Backup_Explanation_Log10.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
RESTORE LOG [Backup_Explanation] FROM DISK = N'D:\Backups\Backup_Explanation_Log11.trn' WITH FILE = 1, NOUNLOAD, STATS = 5
GO
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
VSG
SQL DBA
