Backup bazy danych Microsoft SQL Server, część 1

Tryby odtwarzania baz danych
Bazy danych mogą działać w jednym z trzech trybów odzyskiwania:

1. W edycji SQL Express dla tworzonych baz danych automatycznie ustawiany jest tryb SIMPLE. W ten sposób administrator nie musi się martwić o rozmiar pliku dziennika, ale niemożliwe jest odtworzenie bazy danych dla dowolnego punktu w czasie. W tym trybie odtworzenie bazy możliwe będzie tylko do momentu ostatnio wykonanej pełnej lub różnicowej bazy danych, a więc typowe RPO waha się od dni do tygodni. Żaden użytkownik biznesowy nie zaakceptowałby tak wysokiego RPO, szczególnie gdyby wiedział, że jego zmniejszenie do kilkunastu minut nie wiąże się z dodatkowymi kosztami.
2. W pozostałych edycjach serwera SQL bazy użytkowników domyślnie działają w trybie FULL. W tym trybie wszystkie operacje są w pełni logowane w dzienniku transakcyjnym, a automatycznie wykonywane przez serwer punkty kontrolne nie dezaktywują wirtualnych plików dziennika. Zapobieganie niekontrolowanemu powiększaniu się pliku dziennika wymaga więc regularnego (wykonywanego nawet kilka razy w ciągu godziny) tworzenia kopii zapasowych dziennika transakcyjnego. Z drugiej strony przełączenie bazy w ten tryb gwarantuje możliwość jej odtworzenia do dowolnego punktu w czasie i zmniejszenie RPO do częstotliwości wykonywania kopii zapasowych dziennika, np. do kilkunastu minut.
3. W trybie BULK_LOGGED niektóre operacje (w tym przebudowa i tworzenie indeksów oraz masowe ładowanie danych) logowane są w minimalnym stopniu, a automatycznie wykonywane przez serwer SQL punkty kontrolne nie dezaktywują wirtualnych plików dziennika. Bazy danych przełączane są w ten tryb w celu poprawy wydajności minimalnie logowanych operacji. Przełączenie bazy w ten tryb nie gwarantuje możliwość jej odtworzenia do dowolnego punktu w czasie, ale RPO pozostaje równie małe co w trybie FULL.

Zmienić tryb odtwarzania bazy danych możemy, wykonując poniższą instrukcję:
ALTER DATABASE AdventureWorks
SET RECOVERY FULL;

Przełączanie bazy pomiędzy trybami FULL a BULK_LOGGED nie wpływa na możliwość jej odtworzenia z wykonanych kopii zapasowych. Natomiast przełączenie bazy w tryb SIMPLE i następnie powrót do oryginalnego trybu oznacza, że wykonane po tym przełączeniu, a przed pełną kopią bazy danych, kopie dziennika transakcyjnego będą bezużyteczne.

Najważniejsze z perspektywy tworzenia i odtwarzania kopii zapasowych dane na temat baz zwraca poniższe zapytanie:
SELECT DB_NAME(s.database_id), recovery_model_desc, last_log_backup_lsn, log_reuse_wait_desc
FROM sys.database_recovery_status s
JOIN sys.databases d ON s.database_id = d.database_id
WHERE s.database_id>4;
—
AdventureWorks FULL 72000000045500165 NOTHING
Analytics FULL NULL LOG_BACKUP
…

Pierwsza kolumna wyniku zwraca nazwę bazy danych, druga – tryb jej odzyskiwania, trzecia – numer ostatniej, zapisanej w kopii zapasowej dziennika transakcyjnego, a ostatnia – powód, dla którego serwer SQL nie może deaktywować wirtualnych plików dziennika (w wypadku bazy Analytics jest to zbyt rzadko wykonywana kopia dziennika transakcyjnego).

Tworzenie kopii zapasowych
Kopie zapasowe tworzy się w celu:

1. Odtworzenia bazy po awarii.
2. Zainicjowania replikacji lub podwajania baz danych.
3. Przeniesienia bazy na inny serwer SQL.
4. Zabezpieczenia się przed niespodziewanymi skutkami aktualizacji serwera SQL oraz prób naprawienia spójności baz danych.
5. Kontrolowania wielkości dziennika transakcyjnego.

Serwer SQL zapisuje kopie w trwałych lub tymczasowych urządzeniach kopii zapasowych.

Urządzenia kopii zapasowych
Serwer SQL pozwala zapisywać kopie zapasowe na dwóch typach urządzeń kopii zapasowych (w jednym urządzeniu kopii zapasowych uda się zapisać wielu kopii, czyli w przypadku urządzenia dyskowego jeden plik może zawierać różne kopie zapasowe):

