Tekst alternatywny

Backup SQL z linii komend.

Napisano 31 maja 2015 | 8 komentarzy

sql

Omówię dzisiaj tworzenie backupów MSSQL (Microsoft SQL Server i SQL Express) za pomocą poleceni linii komend. Dodatkowo pokażę jak zarządzać powstałymi backupami poprzez pakowanie ich (zmniejszanie objętości) oraz kasowanie starych. Zapraszam do czytania.

W treści tego wpisu są umieszczone skryptu, linki do rozszerzonych skryptów z opisami, a także na samym dole film w którym omawiam te skrypt trochę dokładniej o pokazuję jak działają. Jeśli chcesz obejrzeć film zjedź na sam dół artykułu.

 

Backup SQL z linii komend.

Do zrobienia kopii zapasowej naszej bazy danych używamy polecenia „sqlcmd” które z odpowiednimi parametrami wysyła komendę/zapytanie do naszego serwera SQL i to faktycznie serwer SQL tworzy backup bazy danych którą mu wskażemy. Ważne w tym skrypcie są parametry które definiujemy za pomocą komendy ‚set’ przed uruchomieniem procedury backupu. Wcześniejsze zdefiniowanie stałych pozwala zachować ‚zrozumiałość’ dalszych poleceń i oczywiście umożliwiają wielokrotne ich użycie w dalszych częściach skryptu.

W tym przypadku używamy zmiennych:

BACKUPPATH – folder w którym tworzymy backupy

DATABASE – nazwa bazy danych

SERVERNAME – nazwa serwera/instancji SQL

DATESTAMP – znacznik daty i godziny potrzebny do oznaczenia kiedy był tworzony nasz plik backupu

FILENAME – pełna ścieżka wraz z nazwą gdzie ma być utworzony plik

