Kopia zapasowa bazy danych SQL Server to jeden z podstawowych elementów bezpieczeństwa danych w każdej firmie korzystającej z systemów informatycznych. Awaria serwera, uszkodzenie dysku, błąd użytkownika czy problem z oprogramowaniem może w kilka chwil doprowadzić do utraty danych, które są często niezbędne do codziennego funkcjonowania przedsiębiorstwa.
W tym poradniku pokażę, jak skonfigurować automatyczną kopię zapasową baz danych SQL Server w systemie Windows. Do wykonania zadania wykorzystamy SQL Server Management Studio, narzędzie sqlcmd oraz Harmonogram zadań Windows.
Rozwiązanie jest szczególnie przydatne w przypadku SQL Server Express, który nie posiada komponentu SQL Server Agent odpowiedzialnego za automatyczne wykonywanie zadań. Microsoft jako jedno z rozwiązań wskazuje właśnie połączenie skryptu Transact-SQL z Harmonogramem zadań Windows.
W przykładzie przygotujemy własny skrypt, który przed wykonaniem kopii usuwa poprzedni katalog backupu, tworzy go ponownie, wykonuje kopie wszystkich baz użytkownika oraz zapisuje informacje o przebiegu operacji w pliku logu.
SQL Server i kopie zapasowe
SQL Server może wykonywać kopie zapasowe baz danych bez instalowania dodatkowego oprogramowania do samego wykonywania backupu. Problem pojawia się wtedy, gdy chcemy, aby kopia wykonywała się automatycznie, na przykład codziennie w godzinach nocnych.
W przypadku pełnych wersji SQL Server automatyzację można realizować między innymi przy wykorzystaniu SQL Server Agent. W SQL Server Express ten komponent nie jest dostępny. Dlatego w takim przypadku dobrym rozwiązaniem jest wykorzystanie programu sqlcmd oraz systemowego Harmonogramu zadań Windows.
Cały mechanizm będzie wyglądał następująco:
SQL Server → skrypt T-SQL → sqlcmd → plik BAT → Harmonogram zadań Windows
Dzięki temu nie musimy każdego dnia ręcznie uruchamiać kopii zapasowej.
Instalacja SQL Server Management Studio
Na początku potrzebujemy programu SQL Server Management Studio, czyli popularnego SSMS. Jest to graficzne narzędzie firmy Microsoft pozwalające między innymi na zarządzanie serwerem SQL, bazami danych, użytkownikami oraz wykonywanie zapytań SQL.
Jeżeli SQL Server Management Studio nie jest jeszcze zainstalowane na komputerze, należy pobrać aktualną wersję z oficjalnej strony Microsoftu i przeprowadzić standardową instalację.
https://learn.microsoft.com/pl-pl/ssms/install/install
Po uruchomieniu programu przechodzimy do okna połączenia z serwerem SQL.
Połączenie z serwerem SQL
W oknie połączenia wybieramy odpowiednią instancję SQL Server.
Jeżeli pracujemy bezpośrednio na komputerze, na którym znajduje się SQL Server Express, często będzie to instancja:
.\SQLEXPRESS
W polu Uwierzytelnianie możemy wybrać Uwierzytelnianie systemu Windows. W takim przypadku SQL Server wykorzysta konto Windows, na którym jesteśmy zalogowani.
Możliwe jest również wykorzystanie uwierzytelniania SQL Server, jeżeli w danym środowisku zostało ono skonfigurowane.
Jeżeli łączymy się z SQL Server z innego komputera w sieci, sposób uwierzytelniania oraz konfiguracja dostępu zależą od konkretnego środowiska. Przy wykorzystaniu Windows Authentication konto musi posiadać odpowiednie uprawnienia na serwerze SQL.
Po wybraniu odpowiednich ustawień klikamy Połącz.
Utworzenie zapytania SQL
Po poprawnym połączeniu z serwerem SQL w SQL Server Management Studio przechodzimy do opcji Nowe zapytanie.
Otworzy się edytor, w którym możemy wykonywać polecenia Transact-SQL.
Microsoft udostępnia gotowy przykład skryptu przeznaczonego do automatyzacji kopii zapasowych SQL Server Express. W ramach tego rozwiązania tworzona jest procedura sp_BackupDatabases, którą później możemy uruchamiać za pomocą programu sqlcmd.
Aktualny skrypt Microsoftu można znaleźć tutaj:
SQL_Express_Backups.sql na GitHub
Skrypt należy wkleić do okna nowego zapytania w SQL Server Management Studio i wykonać.
W ten sposób przygotowujemy po stronie SQL Server procedurę, którą później będzie mógł uruchamiać nasz plik BAT.
Czym jest sqlcmd?
Kolejnym elementem jest sqlcmd. To narzędzie wiersza poleceń firmy Microsoft, które pozwala wykonywać polecenia Transact-SQL, procedury oraz całe skrypty bez konieczności otwierania SQL Server Management Studio.
Jest to bardzo przydatne przy automatyzacji. Nasz skrypt BAT może uruchomić sqlcmd, a sqlcmd połączy się z SQL Server i wykona polecenie odpowiedzialne za utworzenie kopii zapasowej. Dokumentacja Microsoftu wskazuje, że sqlcmd może być wykorzystywany między innymi z poziomu wiersza poleceń oraz skryptów systemu Windows.
Warto wiedzieć, że obecnie występują dwie odmiany narzędzia sqlcmd. Microsoft opisuje sqlcmd (Go) jako samodzielne narzędzie, które można pobrać niezależnie od SQL Server. Dostępna jest również wersja sqlcmd (ODBC), która jest związana z SQL Server lub Microsoft Command Line Utilities.
Microsoft informuje również, że od SQL Server 2016 sqlcmd jest oferowane jako osobne narzędzie.
Sprawdzenie, czy sqlcmd jest zainstalowane
Zanim zaczniemy instalować dodatkowe oprogramowanie, warto sprawdzić, czy sqlcmd jest już dostępne w systemie.
Uruchamiamy Wiersz polecenia Windows, czyli CMD, i wpisujemy:
sqlcmd -?
Jeżeli narzędzie jest prawidłowo zainstalowane i znajduje się w zmiennej PATH, zobaczymy ekran pomocy programu.
W przypadku aktualnego sqlcmd (Go) parametr -? jest dostępny między innymi dla zachowania zgodności ze starszą składnią. Microsoft opisuje również opcję --version, która pozwala sprawdzić wersję zainstalowanego narzędzia.
Możemy więc również użyć:
sqlcmd --version
Jeżeli system nie rozpoznaje polecenia sqlcmd, należy zainstalować odpowiednią wersję narzędzia.
Aktualne wydania sqlcmd (Go) można pobrać z oficjalnego repozytorium Microsoft:
Po instalacji i restarcie serwera warto ponownie otworzyć CMD i sprawdzić:
sqlcmd -?
Pierwszy prosty skrypt do wykonywania kopii
Kiedy mamy już przygotowaną procedurę sp_BackupDatabases oraz działające sqlcmd, możemy stworzyć pierwszy skrypt BAT.
Otwieramy Notatnik i wklejamy:
@echo off
sqlcmd -S .\SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='C:\SQLBackups\', @backupType='F'"
exit /b %ERRORLEVEL%Plik zapisujemy na przykład jako:
SQLBackup.bat
Warto zwrócić uwagę na poszczególne parametry.
-S .\SQLEXPRESS wskazuje instancję SQL Server, z którą chcemy się połączyć.
-E oznacza wykorzystanie uwierzytelniania Windows.
-Q pozwala wykonać podane zapytanie i zakończyć działanie programu sqlcmd.
W tym przykładzie uruchamiana jest procedura sp_BackupDatabases, która wykonuje pełną kopię baz danych do wskazanego katalogu.
Microsoft wykorzystuje bardzo podobny mechanizm w swoim poradniku dotyczącym automatyzacji backupów SQL Server Express.
Własny skrypt do automatycznej kopii SQL Server
Podstawowy przykład Microsoftu możemy rozbudować i dostosować do własnych potrzeb.
W poniższym przykładzie skrypt przed wykonaniem kopii usuwa istniejący katalog backupu, tworzy go ponownie, sprawdza dostępność programu sqlcmd, wykonuje pełne kopie baz użytkownika oraz zapisuje informacje o wykonaniu operacji w pliku logu.
Dzięki temu po każdym uruchomieniu otrzymujemy świeży zestaw kopii baz danych.
Przykładowy skrypt:
@echo off
setlocal EnableExtensions
REM ============================================================
REM KONFIGURACJA
REM ============================================================
REM Nazwa instancji SQL Server
set "SQLSERVER=.\SQLEXPRESS"
REM Katalog, w którym będą przechowywane kopie zapasowe
set "BACKUPDIR=C:\SQLBackups"
REM Plik logu
set "LOGFILE=%BACKUPDIR%\SQLBackup.log"
REM ============================================================
REM INFORMACJA
REM ============================================================
echo ============================================================
echo AUTOMATYCZNA KOPIA ZAPASOWA SQL SERVER
echo ============================================================
echo Instancja SQL Server: %SQLSERVER%
echo Katalog kopii: %BACKUPDIR%
echo.
REM ============================================================
REM USUNIĘCIE STAREGO KATALOGU
REM ============================================================
echo Usuwanie poprzedniego katalogu kopii...
if exist "%BACKUPDIR%" (
rmdir /S /Q "%BACKUPDIR%"
if exist "%BACKUPDIR%" (
echo BLAD: Nie mozna usunac katalogu %BACKUPDIR%
exit /b 1
)
)
REM ============================================================
REM UTWORZENIE KATALOGU
REM ============================================================
echo Tworzenie katalogu kopii...
mkdir "%BACKUPDIR%"
if not exist "%BACKUPDIR%" (
echo BLAD: Nie mozna utworzyc katalogu %BACKUPDIR%
exit /b 1
)
REM ============================================================
REM SPRAWDZENIE SQLCMD
REM ============================================================
echo Sprawdzanie programu sqlcmd...
where sqlcmd >nul 2>&1
if %ERRORLEVEL% NEQ 0 (
echo BLAD: Nie znaleziono programu sqlcmd.
echo Zainstaluj narzedzie sqlcmd i upewnij sie, ze znajduje sie w PATH.
exit /b 1
)
REM ============================================================
REM UTWORZENIE PLIKU LOGU
REM ============================================================
echo [%date% %time%] Rozpoczecie wykonywania kopii zapasowej. > "%LOGFILE%"
echo [%date% %time%] Instancja: %SQLSERVER% >> "%LOGFILE%"
echo [%date% %time%] Katalog: %BACKUPDIR% >> "%LOGFILE%"
echo. >> "%LOGFILE%"
REM ============================================================
REM WYKONANIE KOPII BAZ DANYCH
REM ============================================================
echo Rozpoczynam wykonywanie kopii...
echo.
sqlcmd ^
-S "%SQLSERVER%" ^
-E ^
-b ^
-Q "DECLARE @name NVARCHAR(128); DECLARE @file NVARCHAR(4000); DECLARE db_cursor CURSOR FOR SELECT name FROM sys.databases WHERE database_id > 4 AND state = 0; OPEN db_cursor; FETCH NEXT FROM db_cursor INTO @name; WHILE @@FETCH_STATUS = 0 BEGIN SET @file = N'%BACKUPDIR%\'+@name+N'.bak'; PRINT 'Backup bazy: '+@name; BACKUP DATABASE @name TO DISK = @file WITH INIT, CHECKSUM, STATS = 10; FETCH NEXT FROM db_cursor INTO @name; END; CLOSE db_cursor; DEALLOCATE db_cursor;" >> "%LOGFILE%" 2>&1
REM ============================================================
REM SPRAWDZENIE WYNIKU
REM ============================================================
if %ERRORLEVEL% NEQ 0 (
echo.
echo ============================================================
echo BLAD PODCZAS WYKONYWANIA KOPII!
echo ============================================================
echo Szczegoly znajduja sie w:
echo %LOGFILE%
exit /b 1
)
REM ============================================================
REM ZAKONCZENIE
REM ============================================================
echo.
echo ============================================================
echo KOPIA ZAPASOWA ZAKONCZONA POMYSLNIE
echo ============================================================
echo.
echo [%date% %time%] Kopia zakonczona pomyslnie. >> "%LOGFILE%"
exit /b 0Co robi przygotowany skrypt?
Na początku definiujemy podstawowe parametry. W tym przypadku są to nazwa instancji SQL Server oraz lokalizacja, w której mają zostać zapisane kopie.
set "SQLSERVER=.\SQLEXPRESS"
set "BACKUPDIR=C:\SQLBackups"Jeżeli korzystamy z innej instancji SQL Server, wystarczy zmienić pierwszą wartość.
Jeżeli kopie mają być zapisywane na innym dysku, zmieniamy drugą wartość.
Przed wykonaniem backupu skrypt usuwa istniejący katalog:
rmdir /S /Q "%BACKUPDIR%"Następnie tworzy go ponownie.
Jest to rozwiązanie celowo przygotowane w taki sposób, aby w katalogu znajdowała się zawsze aktualna kopia baz danych.
Następnie skrypt sprawdza, czy system może odnaleźć program sqlcmd.
Jeżeli wszystko jest prawidłowo skonfigurowane, skrypt pobiera z SQL Server listę baz użytkownika i wykonuje dla nich pełne kopie zapasowe.
Bazy systemowe, takie jak master, model, msdb oraz tempdb, są pomijane.
Do wykonywania backupu wykorzystujemy polecenie:
BACKUP DATABASEoraz opcje:
WITH INIT, CHECKSUM, STATS = 10INIT powoduje zastąpienie zawartości wskazanego pliku backupu.
CHECKSUM pozwala SQL Server wykonywać dodatkową kontrolę podczas operacji backupu.
STATS = 10 powoduje wyświetlanie informacji o postępie wykonywania kopii.
Warto tutaj zwrócić uwagę, że w przypadku SQL Server Express nie należy dodawać do tego przykładu opcji COMPRESSION, jeżeli dana wersja i edycja SQL Server jej nie obsługuje. Skrypt powinien być zawsze dostosowany do konkretnej wersji oraz edycji SQL Server znajdującej się na serwerze.
Po zakończeniu operacji informacje są zapisywane w pliku:
SQLBackup.log
Dzięki temu w przypadku problemu możemy sprawdzić, co wydarzyło się podczas wykonywania kopii.
Uwaga przed uruchomieniem skryptu
Ten przykład pokazuje, jak można technicznie zautomatyzować wykonywanie kopii SQL Server, ale przed zastosowaniem go na produkcyjnym serwerze trzeba dokładnie sprawdzić wszystkie ustawienia.
Szczególnie ważna jest zmienna:
set "BACKUPDIR=C:\SQLBackups"Skrypt usuwa wskazany katalog wraz z jego zawartością.
Jeżeli przez pomyłkę wpiszemy nieprawidłową ścieżkę, możemy doprowadzić do usunięcia niewłaściwych danych. Z tego powodu przed uruchomieniem skryptu warto wykonać test na środowisku testowym oraz dokładnie sprawdzić każdą ścieżkę.
Nie należy również przechowywać jedynej kopii danych na tym samym dysku fizycznym, na którym znajduje się baza SQL Server. Awaria dysku może wtedy spowodować jednoczesną utratę bazy oraz backupu.
W praktycznym środowisku firmowym kopię warto dodatkowo przesyłać na drugi serwer, NAS lub do odpowiednio zabezpieczonej chmury.
Testowanie skryptu
Zanim dodamy skrypt do Harmonogramu zadań Windows, należy uruchomić go ręcznie.
Klikamy plik SQLBackup.bat i obserwujemy jego działanie.
Po zakończeniu sprawdzamy, czy w katalogu:
C:\SQLBackups
znalazły się pliki .bak.
Powinniśmy również otworzyć plik:
SQLBackup.log
i sprawdzić, czy SQL Server nie zgłosił żadnego błędu.
Microsoft zaleca również przetestowanie pliku BAT z wiersza poleceń uruchomionego przy użyciu tego samego konta użytkownika, które będzie później właścicielem zadania w Harmonogramie zadań.
Samo pojawienie się pliku .bak nie powinno być traktowane jako pełne potwierdzenie poprawności kopii. Warto okresowo wykonać próbne odtworzenie bazy na osobnym środowisku testowym.
Automatyczne uruchamianie backupu za pomocą Harmonogramu zadań
Jeżeli ręczne uruchomienie skryptu zakończyło się powodzeniem, możemy przejść do automatyzacji.
W Windows otwieramy Harmonogram zadań.
Aby zachować porządek, możemy utworzyć osobny folder na nasze skrypty.
W lewej części okna klikamy prawym przyciskiem myszy Biblioteka Harmonogramu zadań, wybieramy Nowy folder i nadajemy mu nazwę, na przykład:
Skrypty
Następnie wchodzimy do utworzonego folderu i w środkowej części okna klikamy prawym przyciskiem myszy, a następnie wybieramy Utwórz zadanie.
W polu Nazwa wpisujemy na przykład:
Automatyczna kopia SQL Server
Warto zaznaczyć opcję:
Uruchom niezależnie od tego, czy użytkownik jest zalogowany
oraz:
Uruchom z najwyższymi uprawnieniami
W przypadku wykorzystania autoryzacji kontem Windows należy pamiętać, że zadanie będzie wykonywane z uprawnieniami konkretnego konta Windows. Konto to musi mieć odpowiednie uprawnienia do wykonania backupu SQL Server oraz możliwość uruchomienia programu sqlcmd. Microsoft zwraca na to uwagę w dokumentacji dotyczącej automatyzacji backupów SQL Server Express.
Ustawienie harmonogramu wykonywania kopii
Przechodzimy do zakładki Wyzwalacze i klikamy Nowy.
Tutaj możemy zdecydować, kiedy ma być uruchamiany skrypt.
W przypadku codziennego backupu możemy wybrać opcję Codziennie i ustawić na przykład godzinę 21:00.
Na dole okna upewniamy się, że zaznaczona jest opcja Włączono.
Częstotliwość wykonywania kopii należy dopasować do charakteru firmy oraz ilości danych, które mogą zostać utracone w przypadku awarii.
Dla niektórych firm wystarczająca będzie jedna kopia dziennie. W przypadku systemów, w których dane zmieniają się bardzo często, konieczne może być wykonywanie backupów znacznie częściej oraz zastosowanie dodatkowych mechanizmów ochrony danych.
Dodanie skryptu BAT do zadania
Przechodzimy do zakładki Akcje i klikamy Nowa.
W polu Akcja pozostawiamy:
Uruchom program
W polu Program/skrypt wskazujemy przygotowany wcześniej plik:
SQLBackup.bat
Następnie zatwierdzamy konfigurację przyciskiem OK.
Od tej chwili Windows będzie uruchamiał przygotowany skrypt zgodnie z ustalonym harmonogramem.
Warto wykonać test zadania
Po skonfigurowaniu Harmonogramu zadań nie warto czekać do następnego dnia, aby sprawdzić, czy wszystko działa.
Najlepiej kliknąć na utworzone zadanie prawym przyciskiem myszy i wybrać Uruchom.
Następnie sprawdzamy katalog backupu oraz plik logu.
Powinniśmy zobaczyć utworzone pliki .bak odpowiadające bazom użytkownika znajdującym się na serwerze SQL.
Jeżeli zadanie zakończyło się poprawnie, możemy również sprawdzić historię wykonania zadania w Harmonogramie zadań.
Backup SQL Server to nie wszystko
Samo utworzenie kopii zapasowej nie oznacza jeszcze, że dane są odpowiednio zabezpieczone.
Jeżeli wszystkie pliki znajdują się na tym samym serwerze co baza SQL Server, awaria serwera, uszkodzenie dysku, ransomware lub przypadkowe usunięcie danych może spowodować utratę zarówno danych produkcyjnych, jak i kopii.
Dlatego w firmowej infrastrukturze IT warto zastosować kilka niezależnych poziomów zabezpieczenia.
Kopia może być przechowywana na osobnym serwerze plików lub NAS, a dodatkowo kolejna kopia może być wysyłana do zewnętrznej lokalizacji lub chmury.
Warto również okresowo sprawdzać możliwość odtworzenia danych. Backup, którego nigdy nie próbowaliśmy odtworzyć, nie daje pełnej pewności, że w sytuacji awaryjnej odzyskamy dane.
Podsumowanie
Automatyzacja kopii zapasowych SQL Server nie musi wymagać skomplikowanego oprogramowania. W przypadku SQL Server Express możemy wykorzystać SQL Server Management Studio do przygotowania środowiska, sqlcmd do wykonywania poleceń SQL oraz Harmonogram zadań Windows do automatycznego uruchamiania przygotowanego skryptu.
Najważniejszym elementem całego rozwiązania nie jest jednak sam skrypt, ale odpowiednio zaprojektowana strategia backupu.
Kopia powinna być wykonywana automatycznie, regularnie kontrolowana i przechowywana w miejscu niezależnym od podstawowego serwera. Od czasu do czasu warto również wykonać próbne odtworzenie bazy, aby upewnić się, że w przypadku awarii dane rzeczywiście będzie można odzyskać.
W EIGHTBIT Informatyka zajmujemy się między innymi obsługą IT, administracją serwerami, bezpieczeństwem danych oraz projektowaniem i wdrażaniem systemów kopii zapasowych dla firm. Prawidłowo zaprojektowany backup SQL Server jest jednym z podstawowych elementów bezpieczeństwa infrastruktury informatycznej przedsiębiorstwa.