1. Lokalnych lub sieciowych dyskach twardych.
2. Lokalnie podłączonych i zgodnych z systemem Windows napędach taśm.

Ze względu na niezawodność oraz wydajność kopie zapasowe powinny być zapisywane na lokalnych dyskach twardych, innych niż dyski, na których znajdują się pliki baz danych i system operacyjny, a następnie kopiowane do bezpiecznej lokalizacji.

Urządzenia kopii zapasowych dzielą się na fizyczne i logiczne. Definicja logicznego urządzania kopii zapasowych zapisywana jest w bazie master i, tworząc oraz odtwarzając kopie, możemy posługiwać się jego nazwą. Logiczne urządzenia kopii zapasowych ułatwiają tworzenie i odtwarzanie wielu kopii przechowywanych w pojedynczych plikach.

Przechowywanie wielu kopii w pojedynczych plikach ma jednak dwie podstawowe wady:

1. Po pierwsze, dopisując do pliku kolejną kopię, możemy przypadkowo (za pomocą opcji FORMAT lub INIT) skasować wcześniej zapisane w tym pliku kopie.
2. Odtwarzanie kopii wymaga podania ich numeru, a ta informacja nie jest bezpośrednio dostępna. Jeżeli odtwarzający bazę danych administrator błędnie poda numer innej kopii (należy pamiętać, że odtworzenie bazy po awarii jest stresującą sytuacją), cały proces odtwarzania bazy będzie musiał zostać powtórzony.

Przechowywanie wielu kopii w pojedynczych plikach zwiększa ryzyko ich utraty i komplikuje proces ich odtwarzania. Ponieważ definiowanie logicznych urządzeń kopii zapasowych na potrzeby przechowywania w nich pojedynczych plików nie ma sensu, powinniśmy korzystać wyłącznie z fizycznych urządzeń kopii zapasowych i przechowywać w każdym pliku tylko jedną kopię zapasową.

Typy kopii zapasowych
Serwer SQL umożliwia tworzenie trzech typów kopii zapasowych: pełnych kopii zapasowych baz danych, kopii dziennika transakcyjnego oraz kopii różnicowych baz danych. Dodatkowo, kopiowane mogą być całe bazy danych lub ich wybrane pliki i grupy plików. Wszystkie typy kopii zapasowych tworzone są online, a więc nie wymagają zatrzymywania serwera SQL czy przerywania pracy użytkowników. Jedyny wpływ tworzenia kopii na użytkowników wynika z wykonywania przez serwer SQL dodatkowych operacji odczytu i zapisu, co może przełożyć się na zmniejszenie szybkości zapisywania w pliku dziennika transakcji oraz szybkości odczytywania stron z dysku i w konsekwencji na obniżenie wydajności serwera.

Pełna kopia bazy danych
Punktem wyjścia do otworzenia bazy jest jej pełna kopia zapasowa. Zawiera wszystkie zapisane w bazie dane – zapisywane w niej są wszystkie zakresy, w których zaalokowana jest przynajmniej jedna strona danych oraz część dziennika transakcyjnego niezbędna do odtworzenia bazy danych z momentu wykonania tej kopii.
Wbrew powszechnej opinii pełna kopia zapasowa nie zawiera całych plików danych, a jedynie te ich części (zakresy), w których rzeczywiście znajdują się dane. Oznacza to, że wielkość nieskompresowanej kopii zapasowej możemy oszacować na podstawie wyników wywołania procedury sp_spaceused, a nie na podstawie wielkości plików bazy danych:

EXEC sp_spaceused;
—
database_name database_size unallocated space
AdventureWorks 230.50 MB 50.82 MB

reserved data index_size unused
181944 KB 96760 KB 78808 KB 57576 KB

Choć wszystkie pliki bazy AdventureWorks mają łączny rozmiar 230,5 MB, to przechowuje jedynie 180 MB danych, a więc wielkość pliku pełnej kopii zapasowej będzie wynosiła około 180 MB, a nie 230 MB.

W trakcie tworzenie pełnej kopii bazy danych serwer SQL:

1. Synchronizuje, za pomocą punktu kontrolnego, bufory z zapisanymi w plikach danych stronami. Operacja ta ma na celu zmniejszenie aktywnej części dziennika transakcyjnego, a więc skrócenie czasu odtwarzania w przyszłości bazy z tej kopii.
2. Zapamiętuje numer najstarszej, otwartej transakcji – ta informacja zostanie przez serwer wykorzystana do określenia, która część dziennika transakcyjnego zostanie dołączona do kopii.
3. Kolejno kopiuje zakresy z plików danych do urządzenia kopii zapasowej. W trakcie ich kopiowania w bazie danych nie są zakładane żadne blokady – kopiowanie stron jest operacją fizyczną, wykonywaną z pominięciem bufora danych. Oznacza to, że odtwarzając kopię zapasową, przywrócimy bazę danych dokładnie do stanu z jej wykonania. Serwer SQL odczytuje zapisane na kopiowanych stronach dane tylko, jeżeli włączona jest (opisana w drugiej części artykułu) opcja sprawdzania poprawności tworzonej kopii zapasowej. Nawet wtedy dane są odczytywane wyłącznie w celu ich porównania z zapisanymi w nagłówkach stron sumami kontrolnymi.
4. Po zakończeniu kopiowania danych do pliku kopii zapasowej zapisywany jest dziennik transakcyjny. Punkt, od którego rozpoczyna się kopiowanie dziennika, wyznaczony zostaje przez najmniejszą z poniższych wartości:
   a) najmniejszy numer LSN aktywnej w momencie tworzenia kopii transakcji (numer pierwszej transakcji wykonanej po punkcie kontrolnym),
   b) najmniejszy numer LSN otwartej w momencie tworzenia kopii transakcji,
   c) najmniejszy numer LSN niezreplikowanej transakcji.
5. Dziennik transakcyjny jest kopiowany do miejsca w którym znajduje się wpis dotyczący ostatniej, wykonanej podczas tworzenia kopii, operacji. W efekcie kopia zapasowa zawiera dane z momentu zakończenia, a nie rozpoczęcia jej wykonywania.

Żeby wykonać pełną kopię bazy danych, należy wykonać poniższą instrukcję:

BACKUP DATABASE AdventureWorks
TO DISK =’E:\AdventureWorksFull.BAK’;
—
Processed 22672 pages for database 'AdventureWorks’, file 'AdventureWorks_Data’ on file 1.
Processed 16 pages for database 'AdventureWorks’, file 'AdvWorksHR’ on file 1.
Processed 4 pages for database 'AdventureWorks’, file 'AdventureWorks_Log’ on file 1.
BACKUP DATABASE successfully processed 22692 pages in 1.984 seconds (89.351 MB/sec).

Różnicowa kopia bazy danych
Różnicowa kopia bazy danych tym różni się od pełnej kopii, że zawiera tylko zakresy zmodyfikowane od czasu wykonania ostatniej pełnej kopii plus wymaganą do odtworzenia bazy część dziennika transakcyjnego. Oznacza to, że kolejne kopie różnicowe będą coraz większe aż do momentu, w którym rozmiar kopii różnicowej będzie równy rozmiarowi pełnej kopii (ich rozmiary będą takie same, jeżeli od czasu wykonania ostatniej pełnej kopii zmodyfikowane zostaną wszystkie zakresy danych).

W trakcie wykonywania kopii różnicowej serwer SQL odczytuje bit parzystości zakresu danych (bit informujący o tym, czy dany zakres został zmieniony). Bit ten jest zerowany wyłącznie podczas tworzenia pełnej kopii zapasowej, chyba że została utworzona z opisaną w dalszej części artykułu opcją COPY_ONLY.

Różnicowe kopie zapasowe najczęściej tworzone są w celu zmniejszenia liczby potrzebnych do odtworzenia bazy danych kopii dziennika transakcyjnego, co pozwala znacznie skrócić proces odzyskiwania bazy danych. Żeby wykonać różnicową kopię bazy danych, należy wykonać poniższą instrukcję:
BACKUP DATABASE AdventureWorks
TO DISK =’E:\AdventureWorksDiff.BAK’
WITH DIFFERENTIAL;
—
Processed 32 pages for database 'AdventureWorks’, file 'AdventureWorks_Data’ on file 1.
Processed 8 pages for database 'AdventureWorks’, file 'AdvWorksHR’ on file 1.
Processed 1 pages for database 'AdventureWorks’, file 'AdventureWorks_Log’ on file 1.
BACKUP DATABASE WITH DIFFERENTIAL successfully processed 41 pages in 0.082 seconds (3.900 MB/sec).

Różnicowe kopie baz danych zawierają również strony zmodyfikowane przez wycofane transakcje. Wynika to z tego, że serwer SQL kompensuje wycofywane transakcje (np. wstawia usunięte wiersze i przywraca poprzednie wartości zmienionych pól). Ponieważ pierwsza zmiana powoduje ustawienie bitu parzystości zakresu, zmodyfikowany przez wycofaną transakcję zakres zostanie dołączony do kopii różnicowej.

