sqlschool.gr logo

articles

Articles of SQLschool.gr Team

Restore survivor - Restore chain explanation and restore script generation

Antonios Chatzipavlis
Friday 03 September 2021

Overview

Μια από τις γνωστότερες παροιμίες που έχουμε, λέει "των φρονίμων τα παιδιά πριν πεινάσουν μαγειρεύουν". Η ερμηνεία της παροιμίας αυτή είναι ξεκάθαρη, και αφορά το ότι πρέπει να προετοιμαζόμαστε νωρίτερα για αυτά που θα έρθουν. Ταιριάζει απόλυτα με αυτό που πρέπει ένας DBA να κάνει.

Αν θελήσουμε να ταξινομήσουμε το ποιος είναι ο χειρότερος εφιάλτης ενός DBA σίγουρα ο πρώτος, και με διαφορά θα έλεγα, είναι η απώλεια των δεδομένων. Για αυτό ο DBA έχει φροντίσει να έχει πολλαπλά backups. Αυτό όμως από μόνο του δεν φτάνει, καθώς όπως έχω πει πολλές φορές, το να έχεις backup δεν σε εξασφαλίζει αν δεν έχεις επιβεβαιώσει ότι αυτό μπορεί να γίνει restore.

Πέρα όμως από αυτό πρέπει να είναι προετοιμασμένος και προπονημένος για τις περιπτώσεις αυτές ώστε αν συμβούν να μπορεί με ελάχιστες κινήσεις να ξεκινήσει τις διαδικασίες αποκατάστασης.

Με αυτό το άρθρο θέλω να βοηθήσω των DBA (όπως φυσικά και τον εαυτό μου) ώστε να αντιδράσει γρήγορα σε μια τέτοια κατάσταση αλλά και μέσα από αυτό να μάθουμε να διαβάζουμε σωστά το τι διαθέσιμα backups έχουμε στα χέρια μας.

Τέλος για προλάβω κάποιους που θα πουν ότι αυτό μπορούν να το κάνουν μέσα από τον SSMS να τονίσω ότι ναι μπορεί να γίνει αν η βάση είναι διαθέσιμη σε κάθε άλλη περίπτωση αυτό δεν μπορεί να γίνει. Αλλά ακόμα και έτσι έχει αξία το άρθρο αυτό καθώς θα γίνεται κατανοητό το πως επιλέγονται αυτά που προτείνει ο SSMS.

Sample Database and Backup Script

create database bin
go
use bin
create table t (col1 int)
 
backup database bin to disk='R:\MSSQL15.MSSQLSERVER\MSSQL\Backup\bin.bak'
 
insert into t values(1)
backup log bin to disk='R:\MSSQL15.MSSQLSERVER\MSSQL\Backup\bin.bak'
insert into t values(2)
backup log bin to disk='R:\MSSQL15.MSSQLSERVER\MSSQL\Backup\bin.bak'
insert into t values(3)
backup log bin to disk='R:\MSSQL15.MSSQLSERVER\MSSQL\Backup\bin.bak'
 
insert into t values(4)
backup database bin to disk='R:\MSSQL15.MSSQLSERVER\MSSQL\Backup\bin.bak' with differential
 
insert into t values(5)
backup log bin to disk='R:\MSSQL15.MSSQLSERVER\MSSQL\Backup\bin.bak'
insert into t values(6)
backup log bin to disk='R:\MSSQL15.MSSQLSERVER\MSSQL\Backup\bin.bak'
insert into t values(7)
backup log bin to disk='R:\MSSQL15.MSSQLSERVER\MSSQL\Backup\bin.bak'
 
insert into t values(8)
backup database bin to disk='R:\MSSQL15.MSSQLSERVER\MSSQL\Backup\bin.bak' with differential
 
insert into t values(9)
backup log bin to disk='R:\MSSQL15.MSSQLSERVER\MSSQL\Backup\bin.bak'
insert into t values(10)
backup log bin to disk='R:\MSSQL15.MSSQLSERVER\MSSQL\Backup\bin.bak'
insert into t values(11)
backup log bin to disk='R:\MSSQL15.MSSQLSERVER\MSSQL\Backup\bin.bak'