Poniżej cały skrypt który wykonuje backup (oczywiście musicie odpowiednio uzupełnić zmienne)(rozszerzoną treść pliku .bat możecie pobrać również stąd http://wklej.org/id/1726540/):

echo off

:: DANE
set BACKUPPATH=H:\BACKUP_FOLDER
set DATABASE=WAPRO
set SERVERNAME=localhost
set DATESTAMP=%DATE%_%TIME:~0,2%-%TIME:~3,2%
:: data rrrr-mm-dd, time gg:mm:ss,ms
set FILENAME=%BACKUPPATH%\%DATABASE%_%DATESTAMP%.bak

sqlcmd -E -S %SERVERNAME% -d master -Q "BACKUP DATABASE [%DATABASE%] TO DISK = N'%FILENAME%' WITH INIT , NOUNLOAD , NAME = N'%DATABASE% backup', NOSKIP , STATS = 10, NOFORMAT"

 

 Pakowanie utworzonych plików backupu.

Bazy danych dość często zajmują dużo miejsca, a taki backup jak przed chwilą przedstawiłem nie jest w żaden sposób skompresowany. Dodatkowo jeśli kopie zapasowe wykonujemy codziennie to łatwo sobie policzyć kiedy skończy nam się miejsce. Poniżej widać ile miejsca zajmuje ta sama baza przed i po kompresji.

Backup SQL z linii komend

 

 

W załączonym poniżej skrypcie zastosowałem program 7-zip http://7-zip.org.pl/.

Definiuje w nim trzy stałe:

ZIPPATH – ścieżkę w której mam zainstalowany program 7-zip

FILESPATH – ścieżkę do plików z backupem

EXTENSION – rozszeżenie pliku jakie mają moje pliki z backupem

Sama ‚funkcja pakująca’ pliki wykonuje jednak trochę więcej operacji, w kolejności:

  1. za pomocą funkcji „dir” z parametrem „/b” otrzymujemy listę interesujących nas plików z zadanego katalogu, na których będziemy przeprowadzać kolejne operacje
  2. sprawdzamy czy dla konkretnego pliku było już robione archiwum, jeśli było to kasujemy je (to dla przypadku jeśli archiwom nie nadajemy różnych nazw)
  3. tworzymy spakowany plik z rozszerzeniem .7z
  4. usuwamy plik źródłowy .bak

A wszystko to za pomocą bardzo pomocnej pętli „for..in..do..” o której możemy poczytać w TECHNECIE.

echo off

::Sciezki
set ZIPPATH=C:\Program Files\7-Zip
set FILESPATH=H:\BACKUP_FOLDER

::Rozszerzenie pliku
set EXTENSION=bak

for /f %%F in ('dir %FILESPATH%\*.%EXTENSION% /b') do del %FILESPATH%\%%F.7z & "%ZIPPATH%\7z.exe" a %FILESPATH%\%%F.7z %FILESPATH%\%%F & del %FILESPATH%\%%F

Cały skrypt można pobrać tutaj: http://wklej.org/id/1726573/.

 Usuwanie starych plików backupu.

Zawsze nadchodzi taki moment w którym plików z backupem narobiło nam się mnóstwo i trzeba część usunąć i do tego posłuży nam kolejny skrypt.

W tym przypadku mogę przedstawić wam dwie metody:

  1. Użycie komendy „forfiles”, która umożliwia nam wykonanie komendy (w tym przypadku kasowania) na liście wyselekcjonowanych plików. Przy pomocy parametru „/d -7” wybierzemy pliki starsze niż 7 dni(oczywiście można to dowolnie zmienić) a następnie wykonamy na nich kasowanie. Przy użyciu tej opcji należy się upewnić, że backupy robią się na bieżąco bo może się okazać, że wszystkie są starsze niż zadane 7 dni i je skasujemy.
  2. Użycie pętli „for” o której już wcześniej pisałem. Tym razem jednak użyjemy „dir” z parametrem „/o-d” dzięki któremu otrzymamy listę plików posegregowaną od najmłodszego do najstarszego, a w funkcji „for” użyjemy parametru „skip=5” czyli pominiemy z naszej selekcji pierwsze pięć plików (czyli najmłodszych backupów). Wynikiem tego usuniemy wszystkie pliki poza najmłodszymi pięcioma.

 

Cały skrypt możemy ściągnąć tutaj: http://wklej.org/id/1726589/.

echo off
::DANE
set DIRECTORY=H:\BACKUP_FOLDER

::METODA 1: Wyszukiwanie po dacie (usuwa starsze niż 7 dni)
:: forfiles /p %DIRECTORY% /d -7 /c "cmd /c del @path"

::METODA 2: zostawia 5 najmłodszych backupów:
:: for /f "skip=5 delims=" %%F in ('dir %DIRECTORY%\*.* /b/o-d') do del %DIRECTORY%\%%F

Pełen skrypt łączący wszystkie funkcje.

Poniżej umieszczam skrypt który wykonuje wszystkie opisane powyżej funkcje, robi backup, pakuje go, a na końcu usuwa stare pliki. Można go także pobrać stąd http://wklej.org/id/1726600/

:: Tomasz Zdziech
:: agron@helpdeski.pl

echo off

:: DANE
set BACKUPPATH=H:\BACKUP_FOLDER
set DATABASE=WAPRO
set SERVERNAME=localhost
set DATESTAMP=%DATE%_%TIME:~0,2%-%TIME:~3,2%
:: data rrrr-mm-dd, time gg:mm:ss,ms
set FILENAME=%BACKUPPATH%\%DATABASE%_%DATESTAMP%.bak
set ZIPPATH=C:\Program Files\7-Zip
::Rozszerzenie pliku
set EXTENSION=bak


:: -----------Backup bazy danych----------
sqlcmd -E -S %SERVERNAME% -d master -Q "BACKUP DATABASE [%DATABASE%] TO DISK = N'%FILENAME%' WITH INIT , NOUNLOAD , NAME = N'%DATABASE% backup', NOSKIP , STATS = 10, NOFORMAT"

:: -----------Pakowanie plików i usuwanie-----------
for /f %%F in ('dir %BACKUPPATH%\*.%EXTENSION% /b') do del %BACKUPPATH%\%%F.7z & "%ZIPPATH%\7z.exe" a %BACKUPPATH%\%%F.7z %BACKUPPATH%\%%F & del %BACKUPPATH%\%%F


::-----------Usuwanie starych plików-----------
:: Wyszukiwanie po dacie (usuwa starsze niż 7 dni)
forfiles /p %BACKUPPATH% /d -7 /c "cmd /c del @path"

:: usuwa najstarsze pliki (pozostawia 7 najmłodszych backupów)
for /f "skip=7 delims=" %%F in ('dir %BACKUPPATH%\*.* /b/o-d') do del %BACKUPPATH%\%%F

 

Zapraszam też do obejrzenia filmu w którym pokazuje działanie ww. skryptów.

 

Jeżeli Ci pomogłem udostępnij: Share on Facebook1Share on Google+0Tweet about this on TwitterPin on Pinterest0Share on LinkedIn0Print this page
8 Comments on “Backup SQL z linii komend.
  • Gratuluję finezji. Mój skrypt, nad którym się przecież nabiedziłem, jest znacznie prostszy (w zasadzie tylko robi backup, pakuje i wysyła mailem info).

    Ale chciałbym się podzielić moim świeżym odkryciem z netu : sqlbackupandftp.com
    Jest tam do ściągnięcia programik SgkBakSetupAzure.exe, który ma wszystkie niezbędne funkcje: backupy baz SQL (w tym pełne, różnicowe i przyrostowe), pakowanie backupów, kopiowanie ich do wskazanych lokalizacji, wysyłka za pomoca ftp, powiadomienie administratora mailem o sukcesie bądź porażce, harmonogramy wykonywania backupów, usuwanie starych kopii, współpraca z Dropboxem.
    Te wszystkie funkcje są dostępne w wersji free, która ma ograniczenie do 2 baz danych. W wersjach pełnych (niezbyt drogich) oprócz większej liczby obsługiwanych baz jest dodatkowo obsługa Google Drive lub OneDrive, obsługa FTPS i SFTP oraz szyfrowanie AES. Obsługa bardzo prosta, polecam.

  • W pliku lista.txt umieszczasz nazwy baz danych jedna pod drugą (bez spacji, ani innych znaków).
    To jest mój działający skrypt

    for /f „tokens=*” %%i in (‚type C:\backup_lib\lista.txt 2^>NUL’) do call :BACKUP %%i

    :BACKUP
    :: DANE
    set BACKUPPATH=D:\SQL_BACKUP
    set DATABASE=%1
    set SERVERNAME=localhost
    set DATESTAMP=%DATE%_%TIME:~0,2%-%TIME:~3,2%
    :: data rrrr-mm-dd, time gg:mm:ss,ms
    set FILENAME=%BACKUPPATH%\%DATABASE%_%DATESTAMP%.bak
    set ZIPPATH=C:\backup_lib
    ::Rozszerzenie pliku
    set EXTENSION=bak

    :: ———–Backup bazy danych———-
    sqlcmd -E -S %SERVERNAME% -d master -Q „BACKUP DATABASE [%DATABASE%] TO DISK = N’%FILENAME%’ WITH INIT , NOUNLOAD , NAME = N’%DATABASE% backup’, NOSKIP , STATS = 10, NOFORMAT”

    :: ———–Pakowanie plików i usuwanie———–
    for /f %%F in (‚dir %BACKUPPATH%\*.%EXTENSION% /b’) do del %BACKUPPATH%\%%F.7z & „%ZIPPATH%\7z.exe” a %BACKUPPATH%\%%F.7z %BACKUPPATH%\%%F*.%EXTENSION% & del %BACKUPPATH%\%%F*.%EXTENSION%

    ::———–Usuwanie starych plików———–
    :: Wyszukiwanie po dacie (usuwa starsze niż 7 dni)
    forfiles /p %BACKUPPATH% /d -7 /c „cmd /c del @path”

    :: usuwa najstarsze pliki (pozostawia 7 najmłodszych backupów)
    for /f „skip=7 delims=” %%F in (‚dir %BACKUPPATH%\*.* /b/o-d’) do del %BACKUPPATH%\%%F

    • Mam w pliku Lista_baz.txt wpisane 12 baz (bez spacji, pustych linii) i po utworzeniu baz wywala mi taki komunikat
      Pakowanie i usuwanie mam wycięte z skryptu.

      Msg 1038, Level 15, State 4, Server SERVER-PC\SQLEXPRESS, Line 1
      An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as „” or [] are not allowed. Change the alias to a valid name.

      Pomoże ktoś mi dlaczego tak się dzieje

  • Witam
    Jak w ostatnim poleceniu” :: usuwa najstarsze pliki (pozostawia 7 najmłodszych backupów)”, czyli usuwanie starych plików pozbyć sie pytania czy kasować y/n. Chciałbym aby zawsze kasował najstarszy a nie pytał się czy to zrobić.

  • Witam
    problem rozwiązany, kto szuka ten znajdzie :), wystarczy zamiast del dać rmdir /s/q i katalogi się kasują z plikami w środku bez pytania . A oto co dodałem od siebie jeszcze do tego skryptu może ktoś bedzie chcał. Mam do kopiowania kilka baz i chciałem aby pliki z danego dnia były w katalogu z datą co kopiowane bazy. Ułatwia mi to wgląd w kopie. Mam z tygodnia 7 katalogów w nazwie z datą z kopiami baz w katalogu , a nie 25 plików w jednym katalogu kopia_baz. Oto komenda wklejona między „pakowanie plików i usuwanie”, a „usuwanie starych plików”

    ::————Tworzenie katalogu i wrzucanie plików do katalogu z datą ———————
    md %BACKUPPATH%\”kopia_z_dnia_%DATESTAMP%”
    move %BACKUPPATH%\*.* %BACKUPPATH%\kopia_z_dnia_%DATESTAMP%

Dodaj komentarz

Przeczytaj poprzedni wpis:
Jak wstawić zdjęcie do korespondencji seryjnej Word.

Zapraszam do obejrzenia kolejnego filmu z poradami, tym razem jak wstawić obrazy do korespondencji seryjnej w Word.

Zamknij