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
goSummary
Πιστεύω ότι από το άρθρο αυτό έγινε κατανοητό τι σημαίνει αυτό που θα έχετε ακούσει πολλές φορές σαν backup chain και θα είστε σε θέση να καταλάβετε τι σημαίνει το error 4305.
Εύχομαι να μην χρειαστεί να χρησιμοποιήσετε αυτή την stored procedure ποτέ, αλλά κρατήστε την για κάθε ενδεχόμενο.
Να επισημάνω ότι η συγκεκριμένη μπορεί να εκτελεστεί σε SQL Servers 2012 και πάνω και ότι δεν περιέχει τις περιπτώσεις που έχουμε και άλλους τύπους backup όπως file/filegroup, partial κ.λ.π.