Tworzenie kopii zapasowych i przywracanie baz danych programu SQL Server

Dotyczy:SQL Server

W tym artykule opisano zalety tworzenia kopii zapasowych baz danych programu SQL Server, opisano podstawowe terminy tworzenia kopii zapasowych i przywracania oraz przedstawiono strategie tworzenia i przywracania kopii zapasowych programu SQL Server oraz zagadnienia dotyczące zabezpieczeń dotyczące tworzenia kopii zapasowych i przywracania programu SQL Server.

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.

Składnik tworzenia i przywracania kopii zapasowej programu SQL Server zapewnia podstawowe zabezpieczenia ochrony krytycznych danych przechowywanych w bazach danych programu SQL Server. Aby zminimalizować ryzyko katastrofanej utraty danych, należy regularnie tworzyć kopie zapasowe baz danych w celu zachowania modyfikacji danych. Dobrze zaplanowana strategia tworzenia i przywracania kopii zapasowych pomaga chronić bazy danych przed utratą danych spowodowaną różnymi awariami. Przetestuj strategię, przywracając zestaw kopii zapasowych, a następnie odzyskując bazę danych, aby przygotować się do skutecznego reagowania na awarię.

Oprócz magazynu lokalnego do przechowywania kopii zapasowych program SQL Server obsługuje również tworzenie kopii zapasowych i przywracanie z usługi Azure Blob Storage. Aby uzyskać więcej informacji, zobacz Tworzenie kopii zapasowych i przywracanie programu SQL Server za pomocą usługi Microsoft 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 Tworzenie kopii zapasowych migawek plików plików na platformie 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. W pełni zarządzane rozwiązanie do tworzenia kopii zapasowych obsługuje zawsze włączone grupy dostępności, długoterminowe przechowywanie, odzyskiwanie do 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 programu SQL Server, uruchamianie procedur przywracania testów w kopiach zapasowych i przechowywanie kopii zapasowych w bezpiecznej lokalizacji poza lokacją 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 kopii zapasowej programu SQL Server w usłudze Azure Blob Storage, można utworzyć kopię zapasową poza lokacją w innym regionie niż lokalizacja lokalna, aby użyć jej w przypadku klęski żywiołowej wpływającej na 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

tworzenie kopii zapasowej [czasownik]
Proces tworzenia kopii zapasowej [noun] przez skopiowanie rekordów danych z bazy danych programu SQL Server lub rekordów dziennika z dziennika transakcji.

kopia zapasowa [noun]
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 kopii zapasowej
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. Aby uzyskać więcej informacji, zobacz Tworzenie kopii zapasowych i przywracanie programu SQL Server za pomocą usługi Microsoft 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.

Odzyskiwania
Faza uruchamiania bazy danych lub przywracania z odzyskiwaniem, która przenosi bazę danych do stanu spójnej na poziomie transakcji.

model odzyskiwania
Właściwość bazy danych, która kontroluje konserwację dziennika transakcji w bazie danych. Istnieją trzy modele odzyskiwania: proste, pełne i rejestrowane zbiorczo. 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

Tworzenie kopii zapasowych i przywracanie danych musi być dostosowane do określonego środowiska i muszą współpracować z dostępnymi zasobami. W związku z tym niezawodne użycie kopii zapasowych i przywracania na potrzeby odzyskiwania wymaga strategii tworzenia kopii zapasowych i przywracania. Dobrze zaprojektowana strategia tworzenia i przywracania kopii zapasowych równoważy wymagania biznesowe dotyczące maksymalnej dostępności danych i minimalnej utraty danych, biorąc pod uwagę koszt konserwacji i przechowywania kopii zapasowych.

Strategia tworzenia kopii zapasowych i przywracania zawiera część kopii zapasowej i część przywracania. Część tworzenia kopii zapasowej strategii definiuje typ i częstotliwość tworzenia kopii zapasowych, charakter i szybkość wymaganego sprzętu, sposób testowania kopii zapasowych oraz miejsca i sposobu przechowywania nośnika kopii zapasowych (w tym zagadnień dotyczących zabezpieczeń). Część przywracania strategii definiuje, kto jest odpowiedzialny za wykonywanie przywracania, jak należy wykonywać przywracanie w celu spełnienia celów dotyczących dostępności bazy danych i zminimalizowania utraty danych oraz sposobu testowania przywracania.

