Backup i odtworzenie bazy z wykorzystaniem znacznika (mark)

Odtworzenie bazy możemy wykonać z backupu pełnego lub do punktu w czasie z backupu logów transakcyjnych. Dziś pokaże jak odzyskać do punktu (mark) który „zaznaczymy podczas naszych prac na bazie. Pozwala to np na uruchomienie znacznika a następnie odtworzenie kilku zależnych baz do konkretnego punktu aby bazy były konsystentne między sobą.

Na początku utworzymy nową bazę i stworzymy prostą tabelę.

CREATE DATABASE BACKUP_MARK
CREATE TABLE [BACKUP_MARK].[dbo].[TABELA]([random] [int] NULL)

Commands completed successfully.

Baza powinna być w trybie Full Recovery. Wykonamy pełny backup pustej bazy

BACKUP DATABASE [BACKUP_MARK] TO DISK = N'C:\SQLBackup\BACKUP_MARK.bak'
WITH NOFORMAT, INIT, NAME = N'BACKUP_MARK-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

10 percent processed.
20 percent processed.
31 percent processed.
41 percent processed.
50 percent processed.
60 percent processed.
71 percent processed.
81 percent processed.
90 percent processed.
Processed 376 pages for database 'BACKUP_MARK', file 'BACKUP_MARK' on file 1.
100 percent processed.
Processed 5 pages for database 'BACKUP_MARK', file 'BACKUP_MARK_log' on file 1.
BACKUP DATABASE successfully processed 381 pages in 0.082 seconds (36.299 MB/sec).

oraz backup logów

BACKUP LOG [BACKUP_MARK] TO DISK = N'C:\SQLBackup\BACKUP_MARK.bak'
WITH NOFORMAT, NOINIT, NAME = N'BACKUP_MARK-Log Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

57 percent processed.
100 percent processed.
Processed 14 pages for database 'BACKUP_MARK', file 'BACKUP_MARK_log' on file 2.
BACKUP LOG successfully processed 14 pages in 0.017 seconds (6.433 MB/sec).

Teraz wykonamy proste inserty do tabeli.

Najpierw uruchamiamy nasz znacznik (transakcję)

USE [BACKUP_MARK]
GO
BEGIN TRANSACTION TableInsert1
WITH MARK 'Insert do tabeli';

Commands completed successfully.

Teraz wykonujemy inserty. W naszym przypadku będzie to 499 losowych liczb.

DECLARE @Counter int;
SET @Counter = 1;
WHILE @Counter < 10 BEGIN Skok: --SELECT @Counter SET @Counter = @Counter + 1 INSERT INTO [BACKUP_MARK].[dbo].[TABELA] ([random]) VALUES (FLOOR(RAND()*100000)) IF @Counter = 500 GOTO Branch_Two goto SKOK END Branch_Two:

Zamykamy naszą transakcję.

COMMIT TRANSACTION TableInsert1;
GO

Commands completed successfully.

Zapisany znacznik możemy sprawdzić zapytaniem

SELECT * FROM msdb.[dbo].[logmarkhistory]

database_name      mark_name        description          user_name              lsn                  mark_time
----------------------------------------------------------------------------------------------------------------------------
BACKUP_MARK        TableInsert1     Insert do tabeli     MICEK1968\micek1968    37000000036000531    2020-01-23 19:46:32.820

(1 row affected)

Aby sprawdzić jaki ostatni LSN został zapisany w backupie bazy

USE [BACKUP_MARK]
SELECT last_log_backup_lsn
FROM sys.database_recovery_status
WHERE database_id = DB_ID()

last_log_backup_lsn
---------------------------------------
37000000032800001

(1 row affected)

Sprawdzamy ile wierszy się zapisało w tabeli

SELECT count(*)
FROM [BACKUP_MARK].[dbo].[TABELA]

-----------
499

(1 row affected)

Wykonujemy następne 499 insertów

DECLARE @Counter int;
SET @Counter = 1;
WHILE @Counter < 10 BEGIN Skok: --SELECT @Counter SET @Counter = @Counter + 1 INSERT INTO [BACKUP_MARK].[dbo].[TABELA] ([random]) VALUES (FLOOR(RAND()*100000)) IF @Counter = 500 GOTO Branch_Two goto SKOK END Branch_Two:

Sprawdzamy ilość wierszy

SELECT count(*)
FROM [BACKUP_MARK].[dbo].[TABELA]

-----------
998

(1 row affected)

Wykonujemy kolejny backup logów naszej bazy

BACKUP LOG [BACKUP_MARK] TO DISK = N'C:\SQLBackup\BACKUP_MARK.bak'
WITH NOFORMAT, NOINIT, NAME = N'BACKUP_MARK-Log Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