Explaining the backup internals

Όπως είναι γνωστό κάθε φορά που εκτελείται κάποιο backup αυτό καταγράφεται στην msdb database στο dbo.backupset table. Μπορούμε εύκολα με το παρακάτω query να δούμε το αποτέλεσμα του. Επιστρέφει πολλά fields που μπορείτε να διαβάσετε αναλυτικά για αυτά από το documentation.

Όπως θα δείτε στο παρακάτω script έχει επιλέξει να επιστρέφονται συγκεκριμένα καθώς αυτά είναι που με ενδιαφέρουν στο συγκεκριμένο άρθρο.

Τα fields αυτά σύμφωνα πάντα με το documentation περιέχουν τις εξής πληροφορίες που αντιγράφω ακριβώς από το documentation.

  • backup_finish_date - datetime - Date and time the backup operation finished. Can be NULL.
  • type (char(1)) - Backup type. Can be: D = Database, I = Differential database, L = Log, F = File or filegroup, G =Differential file, P = Partial, Q = Differential partial. Can be NULL.
  • first_lsn (numeric(25,0)) - Log sequence number of the first or oldest log record in the backup set. Can be NULL.
  • last_lsn (numeric(25,0)) - Log sequence number of the next log record after the backup set. Can be NULL.
  • checkpoint_lsn - numeric(25,0) -Log sequence number of the log record where redo must start. Can be NULL.
  • database_backup_lsn - numeric(25,0) - Log sequence number of the most recent full database backup. Can be NULL. database_backup_lsn is the "begin of checkpoint" that is triggered when the backup starts. This LSN will coincide with first_lsn if the backup is taken when the database is idle and no replication is configured.

Query backup history

select backup_finish_date,[type],first_lsn,last_lsn,checkpoint_lsn,database_backup_lsn
from msdb.dbo.backupset
where database_name='bin'
order by backup_finish_date;
Backup history query Results
backup_finish_date type first_lsn last_lsn checkpoint_lsn database_backup_lsn
1 2021-09-01T22:58:39 D 40000000032000001 40000000034400001 40000000032000001 0
2 2021-09-01T22:58:40 L 40000000032000001 40000000037600001 40000000032000001 40000000032000001
3 2021-09-01T22:58:40 L 40000000037600001 40000000041600001 40000000038400002 40000000032000001
4 2021-09-01T22:58:40 L 40000000041600001 40000000044800001 40000000042400002 40000000032000001
5 2021-09-01T22:58:40 I 40000000052800001 40000000055200001 40000000052800001 40000000032000001
6 2021-09-01T22:58:40 L 40000000044800001 40000000056000001 40000000052800001 40000000032000001
7 2021-09-01T22:58:40 L 40000000056000001 40000000059200001 40000000056800002 40000000032000001
8 2021-09-01T22:58:41 L 40000000059200001 40000000062400001 40000000060000002 40000000032000001
9 2021-09-01T22:58:41 I 40000000070400001 40000000072800001 40000000070400001 40000000032000001
10 2021-09-01T22:58:41 L 40000000062400001 40000000073600001 40000000070400001 40000000032000001
11 2021-09-01T22:58:41 L 40000000073600001 40000000076800001 40000000074400002 40000000032000001
12 2021-09-01T22:58:41 L 40000000076800001 40000000080000001 40000000077600002 40000000032000001
13 2021-09-01T22:58:58 D 40000000089600001 40000000092000001 40000000089600001 40000000032000001
14 2021-09-01T22:58:58 L 40000000080000001 40000000095200001 40000000089600001 40000000089600001
15 2021-09-01T22:58:58 L 40000000095200001 40000000100000001 40000000096000002 40000000089600001
16 2021-09-01T22:58:58 L 40000000100000001 40000000103200001 40000000100800002 40000000089600001
17 2021-09-01T22:58:58 I 40000000111200001 40000000113600001 40000000111200001 40000000089600001
18 2021-09-01T22:58:58 L 40000000103200001 40000000114400001 40000000111200001 40000000089600001
19 2021-09-01T22:58:58 L 40000000114400001 40000000117600001 40000000115200002 40000000089600001
20 2021-09-01T22:58:59 L 40000000117600001 40000000120800001 40000000118400002 40000000089600001
21 2021-09-01T22:58:59 I 40000000128800001 40000000131200001 40000000128800001 40000000089600001
22 2021-09-01T22:58:59 L 40000000120800001 40000000132000001 40000000128800001 40000000089600001
23 2021-09-01T22:58:59 L 40000000132000001 40000000135200001 40000000132800002 40000000089600001
24 2021-09-01T22:58:59 L 40000000135200001 40000000138400001 40000000136000002 40000000089600001

