Tworzenie kopii zapasowych i przywracanie baz danych SQL Server

Dotyczy:SQL Server

Ten artykuł opisuje zalety tworzenia kopii zapasowych baz danych SQL Server, wprowadza podstawowe terminy dotyczące kopii zapasowych i przywracania oraz omawia strategie tworzenia kopii zapasowych i przywracania oraz kwestie bezpieczeństwa SQL Server.

Uwaga / Notatka

W tym artykule przedstawiono kopie zapasowe programu SQL Server. Aby uzyskać szczegółowe instrukcje tworzenia kopii zapasowych baz danych programu SQL Server, zobacz Tworzenie kopii zapasowych.

Komponent SQL Server do tworzenia kopii zapasowych i przywracania zapewnia niezbędną ochronę dla krytycznych danych przechowywanych w bazach danych SQL Server. Aby zminimalizować ryzyko katastrofalnej utraty danych, regularnie dokonuj kopii zapasowych baz danych, aby zachować modyfikacje danych. Dobrze zaplanowana strategia kopii zapasowej i przywracania pomaga chronić bazy danych przed utratą danych spowodowaną wieloma rodzajami awarii. Sprawdź swoją strategię, przywracając zestaw kopii zapasowych, a następnie odzyskując bazę danych, aby być gotowym na reakcję na katastrofę.

Oprócz lokalnej pamięci, SQL Server obsługuje także kopie zapasowe i przywracanie z Azure Blob Storage. Więcej informacji można znaleźć w artykule SQL Server backup and restore with Azure Blob Storage. W przypadku plików baz danych przechowywanych przy użyciu usługi Azure Blob Storage program SQL Server 2016 (13.x) udostępnia opcję używania migawek platformy Azure do niemal natychmiastowego tworzenia kopii zapasowych i szybszych przywracania. Aby uzyskać więcej informacji, zobacz Kopie zapasowe migawek plików dla plików baz danych w usłudze Azure. Platforma Azure oferuje również rozwiązanie do tworzenia kopii zapasowych klasy korporacyjnej dla programu SQL Server działającego na maszynach wirtualnych platformy Azure. To w pełni zarządzane rozwiązanie do tworzenia kopii zapasowych obsługuje grupy dostępności Always On, długoterminowe przechowywanie, odtwarzanie do określonego punktu w czasie oraz centralne zarządzanie i monitorowanie. Aby uzyskać więcej informacji, zobacz About SQL Server backup on Azure VMs (Informacje o kopii zapasowej programu SQL Server na maszynach wirtualnych platformy Azure).

Dlaczego warto utworzyć kopię zapasową?

Tworzenie kopii zapasowych baz danych SQL Server, przeprowadzanie testowych procedur odtwarzania z kopii zapasowych oraz przechowywanie kopii zapasowych w bezpiecznej lokalizacji poza siedzibą firmy chronią przed potencjalnie katastrofalną utratą danych. Tworzenie kopii zapasowej jest jedynym sposobem ochrony danych.

W przypadku prawidłowych kopii zapasowych bazy danych można odzyskać dane z wielu błędów, takich jak:
- Błąd nośnika.
- Błędy użytkownika, na przykład porzucanie tabeli przez pomyłkę.
- Awarie sprzętowe, na przykład uszkodzony dysk lub trwała utrata serwera.
- Klęski żywiołowe. Korzystając z SQL Server Backup do Azure Blob Storage, możesz stworzyć kopię zapasową poza lokalizacją lokalną, aby użyć jej w przypadku katastrofy naturalnej, która wpłynie na Twoją lokalizację lokalną.
Ponadto kopie zapasowe bazy danych są przydatne w rutynowych celach administracyjnych, takich jak kopiowanie bazy danych z jednego serwera do innego, konfigurowanie zawsze włączonych grup dostępności lub dublowanie bazy danych i archiwizowanie.

Słownik terminów kopii zapasowych

