Planowanie i automatyzowanie kopii zapasowych baz danych SQL Server Express

Oryginalna wersja produktu: SQL Server
Oryginalny numer KB: 2019698

Podsumowanie

SQL Server Express nie obejmuje SQL Server Agent, więc nie można zaplanować zadań tworzenia kopii zapasowych ani planów konserwacji tak, jak można w innych wersjach SQL Server. Aby zautomatyzować tworzenie kopii zapasowych w programie SQL Server Express, połącz trzy składniki: procedurę składowaną sp_BackupDatabases, narzędzie wiersza polecenia sqlcmd oraz Harmonogram zadań systemu Windows. W tym artykule przedstawiono kroki konfigurowania zaplanowanych pełnych, różnicowych i transakcyjnych kopii zapasowych dzienników baz danych SQL Server Express. Wyjaśniono również, jak potwierdzić, że zaplanowana kopia zapasowa została uruchomiona.

Ten artykuł dotyczy tylko wersji SQL Server Express. Nie dotyczy SQL Server Express LocalDB.

Dlaczego SQL Server Express nie może zaplanować tworzenia kopii zapasowych za pomocą SQL Server Agent

SQL Server Agent to składnik, który uruchamia zaplanowane zadania i plany konserwacji i nie jest uwzględniony w usłudze SQL Server Express. Aby zapoznać się z pełnym porównaniem elementów dostępnych w poszczególnych wersjach, zobacz Wersje i obsługiwane funkcje SQL Server.

Bez SQL Server Agent nadal można utworzyć kopię zapasową bazy danych SQL Server Express na żądanie przy użyciu dowolnego z następujących narzędzi:

• narzędzie sqlcmd
• SQL Server Management Studio (SSMS)
• Rozszerzenie MSSQL dla programu Visual Studio Code
• Skrypt Transact-SQL używający rodziny poleceń BACKUP (Transact-SQL)

Aby uruchomić te kopie zapasowe zgodnie z harmonogramem zamiast na żądanie, użyj Windows Harmonogram zadań jako aparatu planowania, zgodnie z opisem w poniższych krokach. Aby uzyskać informacje na temat typów kopii zapasowych i strategii, zobacz Tworzenie kopii zapasowych i przywracanie baz danych SQL Server.

Wymagania wstępne

Przed rozpoczęciem upewnij się, że masz następujące elementy:

• Uruchomione wystąpienie SQL Server Express. W przykładach w tym artykule użyto domyślnego wystąpienia o nazwie .\SQLEXPRESS.
• Członkostwo w stałej roli serwera sysadmin w tym wystąpieniu, wymagane do utworzenia procedury składowanej w bazie danych master.
• Folder lokalny lub dysk z wystarczającą ilością wolnego miejsca do przechowywania plików kopii zapasowej. W przykładach użyto metody D:\SQLBackups.
• Uprawnienie do tworzenia zaplanowanych zadań na komputerze z systemem SQL Server Express.

Krok 1. Tworzenie procedury składowanej sp_BackupDatabases

sp_BackupDatabases to procedura składowana udostępniona przez firmę Microsoft, która uruchamia odpowiednie polecenie BACKUP dla jednej bazy danych lub każdej bazy danych będącej w trybie online w tym wystąpieniu. Tworzysz ją jeden raz w master bazie danych.

Pobierz skrypt z SQL_Express_Backups.sql i zapisz go lokalnie, na przykład C:\Temp\SQL_Express_Backups.sql. Następnie utwórz procedurę składowaną za pomocą programu SSMS lub sqlcmd.

Utwórz procedurę składowaną za pomocą programu SSMS

1. Otwórz program SSMS i połącz się z wystąpieniem SQL Server Express. W polu Nazwa serwera wprowadź ciąg .\SQLEXPRESS, a następnie wybierz pozycję Połącz.
2. Wybierz pozycję Plik>Otwórz>plik, wybierz zapisany plik SQL_Express_Backups.sql , a następnie wybierz pozycję Otwórz.
3. Upewnij się, że lista baz danych na pasku narzędzi zawiera wartość master. Skrypt zaczyna się od USE [master], dzięki czemu automatycznie jest kierowany do właściwej bazy danych.
4. Wybierz pozycję Wykonaj (lub naciśnij klawisz F5). Panel Komunikaty zgłasza Commands completed successfully.

Utwórz procedurę składowaną za pomocą narzędzia sqlcmd

Uruchom następujące polecenie w wierszu polecenia:

sqlcmd -S .\SQLEXPRESS -E -i "C:\Temp\SQL_Express_Backups.sql"

Upewnij się, że utworzono procedurę składowaną

Uruchom następujące zapytanie względem wystąpienia. Zwraca jeden wiersz, jeśli procedura składowana istnieje:

SELECT name, create_date
FROM master.sys.procedures
WHERE name = 'sp_BackupDatabases';

parametry sp_BackupDatabases

Procedura składowana akceptuje następujące parametry:

Procedura składowana nigdy nie tworzy kopii zapasowej tempdb. Pomija również przykładowe bazy danych Northwind, pubsi AdventureWorks dla każdego typu kopii zapasowej, a także pomija master różnicowe kopie zapasowe i kopie zapasowe dzienników, ponieważ te typy kopii zapasowych nie są obsługiwane dla master bazy danych.

Krok 2. Instalowanie narzędzia sqlcmd

Narzędzie sqlcmd umożliwia uruchamianie instrukcji Transact-SQL, systemowych procedur składowanych i plików skryptów z wiersza polecenia. Jest on instalowany z programem SSMS i jest również dostępny jako autonomiczny plik do pobrania dla komputerów, które nie mają programu SSMS. Aby go zainstalować, zobacz Pobieranie i instalowanie narzędzia sqlcmd.

Aby potwierdzić, że sqlcmd jest zainstalowany i dostępny, uruchom polecenie sqlcmd -? w wierszu polecenia.

Folder zawierający sqlcmd plik wykonywalny jest zwykle dodawany do zmiennej środowiskowej Path podczas instalowania SQL Server lub autonomicznych narzędzi. Jeśli sqlcmd -? raportuje, że polecenie nie jest rozpoznawane, dodaj folder do Path zmiennej lub określ pełną ścieżkę do narzędzia w pliku wsadowym.

Krok 3. Tworzenie pliku wsadowego Sqlbackup.bat

W edytorze tekstów utwórz plik wsadowy o nazwie Sqlbackup.bat. Skopiuj tekst z jednego z poniższych przykładów do tego pliku w zależności od scenariusza.

Przed wybraniem przykładu należy wziąć pod uwagę następujące kwestie:

• Każdy przykład używa D:\SQLBackups jako symbolu zastępczego. Zmień ścieżkę na dysk i folder kopii zapasowej, którego chcesz użyć w danym środowisku, i upewnij się, że folder istnieje.
• Jeśli używasz uwierzytelniania SQL Server, hasło jest przechowywane w postaci zwykłego tekstu w pliku wsadowym. Ogranicz dostęp do folderu zawierającego plik wsadowy tylko autoryzowanym użytkownikom.

Przykład 1. Pełne kopie zapasowe wszystkich baz danych przy użyciu uwierzytelniania Windows

REM Sqlbackup.bat
sqlcmd -S .\SQLEXPRESS -E -b -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @backupType='F'"

Przykład 2. Różnicowe kopie zapasowe wszystkich baz danych przy użyciu uwierzytelniania SQL Server

REM Sqlbackup.bat
sqlcmd -U <YourSQLLogin> -P <StrongPassword> -S .\SQLEXPRESS -b -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @backupType='D'"

Przykład 3. Kopie zapasowe dziennika transakcji wszystkich baz danych przy użyciu uwierzytelniania Windows

REM Sqlbackup.bat
sqlcmd -S .\SQLEXPRESS -E -b -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @backupType='L'"

Kopie zapasowe dziennika transakcji wymagają pełnego modelu odzyskiwania lub modelu odzyskiwania z rejestrowaniem zbiorczym oraz co najmniej jednej wcześniejszej pełnej kopii zapasowej. Aby uzyskać więcej informacji, zobacz Recovery models (SQL Server).

Przykład 4. Pełna kopia zapasowa pojedynczej bazy danych przy użyciu uwierzytelniania Windows

REM Sqlbackup.bat
sqlcmd -S .\SQLEXPRESS -E -b -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @databaseName='USERDB', @backupType='F'"

Aby utworzyć różnicową kopię zapasową USERDB, zmień parametr @backupType na D. Aby utworzyć kopię zapasową dziennika transakcji, zmień ją na L.

Opcja -b powoduje, że sqlcmd kończy działanie i zwraca DOS ERRORLEVEL wartość 1, gdy błąd SQL Server ma poziom ważności większy niż 10. Bez -b element sqlcmd zwraca 0 nawet wtedy, gdy wykonanie kopii zapasowej się nie powiedzie, a Harmonogram zadań zgłasza zadanie jako pomyślnie ukończone.

Przed kontynuowaniem uruchom Sqlbackup.bat ręcznie z poziomu wiersza polecenia i upewnij się, że pliki kopii zapasowej są wyświetlane w folderze kopii zapasowej. Naprawianie błędów jest teraz łatwiejsze niż diagnozowanie ich później za pomocą harmonogramu zadań.

Krok 4: Zaplanuj plik wsadowy w Harmonogramie zadań systemu Windows

Wykonaj następujące kroki, aby uruchomić Sqlbackup.bat zgodnie z harmonogramem:

1. Na komputerze z uruchomionym programem SQL Server Express wybierz pozycję Start i wpisz Harmonogram zadań w polu tekstowym.

   

2. W obszarze Najlepsze dopasowanie wybierz pozycję Harmonogram zadań, aby go uruchomić.

3. W harmonogramie zadań kliknij prawym przyciskiem myszy harmonogram zadań (lokalny) i wybierz polecenie Utwórz zadanie podstawowe.