Projektowanie efektywnej strategii tworzenia i przywracania kopii zapasowych wymaga starannego planowania, implementacji i testowania. Testowanie jest wymagane: nie masz strategii tworzenia kopii zapasowych, dopóki nie zostanie pomyślnie przywrócona kopia zapasowa we wszystkich kombinacjach uwzględnionych w strategii przywracania i przetestowano przywróconą bazę danych pod kątem spójności fizycznej. Należy wziąć pod uwagę różne czynniki. Są to:

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ń

Konta, które wykonują operacje tworzenia kopii zapasowej lub przywracania, nie powinny mieć więcej uprawnień niż to konieczne. Przejrzyj kopię zapasową i przywracanie , aby uzyskać szczegółowe informacje o określonych uprawnieniach. Zaleca się, aby kopie zapasowe zostały zaszyfrowane i, jeśli to możliwe, skompresowane.

Aby zapewnić bezpieczeństwo, pliki kopii zapasowych powinny mieć rozszerzenia zgodne z odpowiednimi konwencjami:

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

Korzystanie z oddzielnego magazynu

Ważne

Upewnij się, że kopie zapasowe bazy danych są umieszczone w oddzielnej lokalizacji fizycznej lub urządzeniu z plików bazy danych. Gdy dysk fizyczny przechowujący bazy danych ulegnie awarii lub awarii, możliwość odzyskiwania zależy od możliwości uzyskania dostępu do oddzielnego dysku lub urządzenia zdalnego, które przechowywało kopie zapasowe w celu wykonania przywracania. Należy pamiętać, że można utworzyć kilka woluminów logicznych lub partycji z tego samego dysku fizycznego. Przed wybraniem lokalizacji przechowywania kopii zapasowych dokładnie zbadaj partycję dysku i układy woluminów logicznych.

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. W związku z tym model odzyskiwania bazy danych określa typy kopii zapasowych i scenariuszy przywracania dla bazy danych oraz rozmiar kopii zapasowych dziennika transakcji. 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 wprowadzeniem do tych modeli odzyskiwania i ich wpływem na zarządzanie dziennikami transakcji, zobacz dziennik transakcji.

Najlepszy wybór modelu odzyskiwania dla bazy danych zależy od wymagań biznesowych. 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 podczas operacji rejestrowanych zbiorczo, jednocześnie umożliwiając odzyskiwanie tych operacji, należy użyć modelu odzyskiwania rejestrowanego zbiorczo. Aby uzyskać informacje na temat wpływu modeli odzyskiwania na tworzenie kopii zapasowych i przywracanie, zobacz Omówienie kopii zapasowych.

Projektowanie strategii tworzenia kopii zapasowych

Po wybraniu modelu odzyskiwania spełniającego wymagania biznesowe dla określonej bazy danych należy zaplanować i wdrożyć odpowiednią strategię tworzenia kopii zapasowych. Optymalna strategia tworzenia kopii zapasowych zależy od różnych czynników, z których szczególnie istotne są następujące kwestie:

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

Jeśli istnieje przewidywalny okres poza szczytem, zalecamy zaplanowanie pełnych kopii zapasowych bazy danych w tym okresie.
Jak często występują zmiany i aktualizacje?

Jeśli zmiany są częste, rozważ następujące kwestie:
- W ramach prostego modelu odzyskiwania rozważ zaplanowanie różnicowych kopii zapasowych 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 należy zaplanować częste tworzenie kopii zapasowych dziennika. 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 niewielkiej części bazy danych, czy w dużej części bazy danych?

W przypadku dużej bazy danych, w której zmiany są skoncentrowane w części plików lub grup plików, częściowe kopie zapasowe i pełne kopie zapasowe plików mogą być przydatne. Aby uzyskać więcej informacji, zobacz Częściowe kopie zapasowe (SQL Server) i Pełne kopie zapasowe plików (SQL Server).
Ile miejsca na dysku wymaga pełna kopia zapasowa bazy danych?
Jak daleko w przeszłości twoja firma wymaga obsługi kopii zapasowych?

