Polecenie BACKUP LOG

Ćwiczenie 3
Tworzenie kopii bezpieczeństwa bazy danych.
1
Wprowadzenie teoretyczne. ................................................................................................................. 2
Typy kopii bezpieczeństwa .............................................................................................................. 2
Modele odtwarzania baz danych i kopie bezpieczeństwa ................................................................ 3
Model Full recovery ..................................................................................................................... 3
Model Bulk-logged recovery ....................................................................................................... 3
Model Simple recovery ................................................................................................................ 3
Tworzenie narzędzia archiwizacyjnego ........................................................................................... 3
Kontrolowanie urządzeń archiwizacyjnych przy pomocy Transact-SQL ................................... 3
Sprawdzanie spójności bazy danych ................................................................................................ 4
Inne sposoby sprawdzenia integralności .......................................................................................... 5
Tworzenie kopii bezpieczeństwa bazy danych ................................................................................ 6
Kopie bezpieczeństwa na pojedynczym nośniku archiwizującym (single-device) ..................... 6
Kopie bezpieczeństwa równolegle rozproszone (parallel striped) ............................................... 6
Wiele kopii bezpieczeństwa na pojedynczym nośniku ................................................................ 6
Polecenie BACKUP DATABASE dla całej bazy danych ............................................................... 6
Polecenie DIFFERENTIAL BACKUP DATABASE ..................................................................... 8
Polecenie BACKUP LOG ................................................................................................................ 8
Przebieg ćwiczenia ............................................................................................................................... 9
Przykładowe pytania kontrolne. ......................................................................................................... 15
Wprowadzenie teoretyczne.
Typy kopii bezpieczeństwa
Można stworzyć kilka rodzajów kopii bezpieczeństwa w SQL Serverze 2000. Zostaną omówione: kopia
bezpieczeństwa całej bazy danych (full), różnicowa kopia bazy danych (differential), kopie bezpieczeństwa pliku i
grupy plików oraz kopie bezpieczeństwa dziennika transakcji. W tym rozdziale wszystkie kopie bezpieczeństwa z
wyjątkiem kopii dziennika transakcji będą nazywane kopiami bazy danych.
Poniżej przedstawiono omówienie różnych typów kopii bezpieczeństwa:

Pełna kopia bezpieczeństwa bazy danych (full backing) — tworzy kopię całej bazy danych, włączając w to tabele,
indeksy, tabele systemowe i obiekty bazy danych, (które są zawarte w tych tabelach systemowych). Kopia
bezpieczeństwa bazy danych tworzy również kopię wpisów do dziennika transakcji ale nie przechowuje pustych
stron ani nie usuwa z bazy danych żadnych wpisów w dzienniku transakcji.

Różnicowa kopia bezpieczeństwa bazy danych(differential backing) — tworzy kopię wszystkich stron danych,
które były modyfikowane od czasu tworzenia ostatniej kopii całej bazy danych. Odtwarzanie z kopii tego typu jest
szybsze niż odtwarzanie z kopii bezpieczeństwa dziennika transakcji. Różnicowe kopii zapasowe są używane tylko
w kombinacji z pelnymi kopiami zapasowymi.

Kopia bezpieczeństwa pliku i/lub grupy plików zakłada tworzenie kopii bezpieczeństwa jedynie wybranych plików
lub grup plików, a nie całej bazy danych. Jeżeli jedna tabela lub więcej tabel lub indeksów ma zostać
umieszczonych w odrębnych grupach plików zamiast pozostawienia wszystkich danych i indeksów w domyślnej
grupie plików, można niezależnie utworzyć kopię danych. Zaletą tego rozwiązania jest to, że jeśli pojedynczy dysk
ulegnie uszkodzeniu a została zrobiona kopia plików z tego dysku, można odtworzyć uszkodzone pliki bez
potrzeby odtwarzania całej bazy danych. Ten typ kopii bezpieczeństwa jest szybszy niż odtwarzanie kopii całej
bazy danych.

Kopia bezpieczeństwa dziennika transakcji zawiera kopię wpisów wszystkich zmian jakie zostały wykonane w
bazie danych w zadanym czasie. Zawiera wyrażenia uruchamiane przez użytkowników, jak również działania
drugoplanowe systemu, takie jak alokacja przestrzeni w bazie danych. Dzięki transakcjom zapisywanym w
dzienniku można odtworzyć wszystkie działania (transakcje). Inną korzystną własnością dziennika transakcji jest
to, że można powtórzyć (uruchomić ponownie) transakcje do określonego punktu w czasie. Gdy
2
Modele odtwarzania baz danych i kopie bezpieczeństwa
SQL Server 2000 wprowadził modele odtwarzania, które są przydatne przy planowaniu kopii bezpieczeństwa. Wybór
modelu odtwarzania zależy od rodzaju baz danych i od typu wykonywanych kopii bezpieczeństwa. Dostępne są trzy
następujące modele odtwarzania:
 Pełny (Full recovery)
 Rejestracja masowa (Bulk-logged recovery)
 Prosty (Simple recovery).
