Udostępnij za pomocą


Tworzenie pełnej kopii zapasowej bazy danych

Dotyczy:programu SQL Server

W tym artykule opisano sposób tworzenia pełnej kopii zapasowej bazy danych w programie SQL Server przy użyciu programu SQL Server Management Studio, języka Transact-SQL lub programu PowerShell.

Aby uzyskać więcej informacji, zobacz Tworzenie kopii zapasowej i przywracanie programu SQL Server przy użyciu usługi Azure Blob Storage i kopii zapasowej programu SQL Server pod adresem URL usługi Azure Blob Storage.

Ograniczenia

  • Instrukcja BACKUP nie jest dozwolona w ramach jawnej lub niejawnej transakcji.
  • Nie można przywrócić kopii zapasowych utworzonych przez nowsze wersje programu SQL Server we wcześniejszych wersjach programu SQL Server.

Aby zapoznać się z omówieniem i dokładniej zapoznać się z pojęciami i zadaniami tworzenia kopii zapasowych, zobacz Omówienie kopii zapasowych (SQL Server) przed kontynuowaniem.

Zalecenia

  • W miarę zwiększania rozmiaru bazy danych tworzenie pełnych kopii zapasowych bazy danych zajmuje więcej czasu i wymaga więcej miejsca do magazynowania. W przypadku dużych baz danych rozważ uzupełnienie pełnych kopii zapasowych serią różnicowych kopii zapasowych baz danych.
  • Oszacuj rozmiar pełnej kopii zapasowej bazy danych, używając systemowej procedury składowanej sp_spaceused.
  • Domyślnie każda pomyślna operacja tworzenia kopii zapasowej dodaje wpis w dzienniku błędów programu SQL Server i w dzienniku zdarzeń systemu. W przypadku częstego tworzenia kopii zapasowej komunikaty o powodzeniu szybko gromadzą się, co powoduje ogromne dzienniki błędów, co utrudnia znalezienie innych komunikatów. W takich przypadkach można pominąć te wpisy dziennika kopii zapasowej przy użyciu flagi śledzenia 3226, jeśli żaden ze skryptów nie zależy od tych wpisów. Aby uzyskać więcej informacji, zobacz Ustawianie flag śledzenia za pomocą funkcji TRACEON DBCC.

Zabezpieczenia

TRUSTWORTHY jest ustawiona OFF na wartość w kopii zapasowej bazy danych. Aby uzyskać informacje o sposobie ustawiania TRUSTWORTHY wartości ON, zobacz ALTER DATABASE SET options (Opcje ALTER DATABASE SET).

Począwszy od programu SQL Server 2012 (11.x), PASSWORD opcje i MEDIAPASSWORD nie są dostępne do tworzenia kopii zapasowych. Nadal można przywrócić kopie zapasowe utworzone przy użyciu haseł.

Uprawnienia

Uprawnienia BACKUP DATABASE i BACKUP LOG są domyślnie przyznawane członkom stałej roli serwera sysadmin oraz stałych ról bazy danych db_owner i db_backupoperator.

Problemy z własnością i uprawnieniami w pliku fizycznym urządzenia kopii zapasowej mogą zakłócać operację tworzenia kopii zapasowej. Usługa SQL Server powinna odczytywać dane z urządzenia i zapisywać je na urządzeniu. Konto, na którym działa usługa SQL Server, musi mieć uprawnienia do zapisu na urządzeniu kopii zapasowej. Jednak sp_addumpdevice, który dodaje wpis dla urządzenia kopii zapasowej w tabelach systemowych, nie sprawdza uprawnień dostępu do plików. Problemy z plikiem fizycznym urządzenia kopii zapasowej mogą nie pojawić się, dopóki kopia zapasowa nie zostanie użyta lub próba przywrócenia.

Korzystanie z programu SQL Server Management Studio

Notatka