Find the latest Full backup

Πάμε τώρα στα "δύσκολα" που είναι να εξηγήσουμε τα αποτελέσματα που βλέπουμε.

Στις γραμμές 1 & 13 βλέπουμε τα full backups. Όπως ξέρουμε είναι η βάση στήριξης μας. Κάθε φορά που εκτελούμε full backup γίνεται checkpoint του οποίου το LSN το βλέπουμε στο checkpoint_lsn field.

Αν παρατηρήσουμε στις γραμμές 2-12 στο database_backup_lsn field θα δούμε να έχει την τιμή του checkpoint_lsn της γραμμής 1 που είναι το full backup. To ίδιο ισχύει και για τις γραμμές 14-24 που έχει την τιμή του checkpoint_lsn της γραμμής 13. Αυτό δείχνει ότι τα συγκεκριμένα backups έχουν σαν βάση στήριξης το συγκεκριμένο full backup.

Αν αναρωτιέστε για τις τιμές του database_backup_lsn των γραμμών 1 & 13 όπου στην 1 είναι 0 καθώς είναι το πρώτο full backup που έγινε, και στην 13 είναι το checkpoint_lsn του προηγούμενου full backup δηλαδή της γραμμής 1.

Με αυτές ήδη τις πληροφορίες στα χέρια μας είναι εύκολο να βρούμε το τελευταίο full backup, και ποια είναι τα άλλα που πατάνε πάνω σε αυτό. Oι γραμμές 2-12 έχουν σαν βάση το full backup της γραμμής 1 και οι 14-24 έχουν σαν βάση το full backup της γραμμής 13. Αυτό σημαίνει ότι τελευταίο είναι αυτό της γραμμής 13 αν το κάνουμε sort με βάση την ημερομηνίας ολοκλήρωσης (backup_finish_date) και αυτό κάνουμε restore για αρχή (με norecovery).

Το επόμενο βήμα είναι βρούμε την σειρά των επόμενων backups που πρέπει να γίνουν restore.

Find the latest Differential backup

Στο παράδειγμα μου θα δείτε ότι έχω differentials backups (type='I') και transactional logs backups (type='L').

Αυτό σημαίνει ότι πρέπει να βρω το τελευταίο differential που έχω. Ο εντοπισμός του είναι εύκολος καθώς στα differential backups (γραμμές 17 & 21) που έχουν το database_backup_lsn = checkpoint_lsn του τελευταίου full backup (γραμμή 13) επιλέγω αυτό που έχει το μεγαλύτερο first_lsn και αυτό είναι στην γραμμή 21.

Find Transaction Logs backups

Ας ξεκινήσουμε από το εύκολο σημείο και για αυτό το λόγο ας πάρουμε τις γραμμές 18-20 που είναι transaction log backups.

Σε αυτές παρατηρήστε τα first_lsn & last_lsn. Θα δείτε ότι τo first_lsn της γραμμής 19 έχει την τιμή του last_lsn της γραμμής 18 και αντίστοιχα της 20 αυτό της 19. Με αυτό καταλαβαίνουμε την σειρά που πρέπει να γίνουν τα restores των logs.