Term	Definition
utworzyć kopię zapasową[verb]	Proces tworzenia kopii zapasowej[noun] poprzez kopiowanie rekordów danych z bazy danych SQL Server lub rejestrów logów z dziennika transakcyjnego.
kopia zapasowa[rzeczownik]	Kopia danych, których można użyć do przywrócenia i odzyskania danych po awarii. Kopie zapasowe bazy danych mogą również służyć do przywracania kopii bazy danych do nowej lokalizacji.
Urządzenie zapasowe	Dysk lub urządzenie taśmowe, na którym są zapisywane kopie zapasowe programu SQL Server i z których można je przywrócić. Kopie zapasowe programu SQL Server można również zapisywać w usłudze Azure Blob Storage, a format adresu URL służy do określania miejsca docelowego i nazwy pliku kopii zapasowej. Więcej informacji można znaleźć w artykule SQL Server backup and restore with Azure Blob Storage.
nośnik kopii zapasowej	Co najmniej jedna taśma lub pliki dysku, do których zapisano co najmniej jedną kopię zapasową.
kopia zapasowa danych	Kopia zapasowa danych w pełnej bazie danych (kopia zapasowa bazy danych), częściowa baza danych (częściowa kopia zapasowa) lub zestaw plików lub grup danych (kopia zapasowa pliku).
kopia zapasowa bazy danych	Kopia zapasowa bazy danych. Pełne kopie zapasowe bazy danych reprezentują całą bazę danych w momencie zakończenia tworzenia kopii zapasowej. Różnicowe kopie zapasowe baz danych zawierają tylko zmiany wprowadzone w bazie danych od czasu utworzenia najnowszej pełnej kopii zapasowej bazy danych.
różnicowa kopia zapasowa	Kopia zapasowa danych oparta na najnowszej pełnej kopii zapasowej kompletnej lub częściowej bazy danych lub zestawu plików lub grup plików (bazy różnicowej) zawierającej tylko dane, które uległy zmianie od tej bazy.
pełna kopia zapasowa	Kopia zapasowa danych zawierająca wszystkie dane w określonej bazie danych lub zestawie grup plików lub plików, a także wystarczająca ilość dziennika, aby umożliwić odzyskanie tych danych.
kopia zapasowa dziennika	Kopia zapasowa dzienników transakcji zawierająca wszystkie rekordy dziennika, których kopia zapasowa nie została utworzona w poprzedniej kopii zapasowej dziennika (model pełnego odzyskiwania).
recover	Aby przywrócić bazę danych do stabilnego i spójnego stanu.
Odzyskiwanie	Faza uruchamiania bazy danych lub odtwarzania z odzyskiwaniem, która doprowadza bazę danych do stanu spójnego pod względem transakcji.
model przywracania	Właściwość bazy danych, która kontroluje konserwację dziennika transakcji w bazie danych. Istnieją trzy modele odzyskiwania: podstawowy, pełny i model rejestrowania zbiorczego. Model odzyskiwania bazy danych określa wymagania dotyczące tworzenia kopii zapasowych i przywracania.
przywrócić	Wielofazowy proces, który kopiuje wszystkie strony danych i dzienników z określonej kopii zapasowej programu SQL Server do określonej bazy danych, a następnie przekazuje wszystkie transakcje, które są rejestrowane w kopii zapasowej, stosując zarejestrowane zmiany w celu przeniesienia danych do przodu w czasie.

Strategie tworzenia kopii zapasowych i przywracania

Musisz dostosować strategie tworzenia kopii zapasowych i przywracania do swojego środowiska i dostępnych zasobów. Niezawodna odbudowa wymaga strategii tworzenia kopii zapasowej i przywracania. Dobrze zaprojektowana strategia równoważy wymagania biznesowe dotyczące maksymalnej dostępności danych i minimalnej utraty danych z kosztami utrzymania i przechowywania kopii zapasowych.