Model Full recovery
Model Full recovery jest najczęściej uruchamianym modelem. Model ten obsługuje wszystkie kopie baz danych (pełną,
różnicową, kopię dziennika transakcji, kopię pliku/grupy). Można odtworzyć dane do punktu w czasie lub do
„zaznaczonej” transakcji. Jest to domyślne ustawienie dla nowej bazy tworzonej przy pomocy wersji Standard i
Enterprise Editon SQL Servera. Odpowiadająca temu modelowi strategia kopii bezpieczeństwa powinna obejmować
pełne kopie bezpieczeństwa, kopie dziennika transakcji, oraz opcjonalni , różnicowe kopie bezpieczeństwa.
Model Bulk-logged recovery
Po wykonaniu wybranych poleceń, takich jak SELECT INTO, BULK INSERT, CREATE INDEX, WRITETEXT
i UPDATETEXT, zostają zapisane bardzo duże ilości danych. Przy zastosowaniu modelu Full recovery, każdy wiersz
dodawany do SQL Servera jest w pełni odtwarzany w dzienniku transakcji. Można sobie wyobrazić, że oznacza to, że
dane są zapisywane dwukrotnie. Zapewnia to maksimum możliwości odtworzenia danych ale może być nadmierne w
stosunku do potrzeb.
W trybie Bulk-logged, dane modyfikowane przez te polecenia nie są w pełni rejestrowane; obrazy modyfikowanych
stron są rejestrowane po załadowaniu strony. Z powodu metody używanej do rejestracji danych w bazie nie jest
możliwe odtwarzanie do punktu w czasie. Można odtworzyć jedynie do punktu końcowego kopii bazy danych lub do
końca kopii bezpieczeństwa dziennika transakcji.
Model Simple recovery
Model simple recovery jest najbardziej restrykcyjny. Zasadniczo, model ten jest używany gdy jedyne co jest istotne to
powrót do ostatniej pełnej kopii bezpieczeństwa lub kopii różnicowej. Kopie bezpieczeństwa dziennika transakcji są
dostępne i dziennik transakcji jest okresowy czyszczony.
Jeżeli baza danych jest taka, że jedyne co potrzeba zrobić w razie awarii, to odtworzyć dane do pojedynczego punktu w
czasie (do czasu tworzenia ostatniej kopii całkowitej lub różnicowej), to dobrze jest zastosować prostą rejestrację
(simple logging). Najmniejsza możliwa ilość informacji jest rejestrowana w dzienniku transakcji i dziennik utrzymuje
najmniejszy rozmiar. Występuje nadal rejestracja niektórych transakcji więc SQL Server może zachować wewnętrzną
spójność i obsługiwać obiekty takie jak wyzwalacze, używające dziennika transakcji.
Tworzenie narzędzia archiwizacyjnego
Pierwszym krokiem w przygotowaniu do wykonywania kopii baz danych jest utworzenie narzędzia archiwizacyjnego.
Pomimo tego, że nie ma technicznie potrzeby posiadania predeklarowanych narzędzi archiwizujących, upraszcza to
składnię poleceń archiwizujących, jak również pomaga administratorowi, który musi pracować w systemie po
zakończeniu pracy przez użytkowników.
Narzędzie archiwizujące jest to wskaźnik, w katalogu systemowym SQL Servera (tabela systemowa sysdevices w
bazie danych master), zawierający logiczną nazwę i fizyczną ścieżkę dostępu do pliku lokalnego dysku sztywnego,
lokalnego napędu taśmowego lub zdalnego pliku na innym komputerze. Kopie bezpieczeństwa nie mogą być
wykonywane przy pomocy zdalnych napędów taśmowych. Po określeniu polecenia BACKUP, należy odwołać się raczej
do logicznej nazwy niż określać pełną ścieżkę i nazwę pliku za każdym razem. Podejście to pomaga zredukować nakład
czasu na powtarzające się wpisywanie wszystkich informacji i jest mniej narażone na błędy.
Kontrolowanie urządzeń archiwizacyjnych przy pomocy
Transact-SQL
Można utworzyć urządzenie archiwizujące przy pomocy systemowej procedury składowej sp_addumpdevice:
sp_addumpdevice [@devtype =] 'device_type',
[@logicalname =] 'logical_name', [@physicalname =] 'physical_name'
3
[, {[@cntrltype =] cntrltype |
[@devstatus =] 'device_status'}]
Znaczenie składni:

device_type jest typem urządzenia, na które ma wskazywać urządzenie archiwizujące. Jest to jeden z
możliwych wariantów:
Disk
Tape
Pipe
wskaźnik do fizycznego pliku na lokalnym dysku twardym
wskaźnik do lokalnego napędu taśmowego
wskaźnik do Named Pipe

logical_name jest nazwą, jaka ma być używana dla kopii bezpieczeństwa przez polecenia odtwarzania, przy
odwoływaniu się do fizycznego położenia kopii.

physical_name jest fizyczną lokalizacją urządzenia archiwizacyjnego. Dla dysków, będzie to ścieżka i nazwa
pliku. Dla taśmy, będzie to wskaźnik do napędu taśmowego. Dla Named Pipe jest to adres sieciowy biblioteki
Named Pipe.

cntrltype jest przestarzałym parametrem obsługiwanym dla zachowania zgodności z wcześniejszymi
wersjami.

device_status określa czy nagłówki ANSI taśmy są ignorowane podczas zapisu do tego urządzenia
archiwizacyjnego. Jeżeli wartość parametru device_type zostanie określona jako Tape, SQL Server będzie
miał informację, że oznacza to NoSkip. Czyli jeśli taśma posiada informacje nagłówkowe o wcześniejszych
kopiach na taśmie, SQL Server nie ignoruje ich automatycznie i nie reinicjalizuje taśmy. Jeżeli takie działanie SQL
Servera (automatyczna reinicjalizacja taśmy nawet gdy zawiera dane) jest pożądane należy określić wartość:
Skip.
Aby usunąć urządzenie archiwizujące, należy uruchomić systemową procedurę składową:
sp_dropdevice [@logicalname =] 'logical_name', [@delfile =] 'delfile'
Składnia:

logical_name jest nazwą, jaka ma być używana w poleceniach tworzenia kopii i odtwarzania danych, przy
odwoływaniu się do fizycznej lokalizacji kopii bezpieczeństwa.

delfile — jeżeli parametr został użyty — określa fizyczny plik kopii, który powinien być skasowany (jeśli jest
na dysku).
Sprawdzanie spójności bazy danych
Jeżeli jest dostępne urządzenie archiwizujące, wszystko jest prawie przygotowane do utworzenia kopii bezpieczeństwa
baz danych. Może się zdarzyć, że zajdzie potrzeba przedsięwzięcia dodatkowych kroków przed rozpoczęciem
tworzenia kopii. Jak zostało wcześniej wspomniane, uszkodzona kopia bazy danych jest bezwartościowa. Dlatego,
należy sprawdzić, czy kopia jest poprawna. Microsoft dostarcza dwóch opcji do weryfikacji integralności bazy danych i
kopii bezpieczeństwa. Polecenie tworzenia kopii bezpieczeństwa posiada opcję do kontroli poprawności kopii
bezpieczeństwa po jej wykonaniu. Zbiór poleceń również dostarcza możliwości zweryfikowania integralności bazy
danych.
Teraz należy się skupić na możliwościach sprawdzania spójności jaka występuje w bazach danych SQL Servera. Jeżeli
baza danych jest w porządku przed wykonywaniem jej kopii, na ogół kopii bezpieczeństwa również jest poprawna. Aby
sprawdzić spójność bazy danych, należy skorzystać z rozszerzeń narzędzia Database Consistency Checker (DBCC).
Kilka różnych narzędzi DBCC sprawdza różne aspekty integralności bazy danych. Warto uruchomić polecenie DBCC
CHECKDB:
DBCC CHECKDB ('database_name' [, Noindex |
{ Repair_Allow_Data_Loss | Repair_Fast | Repair_Rebuild }])
[ With {[ALL_ERRORMSGS] | [NO_INFOMSGS],
[TABLOCK],[ESTIMATEONLY], [PHYSICAL_ONLY], [TABLERESULTS]] } ]
Znaczenie składni:

database_name jest nazwą bazy danych, dla której ma być sprawdzona integralność.

Noindex określa, że integralność nie klastrowanych indeksów nie ma być sprawdzana. (Indeksy zostaną
omówione w rozdziale 13).

Repair_Allow_Data_Loss określa wszystkie rodzaje napraw, jakie mają być przeprowadzone prze opcję
Repair_Rebuild. Opcja ta pozwala również „wyczyścić” uszkodzone strony i strony tekst/obraz bez względu
4
na to czy powoduje to utratę danych czy nie. Baza danych musi być w trybie pojedynczego użytkownika (singleuser) aby określić tę opcję.

Repair_Fast modyfikuje uszkodzone indeksy, jeżeli jest to bezpieczne (żadne dane nie zostaną utracone
używając tej opcji). Opcja ta wykonuje również szybkie i łatwe naprawy (takie jak uszkodzone klucze indeksu).
Baza danych musi być w trybie pojedynczego użytkownika, aby było możliwe określenie tej opcji.

Repair_Rebuild naprawia uszkodzone indeksy, podobnie jak opcja Repair_Fast ale są to bardziej
czasochłonne naprawy, takie jak ponowne utworzenie uszkodzonych indeksów. Baza danych musi być w trybie
pojedynczego użytkownika, aby było możliwe określenie tej opcji.

ALL_ERRORMSGS zwraca wszystkie komunikaty z polecenia. Domyślnie, SQL Server wstrzymuje zwracanie
komunikatów o błędach po otrzymaniu 200 komunikatów. Być może opcja ta nie będzie nigdy potrzebna.

NO_INFOMSGS określa, że będą zgłaszane jedynie znaczące komunikaty o błędach; nie będą natomiast
przedstawiane komunikaty informacyjne. Opcja ta jest na ogół używana przez większość czasu.

TABLOCK określa, że współdzielone blokady tabel powinny być używane w zamian za domyślne blokady stron.
Używanie tego parametru przyspiesza działanie poleceń DBCC jeśli system jest zajęty ale utrudnia pracę
konkurencyjnych użytkowników, którzy mogą być w tym przypadku blokowani przez dane polecenia DBCC.

ESTIMATEONLY oznacza, że użytkownik chce wiedzieć ile wolnej przestrzeni w bazie danych tempdb jest
potrzebne do uruchamiania polecenie bez uruchamiania CHECKDB.

PHYSICAL_ONLY oznacza, ze użytkownik chce sprawdzić jedynie fizyczną integralność bazy danych (a nie
logiczną integralność). Opcja ta działa szybciej i wyłapuje większość typowych uszkodzeń w SQL Serverze
(takich jak problemy z dyskiem, kontrolerem dysku i uszkodzonymi stronami).

TABLERESULTS określa, że rezultaty poleceń DBCC będą zwracane w formacie polecenia SELECT (jako zbiór
„wynikowy”). Opcja ta, nowa w SQL Serverze 2000, pozwala zachować wyniki tego polecenia (i innych poleceń
DBCC) w postaci tabeli, co powala uniknąć zagłębiania się w raporty w poszukiwaniu interesującego wyniku.
Teraz należy uruchomić następujący kod, aby sprawdzić spójność i integralność bazy danych:
DBCC CHECKDB ('pubs') With NO_INFOMSGS, TABLERESULTS
The command(s) completed successfully.
Jeżeli zostanie ominięta część polecenia With NO_INFOMSGS, ukaże się wiele wierszy (dwa dla każdej tabeli w
bazie danych).
Inne sposoby sprawdzenia integralności
Następne trzy polecenia DBCC CHECKCATALOG, DBCC CHECKALLOC i DBCC CHECKTABLE są podzbiorem
DBCC CHECKDB.
Polecenia te wymagają, aby użytkownik należał do roli serwera sysadmin lub roli baz danych db_owner. Te same
warunki stosują się do używania DBCC CHECKDB.
DBCC CHECKCATALOG Polecenie DBCC CHECKCATALOG sprawdza integralność referencyjną tabel
systemowych.
DBCC CHECKCATALOG ( 'database_name') [WITH NO_INOFMSGS]
W tej składni database_name oznacza nazwę bazy danych, której integralność na być sprawdzona.
Poniżej przedstawiono przykład użycia tego polecenia:
DBCC CHECKCATALOG ('Pubs')
DBCC CHECKALLOC DBCC CHECKALLOC sprawdza, czy informacje na temat alokacji (oznaczającej — jakie
rozszerzenia są własnością jakich tabel) w bazie danych są poprawne.
DBCC CHECKALLOC ('database_name' [, NOINDEX |
{ REPAIR_ALLOW_DATA_LOSS |REPAIR_FAST | REPAIR_REBUILD }] )
[WITH {[ALL_ERRORMSGS | NO_INFOMSGS], [TABLOCK] [,ESTIMATEONLY]
[, TABLERESULTS]}]
Wszystkie parametry w tej składni mają takie samo znaczenie jak w przypadku DBCC CHECKDB.
DBCC CHECKTABLE Polecenie DBCC CHECKTABLE wykonuje te same operacje co DBCC CHECKDB ale jest
ograniczone do pojedynczej tabeli. Jest to bardzo przydatne jeżeli ma zostać sprawdzona jedna duża tabela ale nie ma
potrzeby sprawdzania całej bazy danych.
5
DBCC CHECKTABLE ('table_name' [, NOINDEX | index_id
|{ REPAIR_ALLOW_DATA_LOSS |REPAIR_FAST | REPAIR_REBUILD }] )
[WITH {[ALL_ERRORMSGS | NO_INFOMSGS],
[TABLOCK] [,ESTIMATEONLY], [PHYSICAL_ONLY].[TABLERESULTS]}]
W przypadku tej składni wszystkie opcje są takie same jak dla DBCC CHECKDB z wyjątkiem dwóch:

table_name jest nazwą tabeli, której integralność ma zostać sprawdzona

ndex_id oznacza, że ma zostać sprawdzona integralność pojedynczego indeksu
Tworzenie kopii bezpieczeństwa bazy danych
Można stworzyć kopię bezpieczeństwa na pojedynczym nośniku archiwizacyjnym lub na wielu nośnikach; można także
umieścić wiele kopii bezpieczeństwa na pojedynczym nośniku archiwizacyjnym. Za każdym razem, odwołując się do
nośnika archiwizacyjnego, należy określić nazwę pliku zamiast polecenia BACKUP.
Kopie bezpieczeństwa na pojedynczym nośniku
archiwizującym (single-device)
Kopia na pojedynczym nośniku jest domyślnym i najczęstszym sposobem archiwizacji. Jeżeli każda baza danych jest
archiwizowana na odrębnym nośniku, można przeprowadzić strategię kopiowania jeden-do-jednego. Jeżeli kopia
bezpieczeństwa ma być na dyskach, podejście to jest zalecane. Jeżeli z pewnych przyczyn został utracony plik, została
w związku z tym utracona pojedyncza kopia bezpieczeństwa pojedynczej bazy danych a nie wiele kopii bezpieczeństwa
zawartych w pojedynczym pliku. Jeżeli kopia bezpieczeństwa jest na taśmie, tworzenie kopii wielu baz danych na
pojedynczej taśmie jest bardziej akceptowalne (i mniej kosztowne).
Kopie bezpieczeństwa równolegle rozproszone (parallel
striped)
Kopie bezpieczeństwa równolegle rozproszone pozwalają na tworzenie kopii pojedynczej bazy danych na wielu
nośnikach archiwizacyjnych. Wystarczy podać więcej niż jeden nośnik archiwizacyjny w poleceniu BACKUP. SQL
Server 2000 inicjuje wątek dla każdego wybranego nośnika w kopii równolegle rozproszonej. Istnieje możliwość
używania w tym przypadku do 24 nośników (oraz wynikających z tego wątków).
Silnym argumentem za używaniem kopii bezpieczeństwa równolegle rozproszonych są kopie na taśmach. Można
podłączyć wiele taśm w tym samym czasie. Jeżeli tworzenie kopii bazy danych zajmuje trzy godziny, a zakupi się dwa
dodatkowe napędy taśmowe (czyli w sumie trzy), można z dużym prawdopodobieństwem przyjąć, że tworzenie kopii
bezpieczeństwa zakończy się w godzinę. Istotną sprawą jest to, że nie potrzeba tej samej ilości napędów taśmowych do
odtworzenia bazy danych z kopii bezpieczeństwa. Jest to szczególnie istotne w przypadku odtwarzania po awarii. Nie
ma gwarancji, że posiada się taką samą ilość napędów taśmowych na serwerze odtwarzającym. Jednak, nadal potrzeba
posiadać wszystkie taśmy aby odtworzyć bazę danych.
Wiele kopii bezpieczeństwa na pojedynczym nośniku
Można również umieścić wiele kopii bezpieczeństwa na pojedynczym nośniku archiwizacyjnym. Jest to domyślna
konfiguracja w przypadku korzystania z taśm. Należy się spodziewać, ze użytkownik będzie chciał umieścić tak wiele
kopii bezpieczeństwa na pojedynczej taśmie jak to jest możliwe. Można również zastosować tę metodę w przypadku
dysków; jednak to podejście nie jest polecane dla dysków — jeżeli pojedynczy plik zostanie utracony lub ulegnie
uszkodzeniu, wszystkie kopie bezpieczeństwa na tym nośniku są stracone.
Polecenie BACKUP DATABASE dla całej bazy danych
Tworzenie bazy danych jest następnym logicznym krokiem (końcowym). Można utworzyć kopię bezpieczeństwa przy
pomocy polecenia BACKUP:
BACKUP DATABASE {database_name | @database_var}
TO backup_device [, ...n]
[WITH [BLOCKSIZE = {blocksize | @blocksize_variable}]
[[,] DESCRIPTION = {text | @text_variable}]
[[,] DIFFERENTIAL]
[[,] EXPIREDATE = {date | @date_var}
6
| RETAINDAYS = {days | @days_var}]
[[,] PASSWORD = {pwd | @pwd_var}
[[,] FORMAT | NOFORMAT]
[[,] {INIT | NOINIT}]
[[,] MEDIADESCRIPTION = {text | @text_variable}]
[[,] MEDIANAME = {media_name | @media_name_variable}]
[[,] MEDIAPASSWORD = {media_pwd | @media_pwd_variable}]
[[,] [NAME = {backup_set_name | @backup_set_name_var}]
[[,] {NOSKIP | SKIP}]
[[,] {NOUNLOAD | UNLOAD}]
[[,] {NOREWIND | REWIND}]
[[,] [RESTART]
[[,] STATS [= percentage]]
gdzie
backup_device :: =
{
{ backup_device_name | @backup_device_name_var}
|
{ DISK | TAPE | PIPE} =
{'temp_backpup_device' | @temp_backup_device_var}
}
Poniżej przedstawiono znaczenie poszczególnych opcji. Opisy nie są powtarzane, chyba że są specyficzne dla jednego z
poleceń, przedstawionych później. Może zachodzić potrzeba odwołania się do opisu opcji BACKUP LOG i BACKUP
FILE/FILEGROUP.

database_name jest nazwą bazy danych, której kopia ma zostać wykonana

@database_var jest nazwą bazy danych, której kopia ma być wykonana, z tym wyjątkiem, że jest
przedstawiona w postaci zmiennej.

backup_device jest albo nazwą nośnika archiwizacyjnego albo określeniem zmiennej dla urządzenia
archiwizacyjnego. Można również określić nazwę pliku, taśmy lub Named Pipe, które mają zostać wykorzystane
do tworzenia kopii bezpieczeństwa. Ponownie, można określić ten parametr jako zmienną.

blocksize określa rozmiar bloku, jaki ma być używany w przypadku taśmy lub Named Pipe. Należy sprawdzić
w dokumentacji Windows 2000 lub instrukcji obsługi napędu taśmowego, jakie rozmiary bloku są zalecane.

DESCRIPTION jest to opis kopii bezpieczeństwa, zawierający do 255 znaków. Ponownie, można określić
zmienną dla tego parametru.

DIFFERENTIAL zasługuje na własną sekcję dla pełnego opisu. Zobacz kolejną sekcję.

EXPIREDATE jest datą ważności taśmy (terminem, po którym zapis na taśmie powinna być usunięty). Nie można
dokonać zapisu na taśmę przed upływem terminu EXPIREDATE bez określenia parametrów nadpisywania.

RETAINDAYS przynosi ten sam efekt co EXPIREDATE, z wyjątkiem tego, że należy określić liczbę dni a nie
konkretną datę.

PASSWORD jest hasłem, jakie ma być wprowadzone aby było możliwe odtworzenie z tej kopii bezpieczeństwa.
Kopia bezpieczeństwa jest zabezpieczona jedynie hasłem. Nie jest w żaden sposób szyfrowana.

FORMAT | NOFORMAT pozwala żądać, aby taśma była ponownie sformatowana. Wszelkie zabezpieczenia
hasłem lub istniejące dane są ignorowane. Używanie tego parametru jest odpowiednikiem obydwu opcji INIT i
SKIP.

INIT | NOINIT określa, czy inicjalizować nośnik archiwizujący przed zapisem kopii bezpieczeństwa. NOINIT
jest opcją domyślną, oznaczającą, że jeżeli na danym nośniku archiwizującym jest inna kopia bezpieczeństwa,
bieżąca kopia zostanie dołączona (dopisana) na taśmę lub do pliku dyskowego. Jeżeli urządzenie nie zawiera
żadnej kopii, jest inicjalizowane a następnie kopia jest zapisywana. INIT powoduje nadpisanie istniejącej
zawartości taśmy lub pliku dyskowego. Dla taśm, jeżeli minęła ich data ważności lub parametr MEDIANAME nie
jest zgodny, taśma nie jest inicjalizowana. Należy określić opcję SKIP jeżeli to było zamierzeniem (jeżeli było to
zamierzeniem, można również skorzystać z opcji FORMAT).

MEDIADESCRIPTION jest innym polem opisu dla kopii bezpieczeństwa, o długości 255 znaków.

MEDIANAME jest opisem zbioru kopii bezpieczeństwa, o długości 128 znaków. Nazwa ta jest używana do
porównań w przypadku nadpisywania. Jeżeli planuje się używanie pojedynczej taśmy do przetrzymywania kopii
bezpieczeństwa systemu Windows 2000 i SQL Servera należy określić MEDIANAME.
7

MEDIAPASSWORD jest hasłem, jakie ma być używane przy tworzeniu kopii bezpieczeństwa jako część zestawu
nośników, jak również hasłem, jakie będzie wymagane przy odtwarzaniu z tego zestawu nośników.

NAME jest kolejną nazwą dla zestawu kopii bezpieczeństwa. Ponownie, parametr ten może być określony jako
zmienna.

NOSKIP | SKIP określa czy opuścić czytanie nagłówka taśmy podczas zapisywania na taśmę. Jeżeli została
wybrana opcja NOSKIP (domyślna), nagłówek taśmy jest czytany i sprawdzane są parametry EXPIREDATE i
MEDIANAME aby nie spowodować przypadkowego nadpisania taśmy.

NOUNLOAD | UNLOAD określa, czy taśma ma zostać wyjęta po zakończeniu tworzenia kopii bezpieczeństwa.

NOREWIND |REWIND określa czy taśma na zostać przewinięta po zakończeniu tworzenia kopii bezpieczeństwa.

RESTART pozwana na ponowne uruchomienie tworzenia kopii bezpieczeństwa jeżeli zostało przerwane i ma być
kontynuowane od punku przerwania. RESTART jest poprawny jedynie gdy tworzy się kopie bezpieczeństwa na
wielu taśmach.

STATS = percentage określa jak często użytkownik będzie powiadamiany o postępie w tworzeniu kopii
bezpieczeństwa. Domyślną wartością jest 10, oznaczające, że po ukończeniu każdego kolejnego 10 % tworzenia
kopii SQL Server zwróci komunikat określający postęp archiwizacji.
Polecenie BACKUP ma wiele opcji dlatego przyda się przykład. Czasem próbka kodu bardzo pomaga w wyjaśnieniu
znaczenia polecenia. Następujący skrypt tworzy nowy nośnik archiwizacyjny dla bazy danych pubs, sprawdza
integralność bazy a następnie tworzy kopię bezpieczeństwa tej bazy.
exec sp_addumpdevice 'disk', 'pubs_backup',
'd:\program files\Microsoft SQL Server\mssql\backup\pubs_backup.bak'
go
use pubs
dbcc checkdb ('pubs') With NO_INFOMSGS
BACKUP DTABASE pubs to pubs_backup WITH INIT
Polecenie DIFFERENTIAL BACKUP DATABASE
Jak zostało powiedziane wcześniej, kopia różnicowa kopiuje wszystkie zmodyfikowane strony z bazy danych — co
oznacza — wszystkie strony, które zmieniły się od czasu wykonania ostatniej pełnej kopii bazy danych. Jeżeli
wykonywana jest kopia różnicowa, a następnie kolejna kopia różnicowa, ta druga kopia różnicowa zawiera wszystkie
zmiany te same co pierwsza plus wszelkie zmienione strony, które zmieniły się od czasu wykonania pierwszej kopii.
Warto zauważyć, że kopia różnicowa ma sens jedynie jako kolejna po wykonaniu pełnej kopii bezpieczeństwa bazy
danych.
Teraz należy wykonać kopię różnicową bazy danych pubs. Po pierwsze, należy stworzyć tabelę i dodać do niej jeden
wiersz (czyli jest to coś nowego do archiwizowania). Nie będzie tworzone nowe urządzenie archiwizacyjnego, kopia
różnicowa zostanie dodana do urządzenia zawierającego kopię bezpieczeństwa bazy danych. Można określić parametr
NOINIT (jak w przykładzie) dla przejrzystości, nawet jeśli jest on domyślny.
use pubs
Create table backuptest (col1 int not null)
Insert backuptest values (1)
Go
BACKUP DATABASE pubs to pubs_backup WITH differential, NOINIT
Polecenie BACKUP LOG
Można utworzyć kopię jedynie dziennika transakcji bazy danych przy pomocy polecenia BACKUP LOG:
BACKUP LOG {database_name | @database_name_var}
{[WITH { NO_LOG | TRUNCATE_ONLY}]}
|
{TO backup_device [, ...n]
[WITH
[BLOCKSIZE = {blocksize | @blocksize_variable}]
[[,] DESCRIPTION = {text | @text_variable}]
[[,] EXPIREDATE = {date | @date_var}
| RETAINDAYS = {days | @days_var}]
[[,] PASSWORD = {pwd | @pwd_var}
[[,] FORMAT | NOFORMAT]
[[,] {INIT | NOINIT}]
[[,] MEDIADESCRIPTION = {text | @text_variable}]
8
[[,]
[[,]
[[,]
[[,]
[[,]
[[,]
[[,]
[[,]
[[,]
MEDIANAME = {media_name | @media_name_variable}]
MEDIAPASSWORD = {mediapwd | @mediapwd}]
[NAME = {backup_set_name | @backup_set_name_var}]
NO_TRUNCATE]
{NOSKIP | SKIP}]
{NOUNLOAD | UNLOAD}]
{NOREWIND | REWIND}]
[RESTART]
STATS [= percentage]]
W tej składni większość opcje zostało omówionych, z wyjątkiem następujących:

Parametry TRUNCATE_ONLY i NO_LOG są identyczne. Usuwają one wykonane (zakończone) transakcje z
dziennika ale nie robią kopii wpisów. Przykładowo, po wykonaniu kopii bezpieczeństwa bazy, w której były
wykonywane nie rejestrowane operacje, dziennik transakcji jest przydatny. Można uruchomić polecenie BACKUP
LOG WITH TRUNCATE_ONLY, a następnie utworzyć pełną kopię bezpieczeństwa bazy danych. Okrojenie
dziennika transakcji jest rejestrowaną operacją samą w sobie. Warto również zauważyć, że opcja ta wykonuje
faktycznie archiwizację, ale nazwa nośnika archiwizacyjnego jest nie potrzebna. Oczywiście, jeżeli chce się
wykonywać tę operację należy przełączyć bazę danych w tryb odzyskiwania SIMPLE.

NO_TRUNCATE wykonuje kopię bezpieczeństwa dziennika transakcji (tak jak polecenie BACKUP LOG bez
żadnych specjalnych opcji). Jednak, opcja ta wykonuje działanie, którego nie wykonuje zwykłe tworzenie kopii:
może utworzyć kopię dziennika transakcji nawet jeśli baza danych jest włączona (dostępna). Można przyjąć, że w
bazie danych jest plik danych na jednym fizycznym dysku i dziennik transakcji na osobnym dysku. Jeżeli plik
danych został z jakiegoś powodu uszkodzony, można uruchomić polecenie BACKUP LOG z opcją
NO_TRUNCATE aby zachować wszystkie transakcje, które wystąpiły od czasu tworzenia ostatniej kopii dziennika
transakcji. Tym sposobem, można odtworzyć bazę danych do punktu w czasie, w którym nastąpiło uszkodzenie
dysku.
Można utworzyć kopię dziennika transakcji bazy danych jedynie w przypadku, gdy wcześniej została wykonana pełna
kopia bazy danych. Nie można wykonać kopii dziennika transakcji jeżeli ustawione są opcje Truncate Log On
Checkpoint lub Select Into/Bulkcopy Database.
Kopie dziennika transakcji są sekwencjami kopii bezpieczeństwa. W przypadku tych kopii, podobnie jak w przypadku
kopii różnicowych, nie pojawia się żadne duplikowanie danych pomiędzy poszczególnymi kolejnymi kopiami
dziennika transakcji. Kiedy zachodzi konieczność odtwarzania dzienników transakcji, potrzeba do tego wszystkich
kopii utworzonych od czasu wykonania ostatniej pełnej kopii bazy danych. Kolejny rozdział prezentuje szczegóły
odtwarzania bazy danych przy pomocy dzienników transakcji.
Przebieg ćwiczenia.
Uwaga: wszystkie kody wykonywane w programie Query Analizer zapisywać do plików, gdyż
ułatwi to sporządzanie sprawozdania.
1. Sprawdzenie i zmiana modelu odtwarzania bazy danych:
Aby zobaczyć jaki model odtwarzania danej bazy jest uruchomiony należy wpisać kod:
Select DATABASEPROPERTYEX ('mydb', 'recovery')
W odpowiedzi system określi nazwę używanego modelu: FULL, BULK_LOGGED lub SIMPLE.
Aby zmienić model odtwarzania, należy skorzystać z polecenia ALTER DATABASE. Można
uruchomić następujące polecenie aby zmienić model bazy danych pubs na wartość FULL:
Alter database pubs SET RECOVERY FULL
- wykorzystując Query Analizer sprawdzić model odtwarzania dla baz
master,model, tempdb i Northwind
- wykorzystując Query Analizer zmienić model odtwarzania bazy Northwind
na Full
- wykorzystując Enterprise Manager ustawić ponownie model odtwarzania
dla bazy Northwind na Simple
1. Kontrolowanie urządzeń archiwizacyjnych:
- wykorzystując program Enterprise Manager dodać następujące urządzenia
archiwizacyjne (Backup Devices )
9
Nazwa logiczna urządzenia
kopiowania danych
NazwiskoSt_1
NazwiskoSt_1log
Nazwa pliku na dysku
C:\Backup\ NazwiskoSt_1.bak
C:\Backup\ NazwiskoSt_1log.bak
W programie Enterprise Manager z menu kontekstowego dla serwera SQL rozszerzyć
katalog „Management”, wybrać podkatalog „Backup”. Kliknąć prawą myszą, wybrać tryb
„New Backup Device”. W oknie „Backup Device Properties – New Device” uzupełnić
pola dla każdego Backup Devices. Przekonać się że stworzone Backup Devices są
widoczne w podkatalogu „Backup”, odczytać właściwości nowych Backup Devices
- wykorzystując procedurę sp_addumpdevice stworzyć dwa urządzenia
archiwizacyjne (Backup Devices ) z następującymi parametrami:
Nazwa logiczna urządzenia
kopiowania danych
NazwiskoSt_2
NazwiskoSt_3
Nazwa pliku na dysku
C:\Backup\ NazwiskoSt_2.bak
C:\Backup\ NazwiskoSt_3.bak
-wykorzystując procedurę sp_helpdevice sprawdzić istniejące urządzenia
archiwizacyjne i ich listę umieścić w sprawozdaniu
3. Sprawdzanie spojności bazy danych: wykorzystując narzędzie Database
Consistency Checker (DBCC) sprawdzić różne aspekty integralności bazy
danych
W tym punkcie będą wykorzystane procedury systemowe, instrukcji Transact_SQL w
programie Query Analyzer dla odczytania listy Backup Devices, sprawdzenia
poprawności oraz integralności bazy danych przed kopiowaniem do Backup Devices.
Kompletna poprawność zawiera sprawdzenie poprawności indeksów, integralności
odwołań, alokacji tabel.
- uruchomić Query Analyzer;
- wykorzystując instrukcję DBCC CHECKDB sprawdzić całkowitą
poprawność bazy danych ”Northwind” w trybach:
a) formowania wszystkich komunikatów diagnostycznych dla obiektów bazy
danych
b) bez szczególnych komunikatów
-Wykorzystując instrukcję DBCC CHECKCATALOG sprawdzić
integralność tabel systemowych bazy danych „Pubs”
-Wykorzystując instrukcję DBCC CHECKALLOC sprawdzić poprawność
alokacji tabel bazy danych „Pubs”
- Wykorzystując instrukcję DBCC CHECKTABLE sprawdzić poprawność
jednej z tabel użytkownika bazy danych „Northwind”
- zachować wyniki i umieścić w sprawozdaniu
4. Realizacja kopiowania bazy danych z wykorzystaniem Enterprise Manager.
10
W tym punkcie baza danych Northwind będzie skopiowana do wcześniej stworzonego w
p.1 urządzenia „NazwiskoSt_1”.
- wykorzystując program Enterprise Manager odtworzyć listę właściwości
bazy danych Northwind, wybrać zakładkę „Option”. Ustalić pole „Read only
= False”, oraz „Model = Full”.
- w programie Enterprise Manager z menu kontekstowego dla serwera SQL
rozszerzyć katalog „Management” oraz wybrać podkatalog „Backup”.
Kliknąć prawą myszą oraz wybrać tryb „Backup a Database”.
W oknie „SQL Server Backup” ustalić następujące wartości pól:
Pole
Database
Name
Description
Backup type
Destination
Overwrite
Wartość
Northwind
NorthwindFull
Pierwsza kopia bazy Northwind
Database-complete
NazwiskoSt_1
Overwrite existing media
- zapisać czas stworzenia kopii, kliknąć OK., sprawdzić czy stworzona kopia
znajduje się w Backup Device o nazwie „NazwiskoSt_1”.
5. Dodawanie nowej kopii bazy danych do stworzonego wcześniej urządzenia
archiwizacyjnego (Backup Device) z wykorzystanieminstrukcji TransactSQL.
W tym punkcie do urządzenia archiwizacyjnego (Backup Device) „NazwiskoSt_1” będzie
dodana jeszcze jedna kopia bazy danych Northwind z wykorzystaniem instrukcji SQL w
programie Query Analyzer.
- wykorzystując instrukcję BACKUP DATABASE w Query Analyzer dodać
nową kopię bazy danych Northwind do urządzenia „NazwiskoSt_1” .
Zanotować czas tworzenia drugiej kopii. Opcje instrukcji BACKUP
DATABASE ustalić jak zamieszczono poniżej:
Opcja
Database name
Backup file(logical device)
Append,overwrite,or initialize
Description
Wartość
Northwind
NazwiskoSt_1
Append (NOINIT)
Druga kopia bazy Northwind
- uruchomić program Enterprise Manager, przekonać się że druga stworzona
kopia znajduje się w Backup Device „NazwiskoSt_1” razem z kopią
pierwszą.
6. Tworzenie nowej kopii bazy danych, która będzie nadpisywać (overwrite) w
urządzeniu archiwizacyjnym(Backup Device) wszystkie kopie poprzednie.
W tym punkcie wykorzystując instrukcje SQL w programie Query Analyzer będzie
tworzona nowa kopia bazy danych Northwind w Backup Device „NazwiskoSt_1”, która
zastąpi kopie poprzednie.
11
- wykorzystując instrukcję BACKUP DATABASE stworzyć nową kopię
bazy danych Northwind w Backup Device „NazwiskoSt_1”, która zastępuje
utworzone kopie w p.3. Opcje instrukcji BACKUP DATABASE ustalić jak
podano w tabeli:
Opcja
Database name
Backup file(logical device)
Append,overwrite,or initialize
Description
Wartość
Northwind
NazwiskoSt_1
Owerwrite (INIT)
Trzecia kopia bazy Northwind zastępuje inne
- zapisać czas stworzenia trzeciej kopii, ruchomić program Enterprise
Manager i sprawdzić czy trzecia kopia została zastąpiona w Backup Device
„NazwiskoSt_1” kopią pierwszą oraz drugą.
7. Równoległe tworzenie rozproszonych kopii w dwóch różnych urządzeniach
archiwizacyjnych(Backup Device).
Kopiowanie rzeczywistych baz danych może trwać wiele godzin, np. kiedy jako
urządzeniearchiwizujące (Backup Device) wykorzystuje się taśmy magnetyczne.Dla
przyspieszenia tego procesu można zastosować równoległe kopiowanie bazy danych na
różne urządzenia archiwizacyjne(Backup Device). W tym punkcie wykorzystując program
Enterprise Manager będą tworzone dwie rozproszone kopie na Backup Devices
NazwiskoSt_2 oraz NazwiskoSt_3 stworzonych w p.2.
- uruchomić program Enterprise Manager, rozszerzyć katalog „Management”
oraz wybrać podkatalog „Backup”, kliknąć prawym przyciskiem myszy oraz
wybrać tryb „Backup a Database”. W oknie „SQL Server Backup” ustawić
następujące parametry:
Pole
Database
Name
Description
Backup type
Destination
Overwrite
Wartość
Northwind
Northwind striped
Paralelne kopiowanie bazy Northwind
Database-complete
NazwiskoSt_2 and NazwiskoSt_3
Overwrite existing media
- zanotować czas stworzenia kopii, kliknąć OK., upewnić się że stworzone
kopie znajdują się w Backup Device „NazwiskoSt_2” oraz w Backup Device
„NazwiskoSt_3”
8. Tworzenie kopii bazy danych na tymczasowym (temporary) urządzeniu
Backup Device.
Temporary Backup Device nie jest widoczne w katalogu Backup serwera SQL i może być
stworzone przy bezpośrednim kopiowaniu bazy danych. Temporary Backup Device
tworzone jest przez administratora BD jednorazowo dla tego niema sensu zapamiętywać
go na serwerze. W tym punkcie będzie stworzone Temporary Backup Device z
12
wykorzystaniem programu Enterprise Manager.
- uruchomić program Enterprise Manager, rozszerzyć katalog
„Management” oraz wybrać podkatalog „Backup”, kliknąć prawym
przyciskiem myszy oraz wybrać tryb „Backup a Database”, w oknie „SQL
Server Backup” ustalić następujące parametry:
Pole
Database
Name
Description
Backup type
Destination
Overwrite
Wartość
Northwind
Northwind temporary
Northwind temporary beckup device nie
odwzorowuje się w tabelach systemowych
Database-complete
C:\BACKUP\MyNewBackup.bak
Overwrite existing media
- zanotować czas stworzenia kopii, kliknąć OK., upewnić się że stworzona
kopia znajduje się w Temporary Backup Device
C:\BACKUP\MyNewBackup.bak .
9. Kopiowanie dziennika transakcji (transaction log) z wykorzystaniem
programu Enterprise Manager.
W typ punkcie będzie skopiowany dziennik transakcji bazy danych Northwind do Backup
Device
NazwiskoSt_1log
które
zostało
stworzone
w
p.1.
- uruchomić program Enterprise Manager, rozszerzyć katalog Databases,
odtworzyć okno Properties bazy Northwind, w zakładce „Option” ustalić
wartość w polu „Model” na tryb „Full”, kliknąć OK
(dzięki tej operacji mamy możliwość kopiowania dziennika transakcji bazy danych,
ponieważ w trybie „Simple” kopiowanie dziennika transakcji jest niemożliwe)
- rozszerzyć katalog „Management” oraz wybrać podkatalog „Backup”,
kliknąć prawym przyciskiem myszy oraz wybrać tryb „Backup a Database”,
w oknie „SQL Server Backup” ustawić następujące parametry:
Pole
Database
Name
Description
Backup type
Destination
Overwrite
Wartość
Northwind
Northwind Log
Northwind transaction Log
Transaction Log
NazwiskoSt_1log
Overwrite existing media
- zanotować czas tworzenia kopii, kliknąć OK, upewnić się że stworzona
kopia znajduje się w Backup Device NazwiskoSt_1log.
13
10.Dodanie nowego plik „transaction log” do urządzenia archiwizacyjnego
NazwiskoSt_1log z wykorzystaniem kodu SQL w programie Query
Analyzer.
- uruchomić Query Analyzer, wykorzystując instrukcję BACKUP LOG
dodać nową kopię pliku „transaction log” do Backup Device
NazwiskoSt_1log bazy danych Northwind, zanotować czas tworzenia nowej
kopii, opcje instrukcji BACKUP LOG ustalić następująco:
Opcja
Database name
Backup file(logical device)
Append,overwrite,or initialize
Wartość
Northwind
NazwiskoSt_1log
Append (NOINIT)
- uruchomić program Enterprise Manager, upewnić się, że stworzona kopia
transaction log znajduje się w Backup Device „NazwiskoSt_1log”
11.Tworzona nowej kopii bazy danych, następnie oczyszczenie dziennika
transakcji.
Ta operacja zrealizuje się po utworzeniu kopii bazy danych w Backup Device i pozwoli
na usunięcie nieaktywnej część rekordów dziennika transakcji.
- uruchomić Query Analyzer, wykorzystując instrukcje BACKUP
DATABASE z opcją WITH NOINIT stworzyć nową kopię bazy danych
Northwind w Backup Device „NazwiskoSt_1”
- zanotować czas tworzenia kopii, upewnić się że stworzona kopia znajduje
się w Backup Device „NazwiskoSt_1”.
- uruchomić Query Analyzer, wykorzystując instrukcję BACKUP LOG (z
opcją WITH TRUNCATE_ONLY) oczyścić dziennik transakcji bazy danych
Northwind
(instrukcja ta nie potrzebuje wyznaczenia Backup Device)
12.Tworzone kopii róznicowych „differential backup”, które zachowają zmiany
od momentu ostatniego stworzenia kopii „Full backup”.
- uruchomić program Enterprise Manager, rozszerzyć katalog
„Management” oraz wybrać podkatalog „Backup”, kliknąć prawym
przyciskiem myszy oraz wybrać tryb „Backup a Database”, w oknie „SQL
Server Backup” ustalić następujące parametry:
Pole
Database
Name
Description
Backup type
Destination
Overwrite
Wartość
Northwind
Northwind differential
Zmiany po ostatnim kopiowaniu Full
Database-differential
C:\BACKUP\Nwdiff.bak
Append to media
14
- zapisać czas stworzenia kopii, kliknąć OK, upewnić się że stworzona kopia
znajduje się w Backup Device C:\BACKUP\ Nwdiff.bak
Treść sprawozdania.
1. Odnośnie do każdego punktu ćwiczenia umieścić kod Transact-SQl oraz
wyniki .
2. Do kilku wybranych punktów zademonstrować własne przykłady
Przykładowe pytania kontrolne.
1. Jakie role użytkowników mają uprawnienia do realizacji operacji kopiowania
(Backing) w SQL serwerze?
2. Jakie istnieją typy kopiowania (Backing) w SQL serwerze?
3. Jakie istnieją modele odtwarzania(Using Recowery Models) w SQL serwerze?
4. Dlaczego jest przeznaczony dziennik transakcji?
5. Jaka definicja pojęcia „Backup Device”?
15