Το "δυσκολο" είναι να βρεις το πρώτο transaction log backup που πρέπει να κάνεις restore είτε αν έχεις σενάριο backup που έχει μέσα και differential backup είτε όχι.

Ευτυχώς και στις δύο περιπτώσεις είναι το ίδιο και το πρώτο transaction log backup είναι αυτό που το last_lsn του full backup ή του τελευταίου differential backup είναι μέσα στο διάστημα του first_lsn και του last_lsn του.

Στο παράδειγμα μου είπα και παραπάνω ότι το τελευταίο differential είναι στην γραμμή 21. Αν θα δείτε την γραμμή 22 που είναι το πρώτο transaction log backup θα δείτε ότι η τιμή last_lsn της γραμμής 21 είναι μέσα στο διάστημα που ορίζουν τα first_lsn και last_lsn της γραμμής 22.

All the above in a stored procedure

Επειδή όταν θα συμβεί το κακό κανείς δεν θέλει και δεν είναι σε θέση να σκεφτεί όλα τα παραπάνω, έχω γράψει την παρακάτω stored procedure. Σε αυτή υλοποιώ όλα τα παραπάνω και στο αποτέλεσμα της εκτός από το να δείξει το αποτέλεσμα για να κάνουμε οπτική επιβεβαίωση, δημιουργεί και τα restore commands.

Στο σημείο αυτό να επισημάνω ότι σκόπιμα έχω επιλέξει το τελευταίο backup να το κάνω και αυτό με norecovery οπότε θα αφήσει την βάση σε κατάσταση restoring. Για να φέρουμε σε κατάσταση λειτουργίας την βάση θα πρέπει να εκτελεστεί αμέσως μετά το παρακάτω.

SQL Script

RESTORE DATABASE <db_name> WITH RECOVERY

Ο κώδικας της stored procedure είναι ο παρακάτω και δημιουργώ αυτή στην msdb database. Ο τρόπος εκτέλεσης της είναι ο εξής:

SQL Script


EXEC msdb.dbo.sp_emergency_db_restore @dbname = 'your db name';
GO

Stored Procedure Code

dbo.sp_emergency_db_restore procedure script