Strategia tworzenia kopii zapasowych i przywracania zawiera część kopii zapasowej i część przywracania. Część kopii zapasowej określa rodzaj i częstotliwość kopii zapasowych, typ i szybkość wymaganego sprzętu, sposób testowania kopii zapasowych oraz miejsce i sposób przechowywania nośników zapasowych (w tym względy bezpieczeństwa). Część przywracająca definiuje, kto odpowiada za wykonywanie przywracań, jak je wykonać, aby osiągnąć cele dotyczące dostępności bazy danych i minimalnej utraty danych, oraz jak testować przywracania.

Skuteczna strategia tworzenia kopii zapasowej i przywracania wymaga starannego planowania, wdrożenia i testowania. Wymagane są testy. Nie masz strategii tworzenia kopii zapasowej, dopóki nie przywrócisz kopii zapasowych we wszystkich kombinacjach zawartych w strategii przywracania i nie przetestujesz każdej przywróconej bazy danych pod kątem fizycznej spójności. Weź pod uwagę kilka czynników, w tym:

Cele organizacji dotyczące produkcyjnych baz danych, szczególnie wymagania dotyczące dostępności i ochrony danych przed utratą lub uszkodzeniem.
Charakter każdej bazy danych: jego rozmiar, wzorce użycia, charakter jego zawartości, wymagania dotyczące danych itd.
Ograniczenia dotyczące zasobów, takich jak sprzęt, personel, miejsce na przechowywanie nośników kopii zapasowych, fizyczne zabezpieczenia przechowywanych multimediów itd.

Zalecenia dotyczące najlepszych rozwiązań

Nie przyznawaj kontom wykonującym operacje backupu lub przywracania więcej uprawnień niż to konieczne. Więcej informacji znajdziesz w tematach tworzenie kopii zapasowej oraz przywracanie, aby uzyskać szczegółowe informacje o uprawnieniach. Zaszyfruj kopie zapasowe bazy danych i, jeśli to możliwe, skompresuj je.

Używaj jednolitych rozszerzeń plików, aby ułatwić identyfikowanie kopii zapasowych i zarządzanie nimi. SQL Server nie wymaga ani nie egzekwuje tych rozszerzeń, ale spójność pomaga w zadaniach operacyjnych, takich jak konfigurowanie wykluczeń antywirusowych dla plików kopii zapasowych. Aby uzyskać więcej informacji, zobacz Konfigurowanie oprogramowania antywirusowego do pracy z programem SQL Server.

Pliki kopii zapasowej bazy danych powinny mieć to .BAK rozszerzenie.
Pliki kopii zapasowej dziennika powinny mieć .TRN rozszerzenie .

Używaj osobnej pamięci masowej

Umieść kopie zapasowe bazy danych w osobnym fizycznym miejscu lub na innym urządzeniu niż pliki bazy danych. Gdy fizyczny dysk, który przechowuje bazę danych, zawodzi lub się zawiesza, odzyskiwanie zależy od możliwości dostępu do osobnego dysku lub zdalnego urządzenia, które przechowywało kopie zapasowe. Możesz utworzyć kilka logicznych woluminów lub partycji z tego samego fizycznego napędu dyskowego. Dokładnie przejrzyj partycje dysku i układ woluminów logicznych, zanim wybierzesz miejsce przechowywania kopii zapasowych.

Wybieranie odpowiedniego modelu odzyskiwania

Operacje tworzenia kopii zapasowych i przywracania są wykonywane w kontekście modelu odzyskiwania. Model odzyskiwania to właściwość bazy danych, która kontroluje sposób zarządzania dziennikiem transakcji. Dlatego model odzyskiwania bazy danych określa, jakie typy scenariuszy kopii zapasowych i przywracania obsługuje baza oraz rozmiar kopii zapasowych logów transakcyjnych. Zazwyczaj baza danych używa prostego modelu odzyskiwania lub pełnego modelu odzyskiwania. Pełny model odzyskiwania można rozszerzyć, przełączając się na model odzyskiwania rejestrowanego zbiorczo przed operacjami zbiorczych. Aby zapoznać się z tymi modelami odzyskiwania i ich wpływem na zarządzanie dziennikiem transakcji, zobacz dziennik transakcji.