Po określeniu zadania tworzenia kopii zapasowej przy użyciu programu SQL Server Management Studio możesz wygenerować odpowiedni skrypt kopii zapasowej Transact-SQL, wybierając przycisk Skrypt , a następnie wybierając miejsce docelowe skryptu.

  1. Po nawiązaniu połączenia z odpowiednim wystąpieniem aparatu bazy danych programu SQL Server w Eksploratorze obiektów rozwiń drzewo serwera.

  2. Rozwiń węzeł Bazy danych, a następnie wybierz bazę danych użytkownika lub rozwiń Systemowe bazy danych i wybierz systemową bazę danych.

  3. Kliknij prawym przyciskiem myszy bazę danych, której kopię zapasową chcesz utworzyć, wskaż polecenie Zadania, a następnie wybierz polecenie Utwórz kopię zapasową....

  4. W oknie dialogowym Tworzenie kopii zapasowej bazy danych wybrana baza danych zostanie wyświetlona na liście rozwijanej. (Bazę danych można zmienić na dowolną inną bazę danych na serwerze).

  5. Na liście Typ kopii zapasowej wybierz typ kopii zapasowej. Wartość domyślna to Pełna.

    Ważny

    Przed wykonaniem różnicowej lub transakcyjnej kopii zapasowej dziennika należy wykonać co najmniej jedną pełną kopię zapasową bazy danych.

  6. W sekcji Składnik kopii zapasowejwybierz pozycję Baza danych.

  7. W sekcji Destination przejrzyj domyślną lokalizację pliku kopii zapasowej (w pliku .). /mssql/data folder).

    Aby wybrać inne urządzenie, możesz użyć listy Tworzenie kopii zapasowej . Wybierz pozycję Dodaj , aby dodać obiekty kopii zapasowej i/lub miejsca docelowe. Zestaw kopii zapasowych można rozłożyć na wiele plików, aby zwiększyć szybkość tworzenia kopii zapasowych.

    Aby usunąć miejsce docelowe kopii zapasowej, wybierz ją, a następnie wybierz pozycję Usuń. Aby wyświetlić zawartość istniejącego miejsca docelowego kopii zapasowej, wybierz ją, a następnie wybierz pozycję Zawartość.

  8. (Opcjonalnie) Przejrzyj inne dostępne ustawienia na stronach Opcje multimediów i Opcje kopii zapasowej .

    Aby uzyskać więcej informacji na temat różnych opcji tworzenia kopii zapasowych, zobacz Tworzenie kopii zapasowej bazy danych (strona ogólna) i Kopia zapasowa bazy danych (strona opcji kopii zapasowej).

  9. Wybierz pozycję OK, aby uruchomić kopię zapasową.

  10. Po pomyślnym zakończeniu tworzenia kopii zapasowej wybierz przycisk OK , aby zamknąć okno dialogowe programu SQL Server Management Studio.

Dodatkowe informacje

  • Po utworzeniu pełnej kopii zapasowej bazy danych można utworzyć różnicową kopię zapasową bazy danych lub kopii zapasowej dziennika transakcji.

  • (Opcjonalnie) Możesz zaznaczyć pole wyboru Kopia zapasowa tylko do kopiowania, aby utworzyć kopię zapasową tylko do kopiowania. Kopia zapasowa tylko do kopiowania to kopia zapasowa programu SQL Server, która jest niezależna od sekwencji konwencjonalnych kopii zapasowych programu SQL Server. Aby uzyskać więcej informacji, zobacz Kopie zapasowe tylko do kopiowania. Kopia zapasowa tylko do kopiowania nie jest dostępna dla różnicowego typu kopii zapasowej.

  • Opcja zastępowania multimediów jest wyłączona na stronie Media Options, jeśli tworzysz kopię zapasową na adresie URL.

Przykłady

W poniższych przykładach utwórz testową bazę danych z następującym kodem Transact-SQL:

USE [master]
GO

CREATE DATABASE [SQLTestDB]
GO

USE [SQLTestDB]
GO
CREATE TABLE SQLTest
   (
      ID INT NOT NULL PRIMARY KEY,
      c1 VARCHAR(100) NOT NULL,
      dt1 DATETIME NOT NULL DEFAULT getdate()
   );
GO

USE [SQLTestDB]
GO

INSERT INTO SQLTest (ID, c1) VALUES (1, 'test1')
INSERT INTO SQLTest (ID, c1) VALUES (2, 'test2')
INSERT INTO SQLTest (ID, c1) VALUES (3, 'test3')
INSERT INTO SQLTest (ID, c1) VALUES (4, 'test4')
INSERT INTO SQLTest (ID, c1) VALUES (5, 'test5')
GO

SELECT * FROM SQLTest
GO