4. Wprowadź nazwę nowego zadania (na przykład SQLBackup), a następnie wybierz przycisk Dalej.

5. Wybierz pozycję Codziennie dla wyzwalacza zadania, a następnie wybierz pozycję Dalej.

6. Ustaw cykl na jeden dzień, a następnie wybierz pozycję Dalej.

7. Wybierz pozycję Uruchom program jako akcję, a następnie wybierz pozycję Dalej.

8. Wybierz pozycję Przeglądaj, wybierz plik Sqlbackup.bat utworzony w kroku 3: Utwórz plik wsadowy Sqlbackup.bat, a następnie wybierz pozycję Otwórz.

9. Zaznacz pole wyboru Otwórz okno dialogowe Właściwości tego zadania po kliknięciu przycisku Zakończ, a następnie wybierz przycisk Zakończ.

10. Na karcie Ogólne przejrzyj opcje zabezpieczeń i potwierdź następujące elementy dla konta wymienionego w obszarze Podczas uruchamiania zadania użyj następującego konta użytkownika:

    • Konto ma co najmniej uprawnienia Odczyt i wykonywanie, aby uruchomić narzędzie sqlcmd.
    • Jeśli plik wsadowy używa uwierzytelniania Windows, konto ma uprawnienia do tworzenia kopii zapasowych baz danych SQL Server.
    • Jeśli plik wsadowy używa uwierzytelniania SQL Server, identyfikator logowania SQL Server w pliku wsadowym ma uprawnienia do tworzenia kopii zapasowych baz danych.

11. Dostosuj pozostałe ustawienia, aby odpowiadały Twoim wymaganiom, a następnie wybierz przycisk OK.

Aby uzyskać więcej informacji na temat opcji planowania, zobacz Harmonogram zadań.

Sprawdź, czy zaplanowana kopia zapasowa została uruchomiona

Po pierwszym zaplanowanym uruchomieniu upewnij się, że tworzenie kopii zapasowej zakończyło się pomyślnie:

• Sprawdź folder kopii zapasowej dla nowych plików .bak (pełnych i różnicowych kopii zapasowych) lub plików trn (kopie zapasowe dziennika transakcji). Procedura składowana dodaje znacznik daty i godziny do każdej nazwy pliku.

• W harmonogramie zadań wybierz pozycję Biblioteka harmonogramu zadań, wybierz zadanie i przejrzyj kolumnę Last Run Result (Wynik ostatniego uruchomienia ). Ta kolumna pokazuje kod zakończenia zwrócony przez plik wsadowy. Ponieważ przykłady używają -b opcji, wartość 0x1 oznacza, że polecenie kopii zapasowej nie powiodło się. Inne wartości można znaleźć w temacie Task Scheduler error and success constants (Błędy harmonogramu zadań i stałe powodzenia).

• Wykonaj zapytanie dotyczące historii kopii zapasowych w wystąpieniu:

  SELECT database_name, type, backup_start_date, backup_finish_date, physical_device_name
  FROM msdb.dbo.backupset AS bs
  INNER JOIN msdb.dbo.backupmediafamily AS bmf
      ON bs.media_set_id = bmf.media_set_id
  ORDER BY backup_start_date DESC;
  

  Te tabele są częścią historii kopii zapasowych, którą program SQL Server przechowuje w bazie danych msdb. Aby uzyskać więcej informacji, zobacz Historia kopii zapasowych i informacje nagłówkowe (SQL Server).

Wymagania i ograniczenia tej metody tworzenia kopii zapasowej

Podczas korzystania z procedury opisanej w tym artykule należy pamiętać o następujących wymaganiach i ograniczeniach:

• Usługa Harmonogram zadań musi być uruchomiona, gdy zadanie ma zostać uruchomione. Zalecamy ustawienie typu uruchamiania dla tej usługi na Wartość Automatyczna , aby usługa działa nawet po ponownym uruchomieniu.
• Dysk odbierający kopie zapasowe musi mieć wystarczającą ilość wolnego miejsca. Wyczyść stare pliki w folderze kopii zapasowej regularnie, aby nie zabrakło miejsca na dysku. Procedura sp_BackupDatabases składowana nie usuwa starych plików kopii zapasowej.
• Procedura składowana wykonuje kopię zapasową tylko baz danych, które są w trybie online. Pomija bez komunikatu bazy danych, które są offline, są w trakcie przywracania lub są z innego powodu niedostępne.
• Ta metoda nie zastępuje SQL Server Agent. Nie ma wbudowanej historii zadań, logiki ponawiania prób ani alertów o błędach. Regularnie przeglądaj historię kopii zapasowych lub uaktualnij ją do wersji zawierającej SQL Server Agent, jeśli potrzebujesz tych możliwości.

Treści powiązane

• Tworzenie pełnej kopii zapasowej bazy danych
• Przywracanie kopii zapasowej bazy danych przy użyciu programu SSMS
• Różnicowe kopie zapasowe (SQL Server)
• Kopie zapasowe dziennika transakcji (SQL Server)
• Omówienie kopii zapasowej (SQL Server)