Kopia dziennika transakcyjnego
Kopia dziennika transakcyjnego jest kopią przyrostową — zawiera wszystkie wpisy dziennika transakcyjnego zapisane od momentu wykonania jego poprzedniej kopii (im częściej wykonywane są kopie dziennika, tym będą mniejsze). Innymi słowy, jeżeli jedna po drugiej wykonamy dwie kopie różnicowe, będą zawierały te same dane. Natomiast dwie kopie dziennika nigdy nie będą zawierać tych samych transakcji, z wyjątkiem transakcji, które były otwarte w trakcie ich wykonywania. Wtedy wpisy dotyczące początku transakcji trafią do pierwszej kopii dziennika, a pozostała – do drugiej.

Po skopiowaniu do pliku kopii odpowiedniej części dziennika serwer SQL dezaktywuje zero lub więcej wirtualnych plików dziennika.

Żeby wykonać kopię dziennika transakcyjnego, należy wykonać poniższą instrukcję:

BACKUP LOG AdventureWorks
TO DISK =’E:\AdventureWorksLog.BAK’;
—
Processed 7 pages for database 'AdventureWorks’, file 'AdventureWorks_Log’ on file 1.
BACKUP LOG successfully processed 7 pages in 0.052 seconds (1.014 MB/sec).

Jeżeli baza działa w trybie BULK_LOGGED, kopie dziennika transakcyjnego będą dodatkowo zawierały zakresy danych zmodyfikowane przez minimalnie logowane operacje. W tym trybie w dzienniku transakcyjnym nie są zapisywane wszystkie, potrzebne do odtworzenia takich operacji, informacje, a więc w celu umożliwiania odtworzenia bazy serwer SQL dołącza do kopii dziennika odpowiednie zakresy danych. Oznacza to, że tryb BULK_LOGGED pozwala przyspieszyć wykonywanie minimalnie logowanych operacji, ale nie zmniejsza wielkości kopii dziennika transakcyjnego.

Tworząc kopie dziennika transakcyjnego, należy pamiętać, że do odtworzenia bazy potrzebne będą wszystkie kopie dziennika wykonane po ostatniej pełnej lub różnicowej kopii bazy danych. Wynika to z tego, że kopie dziennika są przyrostowe, a więc brak wcześniejszej kopii uniemożliwi odtworzenie wszystkich późniejszych kopii dziennika. Ciągłość kopii dziennika transakcyjnego zostaje zerwana, gdy:

1. Dziennik transakcyjny zostanie obcięty za pomocą opisanych w drugiej części artykułu opcji WITH TRUNCATE lub WITH NO_LOG.
2. Baza zostanie przełączona w tryb SIMPLE.
3. Baza zostanie odtworzona z migawki (Snapshot), ale tylko edycja Enterprise pozwala na tworzenie migawek baz danych.
4. Utracona zostanie kopia dziennika wykonana bez opcji COPY_ONLY.

Po zerwaniu ciągłości kopii zapasowych dziennika transakcyjnego należy natychmiast wykonać pełną kopię bazy danych.

Opcja COPY_ONLY
Wykonanie i utracenie pełnej kopii zapasowej bazy danych lub kopii dziennika transakcyjnego uniemożliwia odtworzenia później wykonanych kopii zapasowych bazy danych. Wynika to z tego, że:

1. Pełna kopia zeruje bit parzystości zakresów danych, a więc to ta kopia będzie bazową dla później wykonanych kopii różnicowych.
2. Kopia dziennika transakcyjnego obcina ten dziennik (dezaktywuje wirtualne pliki dziennika), a więc zrywa jego ciągłość.

Na przykład, jeżeli administrator wykona pełną kopię bazy i przekaże ją użytkownikowi w celu odtworzenia przez niego tej bazy na innym serwerze, tworzone według harmonogramu kolejne kopie różnicowe będą bezużyteczne – żeby je odtworzyć, potrzebna będzie przekazana użytkownikowi pełna kopia zapasowa. Rozwiązanie tego problemu polega na tworzeniu wszystkich dodatkowych (niewchodzących w skład przyjętej strategii tworzenia kopii zapasowych) kopii z opcją COPY_ONLY. Tak wykonane kopie zapasowe nie modyfikują bazy danych (kopie pełne nie zerują bitu parzystości, a kopie dziennika nie obcinają go):
BACKUP DATABASE AdventureWorks
TO DISK =’E:\AdventureWorksExtra.BAK’
WITH COPY_ONLY;