Odp. Pełna kopia zapasowa na dysku do lokalizacji domyślnej

W tym przykładzie kopia zapasowa SQLTestDB bazy danych jest tworzona na dysku w domyślnej lokalizacji kopii zapasowej.

  1. Po nawiązaniu połączenia z odpowiednim wystąpieniem aparatu bazy danych programu SQL Server w Eksploratorze obiektów rozwiń drzewo serwera.

  2. Rozwiń węzeł Bazy danych, kliknij prawym przyciskiem myszy SQLTestDB, wskaż polecenie Tasks, a następnie wybierz pozycję Utwórz kopię zapasową....

  3. Wybierz pozycję OK.

  4. Po pomyślnym zakończeniu tworzenia kopii zapasowej wybierz przycisk OK , aby zamknąć okno dialogowe programu SQL Server Management Studio.

Zrzut ekranu przedstawiający kroki tworzenia kopii zapasowej.

B. Pełna kopia zapasowa na dysku w lokalizacji niezdefaultowej

W tym przykładzie kopia zapasowa SQLTestDB bazy danych jest tworzona na dysku w wybranej lokalizacji.

  1. Po nawiązaniu połączenia z odpowiednim wystąpieniem aparatu bazy danych programu SQL Server w Eksploratorze obiektów rozwiń drzewo serwera.

  2. Rozwiń węzeł Bazy danych, kliknij prawym przyciskiem myszy SQLTestDB, wskaż polecenie Tasks, a następnie wybierz pozycję Utwórz kopię zapasową....

  3. Na stronie Ogólne w sekcji Miejsce docelowe wybierz pozycję Dysk na liście Tworzenie kopii zapasowej.

  4. Wybierz pozycję Usuń , dopóki wszystkie istniejące pliki kopii zapasowej nie zostaną usunięte.

  5. Wybierz Dodaj. Zostanie otwarte okno dialogowe Wybieranie lokalizacji docelowej kopii zapasowej .

  6. Wprowadź prawidłową ścieżkę i nazwę pliku w polu Nazwa pliku . Użyj .bak jako rozszerzenia, aby uprościć klasyfikację pliku.

  7. Wybierz pozycję OK, a następnie ponownie wybierz pozycję OK, aby uruchomić kopię zapasową.

  8. Po pomyślnym zakończeniu tworzenia kopii zapasowej wybierz przycisk OK , aby zamknąć okno dialogowe programu SQL Server Management Studio.

Zrzut ekranu przedstawiający sposób dodawania lub usuwania lokalizacji kopii zapasowej.

C. Tworzenie zaszyfrowanej kopii zapasowej

W tym przykładzie kopia zapasowa SQLTestDB bazy danych jest wykonywana z szyfrowaniem w domyślnej lokalizacji kopii zapasowej.

  1. Po nawiązaniu połączenia z odpowiednim wystąpieniem aparatu bazy danych programu SQL Server w Eksploratorze obiektów rozwiń drzewo serwera.

  2. Rozwiń węzeł Bazy danych, rozwiń węzeł Systemowe bazy danych, kliknij prawym przyciskiem myszy masterpozycję , a następnie wybierz pozycję Nowe zapytanie , aby otworzyć okno zapytania z połączeniem SQLTestDB z bazą danych.

  3. Uruchom następujące polecenia, aby utworzyć klucz główny bazy danych i certyfikat w master bazie danych.

    -- Create the master key.
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';
    
    -- If the master key already exists, open it in the same session that you create the certificate. (See next step.)
    OPEN MASTER KEY DECRYPTION BY PASSWORD = '<password>'
    
    -- Create the certificate encrypted by the master key.
    CREATE CERTIFICATE MyCertificate
    WITH SUBJECT = 'Backup Cert', EXPIRY_DATE = '20201031';
    
  4. W eksploratorze obiektów w węźle bazy danych kliknij prawym przyciskiem myszy pozycję SQLTestDB, wskaż polecenie Tasks, a następnie wybierz pozycję Wykonaj kopię zapasową....

  5. Na stronie Opcje multimediów w sekcji Zastąp nośnik wybierz pozycję Utwórz kopię zapasową nowego zestawu multimediów i wymazaj wszystkie istniejące zestawy kopii zapasowych.

  6. Na stronie Opcje kopii zapasowej w sekcji Szyfrowanie wybierz pozycję Szyfruj kopię zapasową.

  7. Na liście Algorytm wybierz pozycję AES 256.

  8. Na liście Certyfikat lub Klucz asymetryczny wybierz pozycję MyCertificate.

  9. Wybierz pozycję OK.