Najlepszy wybór modelu odzyskiwania bazy danych zależy od wymagań Twojej firmy. Aby uniknąć zarządzania dziennikami transakcji i uprościć tworzenie kopii zapasowych i przywracanie, użyj prostego modelu odzyskiwania. Aby zminimalizować narażenie na straty pracy kosztem obciążeń administracyjnych, należy użyć pełnego modelu odzyskiwania. Aby zminimalizować wpływ na rozmiar dziennika transakcji podczas operacji wykonywanych w trybie rejestrowania zbiorczego, a jednocześnie umożliwić odzyskiwanie po takich operacjach, użyj modelu odzyskiwania z rejestrowaniem zbiorczym. Informacje o wpływie modeli odzyskiwania na kopie zapasowe i przywracanie można znaleźć w artykule Przegląd kopii zapasowych (SQL Server).

Projektowanie strategii tworzenia kopii zapasowych

Po wybraniu modelu odzyskiwania spełniającego wymagania Twojej firmy dla konkretnej bazy danych, zaplanuj i wdroż dopasowaną strategię kopii zapasowej. Najlepsza strategia kopii zapasowej zależy od kilku czynników. Szczególnie ważne są następujące czynniki:

Ile godzin dziennie aplikacje muszą mieć dostęp do bazy danych?

Jeśli jest przewidywalny okres poza szczytem, powinieneś zaplanować pełne kopie zapasowe bazy danych na ten okres.
Jak często występują zmiany i aktualizacje?

Jeśli zmiany są częste, rozważ:
- W modelu prostego odzyskiwania można zaplanować różnicowe kopie zapasowe między pełnymi kopiami zapasowymi bazy danych. Różnicowa kopia zapasowa przechwytuje tylko zmiany od czasu utworzenia ostatniej pełnej kopii zapasowej bazy danych.
- W modelu pełnego odzyskiwania możesz planować częste kopie zapasowe logów. Planowanie różnicowych kopii zapasowych między pełnymi kopiami zapasowymi może skrócić czas przywracania, zmniejszając liczbę kopii zapasowych dziennika, które należy przywrócić po przywróceniu danych.
Czy zmiany mogą wystąpić tylko w małej części bazy danych, czy raczej w dużej części?

W przypadku dużej bazy danych, w której zmiany koncentrowane są w podzbiorze plików lub grup plików, przydatne mogą być częściowe lub pełne kopie zapasowe plików. Więcej informacji można znaleźć w artykule Częściowe kopie zapasowe (SQL Server) oraz Pełne kopie zapasowe plików (SQL Server).
Ile miejsca na dysku wymaga pełna kopia zapasowa bazy danych?
Jak długo wstecz Twoja firma musi przechowywać kopie zapasowe?

Upewnij się, że masz odpowiedni harmonogram kopii zapasowych, który odpowiada potrzebom aplikacji i wymaganiom biznesowym. Wraz ze starzeniem się kopii zapasowych rośnie ryzyko utraty danych, chyba że masz sposób na odtworzenie wszystkich danych aż do momentu awarii. Zanim pozbędziesz się starych kopii zapasowych ze względu na ograniczenia pamięci, zastanów się, czy potrzebujesz odzyskiwania tak daleko w przeszłości.

Szacowanie rozmiaru pełnej kopii zapasowej bazy danych

