Leniwce.com | blog technologiczny

Backup wszystkich baz na serwerze

Bartosz Lewandowski, 2009-05-01 23:20:20

backup wszystkich baz Niedawno musiałem zmierzyć się z problemem backupów baz MS SQL Servera. Problem polegał na tym, że ilość baz się zmieniała, więc nie mogłem na sztywno wykonać komendy backup database. Aby rozwiązać problem wykorzystałem mechanizm kursorów na bazie danych, dzięki któremu dla każdej bazy została wykonana wspomniana komenda backup database. Zanim jednak omówię ideę i działanie kursorów pokaże jak odczytać listę baz danych umieszczonych na serwerze SQL.

Jest kilka sposobów odczytania listy baz, zaprezentuje tu dwa, moim zdaniem najprostsze. Pierwszy to wykonanie zapytania na bazie:

SELECT * FROM sys.sysdatabases

Drugi natomiast to uruchomienie procedury storowanej:

EXEC sp_databases

Po uruchomieniu widać, że efekt jest podobny. Jednak ja chcę posłużyć się kursorem, a procedury storowanej bezośrednio do utworzenia kursora wykorzystać nie mogę, więc w dalszych rozważaniach będę korzystać z metody pierwszej.
Czym więc jest kursor?
Zapytanie SQL zwraca zestaw rekordów (tzw. recordset). Aby się po nim poruszać, standardowo wykorzystujemy narzędzia programistyczne, które na nim operują. Przykładowo, większość piszących w PHP spotkała się w pętlą odczytującą dane przez skrypt php, która może wyglądać tak:

while ($row = mysql_fetch_array($result))
{
 echo $row[0];
}

Zadaniem tego kodu jest przechodzenie do kolejnego rekordu zwróconego przez zapytanie SQL i wyświetlenie kolumny o indeksie zero. W innych językach programowanie znajdziemy inne metody, np. w dotNET obiekt DataReader, wygodny i szybki.
A gdy podobną funkcjonalność chcemy osiągnąć bez konieczności użycia zewnętrznych skryptów, tylko bezpośrednio na bazie danych, to wykorzystamy właśnie kursor.
Reasumując, kursor pobiera wyniki z recordseta i pozwala na poruszanie się po nim. W MS SQL utworzenie kursora wygląda tak:

declare [cursor name] cursor for [sql_query]

Gdzie [cursor name] to nazwa kursora, a w parametrze [sql_query] podajemy zapytanie SQL.
Należy pamiętać, że po zadeklarowaniu kursora, na końcu trzeba go zlikwidować, do tego służy ten kod:

deallocate [cursor name]

Po deklaracji kursora musimy go otworzyć, w tym celu wykonamy komendę:

open [cursor name]

Jeżeli kursor był otwarty, należy go zamknąć:

close [cursor name]

Czyli, po podstawieniu nazwy kursora i zapytania SQL, kod wygląda już tak

declare myCursor cursor for SELECT * FROM sys.sysdatabases
open myCursor
close myCursor
deallocate myCursor

No dobrze, kursor otwieramy i zamykamy, ale co z poruszaniem się po nim?
W tym celu wykorzystam komendę fetch, która pobiera rekord z kursora. Wraz z parametrem next i podanym kursorem będziemy się przesuwać do kolejnych wierszy. Aby sprawdzić, czy doszliśmy już do ostaniego wiersza wykorzystam @@fetch_status. Komenda @@fetch_status zwróci 0, gdy ostatnie wykonanie komendy fetch zakończyło się sukcesem (czyli wiersz został odczytany).
Skrypt nasz wygląda teraz tak:

fetch next from myCursor into @dbName
while @@fetch_status=0 
begin
  print @dbName
  fetch next from myCursor into @dbName
end

Umieściłem komendę print @dbName aby zobaczyć nazwy baz.
Czas na ostateczny skrypt, wyłączę bazy systemowe z backupu, w parametrze wywołania komendy backup database ustawię flagę init, co spowoduje każdorazowe nadpisanie backupu, usunę zbędny print:

declare myCursor cursor for SELECT name FROM sys.sysdatabases where name not in ('master','tempdb','model')
open myCursor
declare @dbName varchar(100)
fetch next from myCursor into @dbName
while @@fetch_status=0 
begin
  declare @dest varchar(200)
  set @dest='c:\backup\'+@dbName+'.bak'
  backup database @dbName to disk=@dest with init
  print @dbName
  fetch next from myCursor into @dbName
end
close myCursor
deallocate myCursor

Skrypt ten warto wstawić do zadań serwera, czyli jako Server Job. Pamiętać jednak należy, że musi być uruchomiona usługa SQL Server Agent aby wykonywane były zdania zdefiniowane jako „sql job”. Druga możliwość to uruchomienie osql.exe, programu, który pozwala na uruchamianie skryptów na bazie SQL. Warto wtedy utworzyć plik bat, który wykona osql.exe (sporo parametrów) i wstawić go do harmonogramu zadań. Najwygodniej, skrypt SQL tworzący backup zapisać w pliku (np. query.sql), a nasz bat wtedy wygląda tak:

@echo off
osql.exe -S (local) -E -i query.sql

-S – serwer
-E – autoryzacja NT
-i – plik z zapytaniem SQL
Oczywiście można bata rozbudować o opcje kopiowania backupów, czy ich kompresji.
Kończąc dodam, że warto zapoznać się z opcjami komendy backup database, tak aby nasz plan kopii zapasowych był optymalny. Można stosować backupy pełne i różnicowe, ale to już na inny wpis.

Powiązane artykuły

MS SQL Konwersja dat na liczby i odwrotnie (2009-04-22)

Komentarze

ICE [2010-08-30 08:53:38]

Stary umiliłeś mój dzień ...
Właśnie nad siedziałem i nie miałem pomysłu.
Wielkie dzięki
Masz u mnie piwo

Kategorie

Android (2)

C# (13)

Inne (6)

Java (3)

JavaScript/JQuery (1)

Matlab (1)

MS Sql Server (2)

OpenGL (1)

PHP (2)

Najnowsze wpisy

Ostatnie komentarze