Zrzut ekranu przedstawiający kroki tworzenia zaszyfrowanej kopii zapasowej.

D. Tworzenie kopii zapasowej do Azure Blob Storage

W tym przykładzie tworzona jest pełna kopia zapasowa SQLTestDB bazy danych w usłudze Azure Blob Storage. Przykład został napisany przy założeniu, że masz już konto magazynu z kontenerem obiektów blob. W przykładzie zostanie utworzony sygnatura dostępu współdzielonego. Przykład kończy się niepowodzeniem, jeśli kontener ma istniejący sygnaturę dostępu współdzielonego.

Jeśli nie masz kontenera usługi Blob Storage na koncie magazynu, utwórz go przed kontynuowaniem. Zobacz Tworzenie konta magazynu ogólnego przeznaczenia i Tworzenie kontenera.

  1. Po nawiązaniu połączenia z odpowiednim wystąpieniem aparatu bazy danych programu SQL Server w Eksploratorze obiektów rozwiń drzewo serwera.

  2. Rozwiń węzeł Bazy danych, kliknij prawym przyciskiem myszy SQLTestDB, wskaż polecenie Tasks, a następnie wybierz pozycję Utwórz kopię zapasową....

  3. Na stronie Ogólne w sekcji Miejsce docelowe wybierz pozycję Adres URL na liście Wykonaj kopię zapasową.

  4. Wybierz Dodaj. Zostanie otwarte okno dialogowe Wybieranie lokalizacji docelowej kopii zapasowej .

  5. Jeśli wcześniej zarejestrowano kontener usługi Azure Storage, którego chcesz używać z programem SQL Server Management Studio, wybierz go. W przeciwnym razie wybierz pozycję Nowy kontener, aby zarejestrować nowy kontener.

  6. W oknie dialogowym Łączenie z subskrypcją firmy Microsoft zaloguj się do swojego konta.

  7. W polu Wybierz konto magazynu wybierz swoje konto magazynu.

  8. W polu Wybierz kontener obiektów blob wybierz kontener obiektów blob.

  9. W kalendarzu Wygasanie zasad dostępu współdzielonego wybierz datę wygaśnięcia dla zasad dostępu współdzielonego utworzonych w tym przykładzie.

  10. Wybierz pozycję Utwórz poświadczenie, aby wygenerować wspólną sygnaturę dostępu i poświadczenie w programie SQL Server Management Studio.

  11. Wybierz przycisk OK zamknij okno dialogowe Łączenie z subskrypcją firmy Microsoft .

  12. W polu Plik kopii zapasowej zmień nazwę pliku kopii zapasowej, jeśli chcesz.

  13. Wybierz przycisk OK , aby zamknąć okno dialogowe Wybieranie miejsca docelowego kopii zapasowej .

  14. Wybierz pozycję OK, aby uruchomić kopię zapasową.

  15. Po pomyślnym zakończeniu tworzenia kopii zapasowej wybierz przycisk OK , aby zamknąć okno dialogowe programu SQL Server Management Studio.

Notatka

Tworzenie kopii zapasowej w usłudze Blob Storage przy użyciu tożsamości zarządzanych nie jest obecnie obsługiwane.

Korzystanie z Transact-SQL

Utwórz pełną kopię zapasową bazy danych, uruchamiając instrukcję BACKUP DATABASE , określając:

  • Nazwa bazy danych do utworzenia kopii zapasowej.
  • Urządzenie kopii zapasowej, na którym jest zapisywana pełna kopia zapasowa bazy danych.

Podstawowa Transact-SQL składnia pełnej kopii zapasowej bazy danych to:

BACKUP DATABASE <database>
TO <backup_device> [ , ...n ]
[ WITH <with_options> [ , ...o ] ];
Opcja Opis
<database> Kopia zapasowa bazy danych, która ma zostać utworzona.
<backup_device> [ , ...n ] Określa listę urządzeń kopii zapasowych z zakresu od 1 do 64 do użycia na potrzeby operacji tworzenia kopii zapasowej. Możesz określić fizyczne urządzenie do tworzenia kopii zapasowej lub określić odpowiednie logiczne urządzenie kopii zapasowej, jeśli zostało już zdefiniowane. Aby określić fizyczne urządzenie kopii zapasowej, użyj DISK opcji lub TAPE :