Zanim wdrożysz strategię tworzenia kopii zapasowych i ich przywracania, oszacuj, ile miejsca na dysku zajmuje pełna kopia zapasowa bazy danych. Operacja tworzenia kopii zapasowej kopiuje dane w bazie danych do pliku kopii zapasowej. Kopia zapasowa zawiera tylko rzeczywiste dane z bazy dat, a nie niewykorzystaną przestrzeń. W związku z tym kopia zapasowa jest zwykle mniejsza niż sama baza danych. Aby oszacować rozmiar pełnej kopii zapasowej bazy danych, użyj procedury sp_spaceused systemowej przechowywanej. Aby uzyskać więcej informacji, zobacz sp_spaceused.

Planowanie kopii zapasowych

Operacja kopii zapasowej ma minimalny wpływ na bieżące transakcje, więc możesz je robić podczas regularnych operacji. Możesz wykonać kopię zapasową programu SQL Server z minimalnym wpływem na obciążenia produkcyjne.

Uwaga / Notatka

Aby uzyskać informacje o ograniczeniach współbieżności podczas tworzenia kopii zapasowej, zobacz Omówienie kopii zapasowej (SQL Server).

Po podjęciu decyzji, jakie rodzaje kopii zapasowych potrzebujesz i jak często wykonywać każdy typ, zaplanuj regularne kopie zapasowe jako część planu utrzymania bazy danych. Aby uzyskać informacje o planach konserwacji i sposobie ich tworzenia dla kopii zapasowych bazy danych i kopii zapasowych dzienników, zobacz Korzystanie z Kreatora planu konserwacji.

Testowanie kopii zapasowych

Nie masz strategii przywracania, dopóki nie przetestujesz kopii zapasowych. Dokładnie przetestuj strategię tworzenia kopii zapasowej dla każdej bazy danych, przywracając jej kopię do systemu testowego. Należy przetestować przywracanie każdego typu kopii zapasowej, która ma być używana. Po przywróceniu kopii zapasowej uruchom DBCC CHECKDB bazę danych, aby potwierdzić, że nośnik kopii zapasowej nie został uszkodzony.

Weryfikowanie stabilności i spójności multimediów

Korzystaj z opcji weryfikacji dostępnych w narzędziach do tworzenia kopii zapasowych (BACKUPpolecenie T-SQL, plany utrzymania SQL Server, oprogramowanie lub rozwiązanie do backupu itd.). Dla przykładu, zobacz RESTORE Twierdzenia - VERIFYONLY.

Korzystaj z zaawansowanych funkcji, BACKUP CHECKSUM takich jak wykrywanie problemów z samym nośnikiem zapasowym. Aby uzyskać więcej informacji, zobacz Possible Media Errors During Backup and Restore (SQL Server).

Strategia tworzenia/przywracania kopii zapasowych dokumentów

Dokumentuj procedury tworzenia kopii zapasowych i przywracania oraz przechowuj kopię ich dokumentacji w swoim runbooku.

Powinieneś także prowadzić podręcznik operacyjny dla każdej bazy danych. Ten podręcznik operacyjny powinien dokumentować lokalizację kopii zapasowych, nazwy urządzeń do backupu (jeśli występują) oraz czas potrzebny na przywrócenie testowych kopii zapasowych.

Ryzyko związane z bezpieczeństwem przywracania kopii zapasowych z niezaufanych źródeł

W tej sekcji opisano ryzyko bezpieczeństwa związane z przywracaniem kopii zapasowych z niezaufanych źródeł do dowolnego środowiska programu SQL Server, w tym lokalnego, usługi Azure SQL Managed Instance, programu SQL Server na maszynach wirtualnych platformy Azure i innych środowiskach.

Dlaczego to ma znaczenie

Przywracanie plików kopii zapasowej SQL (.bak) powoduje potencjalne ryzyko, jeśli kopia zapasowa pochodzi z niezaufanego źródła. Ryzyko bezpieczeństwa jest jeszcze bardziej zaostrzone, gdy środowisko programu SQL Server ma wiele wystąpień, ponieważ wzmacnia obszar zagrożenia. Chociaż kopie zapasowe, które pozostają w zaufanej granicy, nie stanowią problemu z zabezpieczeniami, przywracanie złośliwej kopii zapasowej może naruszyć bezpieczeństwo całego środowiska.