11 percent processed.
20 percent processed.
31 percent processed.
40 percent processed.
52 percent processed.
60 percent processed.
72 percent processed.
81 percent processed.
92 percent processed.
100 percent processed.
Processed 259 pages for database 'BACKUP_MARK', file 'BACKUP_MARK_log' on file 3.
BACKUP LOG successfully processed 259 pages in 0.038 seconds (53.145 MB/sec).

I następne 499 inserty

DECLARE @Counter int;
SET @Counter = 1;
WHILE @Counter < 10 BEGIN Skok: --SELECT @Counter SET @Counter = @Counter + 1 INSERT INTO [BACKUP_MARK].[dbo].[TABELA] ([random]) VALUES (FLOOR(RAND()*100000)) IF @Counter = 500 GOTO Branch_Two goto SKOK END Branch_Two:

I ponownie sprawdzamy ilość wierszy w tabeli

SELECT count(*)
FROM [BACKUP_MARK].[dbo].[TABELA]

 -----------
1497

(1 row affected)

I kolejny backup logów

BACKUP LOG [BACKUP_MARK] TO DISK = N'C:\SQLBackup\BACKUP_MARK.bak'
WITH NOFORMAT, NOINIT, NAME = N'BACKUP_MARK-Log Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

12 percent processed.
22 percent processed.
31 percent processed.
41 percent processed.
50 percent processed.
60 percent processed.
73 percent processed.
82 percent processed.
92 percent processed.
100 percent processed.
Processed 251 pages for database 'BACKUP_MARK', file 'BACKUP_MARK_log' on file 4.
BACKUP LOG successfully processed 251 pages in 0.035 seconds (56.026 MB/sec).

Pora odzyskać naszą bazę. Interesuje nas punkt kiedy oznaczyliśmy znacznikiem (zakończyliśmy transakcję)

Wcześniej poznaliśmy numer LSN odpowiadającemu naszemu znacznikowi (LSN=37000000036000531)

Możemy sprawdzić z których plików backupu będzimy korzystać przy odzyskiwaniu bazy

use msdb
go
select s.backup_set_id,
s.first_lsn,
s.last_lsn,
s.database_name,
s.backup_start_date,
s.backup_finish_date,
s.type,
f.physical_device_name
from backupset s join backupmediafamily f
on s.media_set_id = f.media_set_id
WHERE s.database_name = 'BACKUP_MARK'
and first_lsn < 37000000036000531 order by s.backup_finish_date

backup_set_id   first_lsn             last_lsn              database_name     backup_start_date          backup_finish_date         type   physical_device_name
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1148            37000000021600170     37000000030400001     BACKUP_MARK       2020-01-23 19:46:16.000    2020-01-23 19:46:16.000    D      C:\SQLBackup\BACKUP_MARK.bak
1149            37000000021600170     37000000032800001     BACKUP_MARK       2020-01-23 19:46:20.000    2020-01-23 19:46:20.000    L      C:\SQLBackup\BACKUP_MARK.bak
1150            37000000032800001     38000000051200001     BACKUP_MARK       2020-01-23 19:46:48.000    2020-01-23 19:46:48.000    L      C:\SQLBackup\BACKUP_MARK.bak

(3 rows affected)

Jak widać będziemy potrzebowali pliku z backupem pełnym i dwa backupy logów

Przypominam że wykonaliśmy w trakcie naszych prac:
-pełny backup bazy (backup File=1)
-backup logów (backup File=2) - 0 wierszy w tabeli
-inserty do tabeli (499 wierszy)
-ustawiliśmy znacznik (TableInsert1)
-wykonaliśmy kolejne 499 insertów
-backup logów (backup File=3) - 998 wierszy
-inserty do tabeli (499 wierszy)
-backup logów (backup File=4) - 1497 wierszy

Tak więc aby odzyskać bazę do punktu oznaczenia naszego znacznika musimy odzyskać bazę z pełnego backupu (File=1) a następnie z backupu logów (File=2 i File=3). Nasz znacznik jest zbackupowany w backupie oznaczonym jako File=3

najpierw przestawimy bazę w tryb Single User
USE [master]
ALTER DATABASE [BACKUP_MARK] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Commands completed successfully.

Wykonujemy backup obecnych logów.
BACKUP LOG [BACKUP_MARK]
TO DISK = N'C:\SQLBackup\BACKUP_MARK_LogBackup_2020-01-23_19-26-06.bak'
WITH NOFORMAT, NOINIT, NAME = N'BACKUP_MARK_LogBackup_2020-01-23_19-26-06',
NOSKIP, NOREWIND, NOUNLOAD, NORECOVERY , STATS = 5

100 percent processed.
Processed 4 pages for database 'BACKUP_MARK', file 'BACKUP_MARK_log' on file 2.
BACKUP LOG successfully processed 4 pages in 0.017 seconds (1.838 MB/sec).

Teraz odtwarzamy naszą bazę z pełnego logu