use msdb;
go
create proc dbo.sp_emergency_db_restore @dbname nvarchar(128)
as
begin
    set nocount on;

    -- find latest full backup
    declare @chkp_lsn numeric(25,0) = 0;
    select top(1) @chkp_lsn = checkpoint_lsn from msdb.dbo.backupset
    where database_name=@dbname and type='D'
    order by backup_finish_date desc;

    -- store in temp results
    select top(1) * into #backups from msdb.dbo.backupset
    where database_name=@dbname and type='D'
    order by backup_finish_date desc;
 
    -- find latest differential backup
    declare @diff_last_lsn numeric(25,0) = 0;
    select top(1) @diff_last_lsn=last_lsn 
    from msdb.dbo.backupset
    where database_name=@dbname and type='I' and database_backup_lsn= @chkp_lsn
    order by first_lsn desc;
 
    -- store in temp results
    set identity_insert #backups on;
    insert into #backups([backup_set_id], [backup_set_uuid], [media_set_id], [first_family_number], [first_media_number], [last_family_number], [last_media_number], [catalog_family_number], [catalog_media_number], [position], [expiration_date], [software_vendor_id], [name], [description], [user_name], [software_major_version], [software_minor_version], [software_build_version], [time_zone], [mtf_minor_version], [first_lsn], [last_lsn], [checkpoint_lsn], [database_backup_lsn], [database_creation_date], [backup_start_date], [backup_finish_date], [type], [sort_order], [code_page], [compatibility_level], [database_version], [backup_size], [database_name], [server_name], [machine_name], [flags], [unicode_locale], [unicode_compare_style], [collation_name], [is_password_protected], [recovery_model], [has_bulk_logged_data], [is_snapshot], [is_readonly], [is_single_user], [has_backup_checksums], [is_damaged], [begins_log_chain], [has_incomplete_metadata], [is_force_offline], [is_copy_only], [first_recovery_fork_guid], [last_recovery_fork_guid], [fork_point_lsn], [database_guid], [family_guid], [differential_base_lsn], [differential_base_guid], [compressed_backup_size], [key_algorithm], [encryptor_thumbprint], [encryptor_type])
    select top(1) * from msdb.dbo.backupset
    where database_name=@dbname and type='I' and database_backup_lsn= @chkp_lsn
    order by first_lsn desc;
 
    -- find 1st next t-log
    declare @next_log_lsn numeric(25,0) = 0;
    select @next_log_lsn = first_lsn from msdb.dbo.backupset
    where database_name=@dbname and type='L' and database_backup_lsn=@chkp_lsn
    and ( @diff_last_lsn between first_lsn and last_lsn)
    order by first_lsn;
 
    -- store in temp results
    insert into #backups ([backup_set_id], [backup_set_uuid], [media_set_id], [first_family_number], [first_media_number], [last_family_number], [last_media_number], [catalog_family_number], [catalog_media_number], [position], [expiration_date], [software_vendor_id], [name], [description], [user_name], [software_major_version], [software_minor_version], [software_build_version], [time_zone], [mtf_minor_version], [first_lsn], [last_lsn], [checkpoint_lsn], [database_backup_lsn], [database_creation_date], [backup_start_date], [backup_finish_date], [type], [sort_order], [code_page], [compatibility_level], [database_version], [backup_size], [database_name], [server_name], [machine_name], [flags], [unicode_locale], [unicode_compare_style], [collation_name], [is_password_protected], [recovery_model], [has_bulk_logged_data], [is_snapshot], [is_readonly], [is_single_user], [has_backup_checksums], [is_damaged], [begins_log_chain], [has_incomplete_metadata], [is_force_offline], [is_copy_only], [first_recovery_fork_guid], [last_recovery_fork_guid], [fork_point_lsn], [database_guid], [family_guid], [differential_base_lsn], [differential_base_guid], [compressed_backup_size], [key_algorithm], [encryptor_thumbprint], [encryptor_type])
    select * from msdb.dbo.backupset
    where database_name=@dbname and type='L' and database_backup_lsn=@chkp_lsn
    and  first_lsn >= @next_log_lsn  and database_backup_lsn= @chkp_lsn
    order by first_lsn;
 
    -- get details and restore commands
 
    select 
        -- database info
           bs.[database_name] AS database_name
        , bs.[recovery_model] AS database_recovery_model
        , bs.[compatibility_level] AS database_compatibility_level
        
        -- backup device info
        , case bs.[type]
            when 'D' then 'FULL'
            when 'I' then 'DIFFERENTIAL'
            when 'L' then 'LOG'
            when 'F' then 'FILE / FILEGROUP'
            when 'G' then 'DIFFERENTAIL FILE'
            when 'P' then 'PARTIAL'
            when 'Q' then 'DIFFERENTIAL PARTIAL'
          end as backup_type
        , bs.[backup_finish_date] AS backup_finish_date
        , mf.[physical_device_name] AS physical_device_name
        , bs.[user_name] AS backup_operator_username
        , bs.[position] AS position_in_device
          
        -- backup details
        , cast(round(bs.[backup_size] * 1.0 / ( 1024 * 1024 ), 2) AS decimal(10, 2)) as size_in_mb
        , cast(round(bs.[compressed_backup_size] * 1.0 / ( 1024 * 1024 ), 2) AS decimal(10, 2)) AS compressed_size_in_mb 
        , datediff(minute, bs.[backup_start_date], bs.[backup_finish_date]) AS backup_duration_min
        , bs.[is_copy_only] AS is_copy_only
        , bs.[is_force_offline] AS with_norecovery_option 
        , bs.[is_password_protected] AS is_password_protected
        , bs.expiration_date AS backup_expiration_date

        -- backup chain info 
        , bs.first_lsn
        , bs.last_lsn
        , bs.checkpoint_lsn
        , bs.database_backup_lsn as full_lsn
        , case
            when bs.type='D' then 'chain start'
            when bs.type='I' then 'in chain'
            when bs.type='L' 
                 and lag(bs.type,1,null) over (order by backup_finish_date) = 'D' 
                 and (lag(bs.last_lsn,1,0) over (order by backup_finish_date) between bs.first_lsn and bs.last_lsn) 
                 then 'in chain'
            when bs.type='L' 
                 and lag(bs.type,1,null) over (order by backup_finish_date) = 'I' 
                 and (lag(bs.last_lsn,1,0) over (order by backup_finish_date) between bs.first_lsn and bs.last_lsn) 
                 then 'in chain'
            when bs.type='L' 
                 and lag(bs.type,1,null) over (order by backup_finish_date) = 'L' 
                 and lag(bs.last_lsn,1,0) over (order by backup_finish_date) <> first_lsn 
                 then 'chain broken'
            when bs.type='L' 
                 and lag(bs.type,1,null) over (order by backup_finish_date) = 'L' 
                 and lag(bs.last_lsn,1,0) over (order by backup_finish_date) = first_lsn 
                 then 'in chain'
            else 'unknown'
            end as is_in_chain

        -- restore command
        , 'RESTORE ' +
            case 
            when bs.type in ('D','I') then 'DATABASE '
            else 'LOG ' 
            end +
            quotename(bs.[database_name]) +
            ' FROM DISK=' + quotename (mf.[physical_device_name],'''') +
            ' WITH FILE=' + cast(  bs.[position]  as varchar(10)) +
            case 
            when bs.type = 'D' then ', REPLACE, NORECOVERY'
            else ', NORECOVERY'
            end as restore_cmd
           
    from    #backups as bs
    inner join msdb.dbo.backupmediafamily as mf on bs.[media_set_id] = mf.[media_set_id]
    order by  backup_finish_date;
end
go

Summary

Πιστεύω ότι από το άρθρο αυτό έγινε κατανοητό τι σημαίνει αυτό που θα έχετε ακούσει πολλές φορές σαν backup chain και θα είστε σε θέση να καταλάβετε τι σημαίνει το error 4305.

Εύχομαι να μην χρειαστεί να χρησιμοποιήσετε αυτή την stored procedure ποτέ, αλλά κρατήστε την για κάθε ενδεχόμενο.

Να επισημάνω ότι η συγκεκριμένη μπορεί να εκτελεστεί σε SQL Servers 2012 και πάνω και ότι δεν περιέχει τις περιπτώσεις που έχουμε και άλλους τύπους backup όπως file/filegroup, partial κ.λ.π.

Antonios Chatzipavlis

Antonios Chatzipavlis

Antonios is a Data Solutions Consultant and Trainer. He has been working in IT since 1988. In his career, he has worked as senior developer, IT Manager, Solutions Architect and IT Consultant. Since 1995 he has been devoted on new technologies and software development tools, mainly by Microsoft, either by training company staff and colleagues or assisting them in design, development and implementation as a consultant or chief developer. He has focused in Databases and Data Science since 1995. He specialized in Microsoft SQL Server since version 6.0 in areas like SQL Server Internals, Database Design and Development, Business Intelligence and in 2010 he has started working with Azure Data Platform, NoSQL databases, Big Data Technologies and Machine Learning. He is an active member of many IT communities in Greece, answering colleagues' questions and writing articles in his web site. He is the owner of SQLschool.gr which is a community portal with a lot of information about Microsoft SQL Server. He has been a Microsoft Certified Trainer (MCT) since 2000. Microsoft honored him as MVP on Data Platform due to his activities in SQL Server since 2010. He holds a large number of Microsoft Certifications and Microsoft SQL Server Certifications since version 6.5.

Episode

First look: SQL Database in Microsoft Fabric

image

More Episodes...

Tip

What's New in SQL Server 2022 - Episodes

More Tips...

Become a member

If you want to receive updates from us become a member to our community.

Connect

Explore

Learn


sqlschool.gr © 2010-2024 All rights reserved

This site uses cookies for operational and analytics purposes only. By continuing to browse this site, you agree to their use.