Złośliwy .bak plik może:

Przejmij kontrolę nad całym wystąpieniem SQL Server.
Eskaluj uprawnienia i uzyskaj nieautoryzowany dostęp do bazowego hosta lub maszyny wirtualnej.

Ten atak występuje przed wykonaniem skryptów weryfikacji lub kontroli zabezpieczeń, co sprawia, że jest to niezwykle niebezpieczne. Przywracanie niezaufanej kopii zapasowej jest równoważne uruchamianiu niezaufanych aplikacji na krytycznym serwerze lub maszynie wirtualnej i wprowadzeniu dowolnego wykonania kodu do środowiska.

Najlepsze rozwiązania

Postępuj zgodnie z poniższymi najlepszymi rozwiązaniami dotyczącymi zabezpieczeń kopii zapasowych, aby zmniejszyć zagrożenie dla środowisk programu SQL Server:

Traktuj przywracanie kopii zapasowych jako operację wysokiego ryzyka.
Zmniejsz obszar usługi zagrożeń przy użyciu izolowanych wystąpień.
Zezwalaj tylko na zaufane kopie zapasowe: nigdy nie przywracaj kopii zapasowych z nieznanych lub zewnętrznych źródeł.
Zezwalaj tylko na tworzenie kopii zapasowych, które pozostały w granicach zaufanych: upewnij się, że kopie zapasowe pochodzą z zaufanej granicy.
Nie pomijaj mechanizmów kontroli zabezpieczeń dla wygody.
Włącz inspekcję na poziomie serwera , aby przechwycić zdarzenia tworzenia kopii zapasowych i przywracania oraz ograniczyć uchylanie się od inspekcji.

Monitorowanie postępu za pomocą rozwiązania XEvent

Operacje tworzenia kopii zapasowych i przywracania mogą trwać dużo czasu ze względu na rozmiar bazy danych oraz złożoność operacji. Gdy pojawią się problemy z którąkolwiek z operacji, użyj zdarzenia rozszerzonego backup_restore_progress_trace, aby na bieżąco monitorować postęp. Aby uzyskać więcej informacji na temat zdarzeń rozszerzonych, zobacz Omówienie zdarzeń rozszerzonych.

Ostrzeżenie

Rozszerzone zdarzenie backup_restore_progress_trace może powodować problemy z wydajnością i zajmować dużą ilość miejsca na dysku. Używaj go przez krótki czas, zachowaj ostrożność i dokładnie testuj przed użyciem w produkcji.

-- Create the backup_restore_progress_trace extended event session
CREATE EVENT SESSION [BackupRestoreTrace] ON SERVER
ADD EVENT sqlserver.backup_restore_progress_trace
ADD TARGET package0.event_file (SET filename = N'BackupRestoreTrace')
WITH
(
    MAX_MEMORY = 4096 KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 5 SECONDS,
    MAX_EVENT_SIZE = 0 KB,
    MEMORY_PARTITION_MODE = NONE,
    TRACK_CAUSALITY = OFF,
    STARTUP_STATE = OFF
);
GO

-- Start the event session
ALTER EVENT SESSION [BackupRestoreTrace] ON SERVER
STATE = START;
GO

-- Stop the event session
ALTER EVENT SESSION [BackupRestoreTrace] ON SERVER
STATE = STOP;
GO

Przykładowe dane wyjściowe z Extended Event

Zrzut ekranu przykładu kopii zapasowej xevent output.

Zrzut ekranu przykładu kopii zapasowej xevent wyjścia, kontynuacja.

Więcej informacji o zadaniach tworzenia kopii zapasowych

Praca z urządzeniami kopii zapasowych i nośnikiem kopii zapasowych