RESTORE DATABASE [BACKUP_MARK] FROM DISK = N'C:\SQLBackup\BACKUP_MARK.bak'
WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5

6 percent processed.
10 percent processed.
16 percent processed.
20 percent processed.
25 percent processed.
31 percent processed.
35 percent processed.
41 percent processed.
46 percent processed.
50 percent processed.
56 percent processed.
60 percent processed.
65 percent processed.
71 percent processed.
75 percent processed.
81 percent processed.
86 percent processed.
90 percent processed.
96 percent processed.
100 percent processed.
Processed 376 pages for database 'BACKUP_MARK', file 'BACKUP_MARK' on file 1.
Processed 5 pages for database 'BACKUP_MARK', file 'BACKUP_MARK_log' on file 1.
RESTORE DATABASE successfully processed 381 pages in 0.774 seconds (3.845 MB/sec).

Spójrzmy co będzie jak będziemy chcieli uruchomić odtwarzanie od razu z ostatniego backupu (File=3) podając opcję STOPMARK i wpisując nazwę naszej transakcji (marka)

RESTORE LOG [BACKUP_MARK]
FROM DISK = N'C:\SQLBackup\BACKUP_MARK.bak'
WITH FILE = 3,
RECOVERY,
STOPATMARK = 'TableInsert1';

Msg 4305, Level 16, State 1, Line 236
The log in this backup set begins at LSN 89000000544800001, 
which is too recent to apply to the database.
 An earlier log backup that includes LSN 89000000542400001 can be restored.
Msg 3013, Level 16, State 1, Line 236
RESTORE LOG is terminating abnormally.

Jak widać SQL zwrócił nam błąd związany z błędnym numerem sekwencji (LSN). Musimy bazę odtworzyć kolejno z naszych backupów logów. Wykonywaliśmy je ręcznie i wiemy na którym pliku backupu logu mamy skończyć ale równie dobrze backup może wykonywać się automatycznie więc będziemy wykonywać odtworzenie do czasu prawidłowego zakończenia procesu.

Zaczynamy od pliku backupu logu File=2
RESTORE LOG [BACKUP_MARK]
FROM DISK = N'C:\SQLBackup\BACKUP_MARK.bak'
WITH FILE = 2,
RECOVERY,
STOPATMARK = 'TableInsert1';

Processed 0 pages for database 'BACKUP_MARK', file 'BACKUP_MARK' on file 2.
Processed 12 pages for database 'BACKUP_MARK', file 'BACKUP_MARK_log' on file 2.
This log file contains records logged before the designated mark. 
The database is being left in the Restoring state so you can apply another log file.
RESTORE LOG successfully processed 12 pages in 0.013 seconds (6.911 MB/sec).

Potrzebny jest kolejny plik backupu logów aby odtworzyć bazę do punktu naszego znacznika. Odtwarzamy kolejny plik backupu logów (File=3)

RESTORE LOG [BACKUP_MARK]
FROM DISK = N'C:\SQLBackup\BACKUP_MARK.bak'
WITH FILE = 3,
RECOVERY,
STOPATMARK = 'TableInsert1';

Processed 0 pages for database 'BACKUP_MARK', file 'BACKUP_MARK' on file 3.
Processed 259 pages for database 'BACKUP_MARK', file 'BACKUP_MARK_log' on file 3.
RESTORE LOG successfully processed 259 pages in 0.034 seconds (59.397 MB/sec).

Odzyskanie się zakończyło. Komunikat zwrócił informację o poprawnym odtworzniu bazy. Próby kolejnego odtwarzania plików backupu logów zwrócą nam błąd

RESTORE LOG [BACKUP_MARK]
FROM DISK = N'C:\SQLBackup\BACKUP_MARK.bak'
WITH FILE = 4,
RECOVERY,
STOPATMARK = 'TableInsert1';

Msg 3117, Level 16, State 1, Line 252
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 252
RESTORE LOG is terminating abnormally.

Tak więc bazę mamy odtworzoną. Powinno być 499 wierszy.

SELECT count(*)
FROM [BACKUP_MARK].[dbo].[TABELA]

-----------
499

(1 row affected)

Jak widać baza odzyskała się prawidłowo.

Po oznaczeniu znacznikiem bazy sprawdziliśmy numer LSN odpowiadający naszemu znacznikowi. Odtwarzając bazę możemy skorzystać też z tego numeru LSN (zamiast nazwy znacznika).

RESTORE LOG AdventureWorks2 FROM DISK = 'C:\SQLBackup\BACKUP_MARK.bak'
WITH STOPATMARK = 'lsn:37000000036000531'
GO

Na koniec czyścimy bazy...

USE master
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'A1'
GO
DROP DATABASE A1
GO

Ten wpis został opublikowany w kategorii Microsoft SQL. Dodaj zakładkę do bezpośredniego odnośnika.

Dodaj komentarz