{ DISK | TAPE } = physical_backup_device_name

Aby uzyskać więcej informacji, zobacz Backup Devices (SQL Server).
WITH <with_options> [ , ...o ] Służy do określania jednej lub więcej opcji o. Poniżej przedstawiono niektóre z podstawowych WITH opcji.

Opcjonalnie określ co najmniej jedną WITH opcję. Poniżej opisano kilka podstawowych WITH opcji. Aby uzyskać informacje o wszystkich opcjach WITH , zobacz KOPIA ZAPASOWA.

Podstawowe opcje zestawu WITH kopii zapasowych:

  • { KOMPRESJA | NO_COMPRESSION }. W programie SQL Server 2008 (10.0.x) Enterprise i nowszym określa, czy kompresja kopii zapasowej jest wykonywana na kopii zapasowej, przesłaniając wartość domyślną na poziomie serwera.
  • SZYFROWANIE (ALGORYTM, CERTYFIKAT SERWERA | KLUCZ ASYMETRYCZNY). W programie SQL Server 2014 lub nowszym określa algorytm szyfrowania do użycia, a certyfikat lub klucz asymetryczny do użycia w celu zabezpieczenia szyfrowania.
  • DESCRIPTION = { 'text' | @text_variable }. Określa dowolny tekst opisujący zestaw kopii zapasowych. Ciąg może mieć maksymalnie 255 znaków.
  • NAME = { backup_set_name | @backup_set_name_var }. Określa nazwę zestawu kopii zapasowych. Nazwy mogą mieć maksymalnie 128 znaków. Jeśli NAME nie zostanie określony, jest on pusty.

Domyślnie BACKUP dołącza kopię zapasową do istniejącego zestawu multimediów, zachowując istniejące zestawy kopii zapasowych. Aby jawnie określić tę konfigurację NOINIT , użyj opcji . Aby uzyskać informacje na temat dołączania do istniejących zestawów kopii zapasowych, zobacz Zestawy multimediów, rodziny multimediów i zestawy kopii zapasowych (SQL Server).

Aby sformatować nośnik kopii zapasowej, użyj FORMAT opcji:

FORMAT [ , MEDIANAME = { media_name | @media_name_variable } ] [ , MEDIADESCRIPTION = { text | @text_variable } ]

Użyj klauzuli FORMAT podczas korzystania z nośnika po raz pierwszy lub gdy chcesz zastąpić wszystkie istniejące dane. Opcjonalnie przypisz nowemu nośnikowi nazwę i opis.

Ważny

Należy zachować ostrożność podczas używania FORMAT klauzuli instrukcji BACKUP , ponieważ ta opcja niszczy wszystkie kopie zapasowe, które były wcześniej przechowywane na nośniku kopii zapasowych.

Przykłady

W poniższych przykładach utwórz testową bazę danych z następującym kodem Transact-SQL:

USE [master]
GO

CREATE DATABASE [SQLTestDB]
GO

USE [SQLTestDB]
GO
CREATE TABLE SQLTest (
   ID INT NOT NULL PRIMARY KEY,
   c1 VARCHAR(100) NOT NULL,
   dt1 DATETIME NOT NULL DEFAULT GETDATE()
)
GO

USE [SQLTestDB]
GO

INSERT INTO SQLTest (ID, c1) VALUES (1, 'test1')
INSERT INTO SQLTest (ID, c1) VALUES (2, 'test2')
INSERT INTO SQLTest (ID, c1) VALUES (3, 'test3')
INSERT INTO SQLTest (ID, c1) VALUES (4, 'test4')
INSERT INTO SQLTest (ID, c1) VALUES (5, 'test5')
GO

SELECT * FROM SQLTest
GO

Odp. Wykonaj kopię zapasową na urządzeniu dyskowym

Poniższy przykład wykonuje kopię zapasową kompletnej SQLTestDB bazy danych na dysku. Używa FORMAT go do utworzenia nowego zestawu multimediów.