Upewnij się, że masz ustalony odpowiedni harmonogram tworzenia kopii zapasowych zgodnie z potrzebami aplikacji i wymagań biznesowych. W miarę starości kopii zapasowych ryzyko utraty danych jest wyższe, chyba że masz sposób ponownego generowania wszystkich danych do momentu awarii. Zanim zdecydujesz się na usunięcie starych kopii zapasowych z powodu ograniczeń zasobów magazynu, rozważ, czy w przeszłości wymagana jest możliwość odzyskiwania.

Szacowanie rozmiaru pełnej kopii zapasowej bazy danych

Przed wdrożeniem strategii tworzenia kopii zapasowych i przywracania należy oszacować, ile miejsca na dysku będzie używane pełnej kopii zapasowej bazy danych. Operacja tworzenia kopii zapasowej kopiuje dane w bazie danych do pliku kopii zapasowej. Kopia zapasowa zawiera tylko rzeczywiste dane w bazie danych i nieużywane miejsce. W związku z tym kopia zapasowa jest zwykle mniejsza niż sama baza danych. Rozmiar pełnej kopii zapasowej bazy danych można oszacować przy użyciu sp_spaceused procedury składowanej systemu. Aby uzyskać więcej informacji, zobacz sp_spaceused (Transact-SQL).

Planowanie kopii zapasowych

Wykonywanie operacji tworzenia kopii zapasowej ma minimalny wpływ na uruchomione transakcje; w związku z tym można uruchamiać operacje tworzenia kopii zapasowej podczas regularnych operacji. Możesz wykonać kopię zapasową programu SQL Server z minimalnym wpływem na obciążenia produkcyjne.

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

Po podjęciu decyzji o typach kopii zapasowych, których potrzebujesz i sposobie wykonywania poszczególnych typów, zalecamy zaplanowanie regularnych kopii zapasowych w ramach planu konserwacji 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. Bardzo ważne jest, aby dokładnie przetestować strategię tworzenia kopii zapasowych dla każdej bazy danych, przywracając kopię bazy danych do systemu testowego. Należy przetestować przywracanie każdego typu kopii zapasowej, która ma być używana. Zalecamy również, aby po przywróceniu kopii zapasowej wykonać kontrole spójności bazy danych za pośrednictwem bazy danych DBCC CHECKDB bazy danych, aby sprawdzić, czy nośnik kopii zapasowej nie został uszkodzony.

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

Użyj opcji weryfikacji udostępnianych przez narzędzia kopii zapasowej (BACKUP polecenie T-SQL, plany konserwacji programu SQL Server, oprogramowanie lub rozwiązanie kopii zapasowej itp.). Aby zapoznać się z przykładem, zobacz instrukcje RESTORE — VERIFYONLY.

Użyj zaawansowanych funkcji, takich jak BACKUP CHECKSUM wykrywanie problemów z samym nośnikiem kopii zapasowej. Aby uzyskać więcej informacji, zobacz Możliwe błędy multimediów podczas tworzenia kopii zapasowej i przywracania (SQL Server)

Strategia tworzenia/przywracania kopii zapasowych dokumentów

Zalecamy dokumentowanie procedur tworzenia kopii zapasowych i przywracania oraz przechowywanie kopii dokumentacji w książce run book. Zalecamy również ręczne wykonywanie operacji dla każdej bazy danych. Ta instrukcja obsługi powinna udokumentować lokalizację kopii zapasowych, nazwy urządzeń kopii zapasowych (jeśli istnieją) oraz czas wymagany do przywrócenia 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 i przywracania kopii zapasowych mogą zająć dużo czasu ze względu na rozmiar bazy danych i złożoność zaangażowanych operacji. Jeśli wystąpią problemy z jedną operacją, możesz użyć rozszerzonego backup_restore_progress_trace zdarzenia do monitorowania postępu na żywo. Aby uzyskać więcej informacji na temat zdarzeń rozszerzonych, zobacz Omówienie zdarzeń rozszerzonych.

Ostrzeżenie

Użycie zdarzenia rozszerzonego backup_restore_progress_trace może powodować problem z wydajnością i zużywać znaczną ilość miejsca na dysku. Należy używać w krótkich okresach czasu, zachować ostrożność i dokładnie przetestować przed wdrożeniem w środowisku produkcyjnym.

-- 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 zdarzenia rozszerzonego

Zrzut ekranu przedstawiający przykład tworzenia kopii zapasowej danych wyjściowych xevent.

Więcej informacji o zadaniach tworzenia kopii zapasowych

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