CREATE DATABASE AS SNAPSHOT – Generowanie prostego skryptu tworzącego snapshot bieżącej bazy

Michał Pawlikowski

PROBLEM

Mam bazy, które chcę „zesnapshotować”. Ale żeby stworzyć snapshota muszę sprawdzić na jakich plikach danych jest ona oparta, użyć ich logicznych nazw i zmapować ich kopię z plikami (czyli tam, gdzie będą odkładane dane przed zmianą na oryginale). Management studio nie dostarcza żadnego gotowego rozwiązania, więc czas przygotować swoje.

 

 

DO CZEGO POTRZEBNY MI SNAPSHOT?

Stworzenie snapshota generuje nową bazę, która reprezentuje jej kompletną postać na moment wykonania tej czynności, bez względu na ilość zmian, które po niej wystąpiły. Na bazie źródłowej SQL może wykonywać dowolne operacje na tabelach, procedurach i innych obiektach (UPDATE, DELETE, DROP itd.). Kopia stron ze starymi danymi, przed ich nadpisaniem (np. poleceniem UPDATE), jest przenoszona do plików snapshotowych, w postaci zrzutu tzw. extentów, czyli zbioru ośmiu stron z danymi. Zasady jakimi się to rządzi szczegółowo opisane są w tym miejscu. Dla nas istotny jest fakt, że taki snapshot działa mniej więcej jak każda inna baza w trybie do odczytu i pokazuje nasze stare dane 🙂 . Oszczędzając przy tym miejsce, choć oczywiście wpływa to bezpośrednio na wydajność bazy źródłowej.

  • Funkcja niezwykle pomocna, szczególnie dla mnie jako developera, który może prostym poleceniem (innym niż backup) zachować postać bazy (strukturę, dane, uprawnienia itd.), a po zmianach ją zwyczajnie przyrównać do oryginału (Redgate [Data] Compare, tęsknie za Tobą! chlip ;( )
  • Mało tego, mogę przywrócić oryginalną postać bazy do wersji zawartej w snapshocie, gdzie nanoszone są tylko zmienione strony. Stare nie są nadpisywane 🙂 Łatwo sobie wyobrazić jak wiele czasu i nerwów zabrałoby wykonanie backupu i odtworzenie go, jeśli dokonaliśmy zmian wielkości kilkunastu MB na kilkuset gigabajtowej bazie. Niestety możliwe jest to tylko wtedy, gdy baza nie posiada aktywnego FILESTREAM lub tabel in-memory (czyli tych stworzonych przy pomocy MEMORY_OPTIMIZED = ON).

O snapshotach będę wrzucał jeszcze więcej informacji. Jest to temat zdecydowanie wart omówienia. Rozwiązanie ma wiele zalet, są też wady 🙂 . Oczywiście kilka warunków musi być spełnionych, niektóre mogą okazać się niemożliwe do spełnienia (choćby wymóg wersji enterprise).

 

WYMAGANIA SKRYPTU

  • Skrypt można uruchomić na wszystkich wersjach SQL Server od 2005, jednakże wygenerowane przez niego polecenia zadziałają dopiero w wersjach implementujących funkcjonalności Enterprise,
  • muszą być spełnione wszystkie inne warunki (a jest ich dużo…) wynikające z technicznych ograniczeń opisanych szerzej TUTAJ.

 

 

PRZYKŁADOWY WYNIK DZIAŁANIA

Uruchomienie skryptu na bazie SSISDB przy def. parametrach zwraca w oknie Messages:

snpasht_create

 

SKRYPT

 

/*****************\
|    PARAMETRY    |
\*****************/

DECLARE @SnapshotPostfix SYSNAME= '_SNAP'; --suffix nazwy snapshota, np. dla bazy DUPA i @SnapshotPostfix = '_SNAP' nazwa snaphota będzie: 'DUPA_SNAP'

DECLARE @SnapshotFolderPath VARCHAR(MAX)= 'C:\SQL\SNAPSHOTS\'; --scieżka do folderu, gdzie ma być utworzony snapshot (koniecznie zakończona backslashem \)

DECLARE @SnpashotFileExtention VARCHAR(3)= 'ss'; --roszerzenie plliku, który powstanie w wyniku stworzenia snapshota


/*****************\
|       KOD       |
\*****************/

DECLARE @DBFiles NVARCHAR(MAX)= '', @SQL NVARCHAR(MAX)= '';
DECLARE @nl NCHAR(2)= CHAR(13) + CHAR(10), @tab NCHAR(1)= CHAR(9); --definicja nowej linii i tabulatorad do formatowania kodu

SELECT @DBFiles+=CASE
                     WHEN @DBFiles = ''
                     THEN ''
                     ELSE ','+@nl
                 END+'(' -- deklaracja plikow:
       +@nl+@tab+'NAME = N'+QUOTENAME([name], '''')+',' -- NAME = N'nazwa_pliku',
       +@nl+@tab+'FILENAME = N'+QUOTENAME(@SnapshotFolderPath+[name]+'.'+@SnpashotFileExtention, '''') -- FILENAME = N'[@sciezka]\[nazwa_pliku].[@roszerzenie]'
       +@nl+')' --koniec deklaracji plików
--NAME = N'tmp',
--  FILENAME = N'C:\SQL\SNAPSHOTS\tmp.ss'
FROM sys.database_files
WHERE type = 0
ORDER BY name; --rows file;

SET @SQL+='CREATE DATABASE '+QUOTENAME(DB_NAME()+@SnapshotPostfix)+' ON'+@nl -- CREATE DATABASE [nazwa_bazy_postfix] ON
+@DBFiles --definicja plikow
+' AS SNAPSHOT OF '+QUOTENAME(DB_NAME())+';';
PRINT @sql;

 

Dodaj komentarz