USE SQLTestDB;
GO
BACKUP DATABASE SQLTestDB
TO DISK = 'c:\tmp\SQLTestDB.bak'
   WITH FORMAT,
      MEDIANAME = 'SQLServerBackups',
      NAME = 'Full Backup of SQLTestDB';
GO

B. Wykonaj kopię zapasową na urządzeniu taśmowym

Poniższy przykład wykonuje kopię zapasową pełnej SQLTestDB bazy danych na taśmie. Dołącza kopię zapasową do poprzednich kopii zapasowych.

USE SQLTestDB;
GO
BACKUP DATABASE SQLTestDB
   TO TAPE = '\\.\Tape0'
   WITH NOINIT,
      NAME = 'Full Backup of SQLTestDB';
GO

C. Tworzenie kopii zapasowej na urządzeniu taśmy logicznej

Poniższy przykład tworzy logiczne urządzenie kopii zapasowej dla stacji taśmowej. Następnie przykład wykonuje kopię zapasową pełnej SQLTestDB bazy danych na tym urządzeniu.

-- Create a logical backup device,
-- SQLTestDB_Bak_Tape, for tape device \\.\tape0.
USE master;
GO
EXEC sp_addumpdevice 'tape', 'SQLTestDB_Bak_Tape', '\\.\tape0'; USE SQLTestDB;
GO
BACKUP DATABASE SQLTestDB
   TO SQLTestDB_Bak_Tape
   WITH FORMAT,
      MEDIANAME = 'SQLTestDB_Bak_Tape',
      MEDIADESCRIPTION = '\\.\tape0',
      NAME = 'Full Backup of SQLTestDB';
GO

Użyj PowerShell

Backup-SqlDatabase Użyj polecenia cmdlet . Aby jawnie wskazać pełną kopię zapasową bazy danych, określ -BackupAction parametr z wartością domyślną . Database Ten parametr jest opcjonalny dla pełnych kopii zapasowych bazy danych.

Notatka

Te przykłady wymagają modułu SqlServer. Aby określić, czy jest zainstalowany, uruchom polecenie Get-Module -Name SqlServer. Aby go zainstalować, uruchom polecenie Install-Module -Name SqlServer w sesji administratora programu PowerShell.

Aby uzyskać więcej informacji, zobacz Dostawca PowerShell SQL Server.

Ważny

Jeśli otwierasz okno programu PowerShell z poziomu programu SQL Server Management Studio (SSMS) w celu nawiązania połączenia z wystąpieniem programu SQL Server, możesz pominąć część poświadczeń, ponieważ poświadczenia w programie SSMS są automatycznie używane do nawiązywania połączenia między programem PowerShell i wystąpieniem programu SQL Server.

Przykłady

Odp. Pełna kopia zapasowa (lokalna)

Poniższy przykład tworzy pełną kopię zapasową bazy danych <myDatabase> w domyślnej lokalizacji kopii zapasowej instancji serwera Computer\Instance. Opcjonalnie ten przykład określa wartość -BackupAction Database.

Aby zapoznać się z pełnymi przykładami składni, zobacz Backup-SqlDatabase.

$credential = Get-Credential

Backup-SqlDatabase -ServerInstance Computer[\Instance] -Database <myDatabase> -BackupAction Database -Credential $credential

B. Pełna kopia zapasowa na platformie Azure

Poniższy przykład tworzy pełną kopię zapasową bazy danych <myDatabase> w wystąpieniu do <myServer> usługi Blob Storage. Zasady dostępu przechowywanego zostały utworzone z uprawnieniami do odczytu, zapisu i listy. Poświadczenie https://<myStorageAccount>.blob.core.windows.net/<myContainer>programu SQL Server zostało utworzone przy użyciu sygnatury dostępu współdzielonego skojarzonej z zapisanymi zasadami dostępu. Polecenie używa parametru $backupFile , aby określić lokalizację (adres URL) i nazwę pliku kopii zapasowej.

$credential = Get-Credential
$container = 'https://<myStorageAccount>blob.core.windows.net/<myContainer>'
$fileName = '<myDatabase>.bak'
$server = '<myServer>'
$database = '<myDatabase>'
$backupFile = $container + '/' + $fileName

Backup-SqlDatabase -ServerInstance $server -Database $database -BackupFile $backupFile -Credential $credential