Odtwarzanie baz systemowych w MsSQL

W przypadku odtwarzania baz użytkowników w bazie MsSQL sprawa jest prosta. Podajemy lokalizację backupu bazy (pliku BAK), podajemy nazwę naszej bazy (jeśli chcemy ją zmienić), podajemy lokalizację plików MDFi LDF (jesli chcemy zmienić), wciskamy Odwtórz i baza gotowa do pracy. Gorzej wygląda w przypadku kiedy chcemy odzyskać bazy systemowe, a szczególnie gdy odtwarzamy je z innego serwera gdzie lokalizacja plików MDF i LDF była inna oraz gdzie serwer miał inną nazwę.
Na początek kilka informacji o bazach systemowych. Mamy kilka baz należących do tzw grupy baz systemowych:

master – zapisane są tu wszelkie informacje o użytkownikach, ich uprawnieniach, o plikach baz i lokalizacji ich plików bazodanowych
model – jest to tzw modelowa baza danych. Każda stworzona nowa baza będzie kopią tej bazy. Jeśli chcemy aby w nowych bazach była jakaś procedura składowana lub tabela (np. techniczna) musimy w bazie model stworzyć taki obiekt
msdb – baza ta głównie odpowiada SQLAgenta czyli za wszelkie joby w bazie, konfigurację replikacji itd.
temp – jest to baza tymczasowa. Wykorzystywana jest podczas pracy bazy. Bazy tej nie backupujemy gdyż przy każdorazowym starcie bazy jest ona usuwana i tworzona na nowo;
distribution – jest to specjalna baza systemowa tworzona na serwerach gdzie jest uruchomiona replikacja. Odtwarza się ją jak normalną bazę użytkownika.

Nim przystąpimy do procesu odzyskiwania baz proponuję skopiować je do jakiegoś „prostego folderu”. Chodzi o to by były gdzieś na dysku z krótką ścieżką (D:\BACKUP\master.bak), a nie gdzie zagłębione w folderach ze spacjami w nazwach itd gdzie będziemy musieli za każdym razem kopiować ścieżkę do plików. To tak bardziej dla wygody 🙂
No i druga sprawa aby przed odzyskiwaniem baz systemowych zrobić kopię obecnych baz. Można to wykonać poprzez backup baz lub po prostu zatrzymać serwis SQL i skopiować folder z bazami systemowymi. Przydadzą się jakby coś nam nie poszło 🙂
Druga sprawa aby sprawdzić gdzie znajdują się bazy systemowe a serwerze źródłowym skąd mamy bazy systemowe i na nowym serwerze stworzyć takie same foldery i wrzucić tam kopie baz. Pozwoli to nam na tymczasowe uruchomienie serwera SQL. Po procesie odzyskania baz usuniemy te kopie baz.

Odtwarzanie bazy MASTER.

I tu już nie jest tak prosto. Jest to najważniejsza baza w SQL-u i do tego dość specyficzna. Po pierwsze aby przywrócić bazę Master musimy przestawić bazę w tryb SingleUser czyli praktycznie ją wyłączyć i zrobić dostępną wyłącznie dla administratora.
Aby to zrobić uruchamiamy SQL Server Configuration Manager-a

Klikamy w SQL Server a następnie wchodzimy w zakładkę Startup Parameters

W Oknie parametrów dopisujemy parametr „-m”

Klikamy ADD

Po tym wpisie konieczny będzie restart SQL-a

W nowszych serwerach Windows można to zrobić w prostszy sposób wyświetlając właściwości serwisu MSSQLSERVER, zatrzymując serwis przyciskiem STOP, a następnie wpisując w okno parametrów parametr -m i uruchamiając serwis przyciskiem START.

Po uruchomieniu serwisu okna nie zamykamy.

Uwaga! Kliknięcie OK i próba uruchomienia serwisu w normalny sposób spowoduje uruchomienie serwisu w normalnym trybie.

Po restarcie baza jest w trybie Single User. Nie dostaniemy się do niej za pomocą SQL Management Studio. Do dalszych prac będziemy musieli skorzystać z linii poleceń (SQLCMD)

Uruchamiamy linię poleceń (CDM) i wpisujemy polecenie :

sqlcmd

a następnie :

RESTORE DATABASE master FROM DISK = 'd:\backup\master.bak' WITH REPLACE;
GO

Po odzyskaniu bazy master serwis SQL jest zatrzymywany.

Baza jest odzyskana. Jeśli nie ma konieczności wykonywania dalszych prac na bazie master po usunięciu wpisu w SQL Configuration Manager wpisu -M restartujemy bazę i powinna być już gotowa do użycia. Jeśli uruchamialiśmy serwis na nowszych systemach Windows musimy usunąć parametr „-m” z okna właściwości serwisu i uruchomić serwis przyciskiem START.
Sprawdzamy czy odzyskały się uprawnienia i czy baza działa prawidłowo.

Gorzej gdy odzyskujemy bazy z serwera, gdzie leżały one w innych folderach. Baza master podczas odzyskiwania zostanie umieszczona w folderze zadeklarowanym podczas instalacji instancji. Po jej odzyskaniu bazy model, msdb oraz temp będzie szukał w folderach, które są zapisane w bazie master (lokalizacje jak na serwerze z którego odzyskujemy bazę master). A tu niespodzianka, bo na nowym serwerze powinny leżeć gdzie indziej. Możemy na upartego stworzyć foldery jak na starym serwerze i tam umieścić bazy systemowe (oprócz mastera który jest w domyślnej lokalizacji). SQL powinien uruchomić się bez większego problemu.

W takim przypadku nie przestawiamy bazy z tryby Single User i w tym trybie wykonujemy dalsze prace czyli zmianę lokalizacji baz systemowych zapisanych w naszej nowo odtworzonej bazie Master.
Najpierw sprawdzamy jakie lokalizacje są zapisane w bazie Master.

Sprawdzamy położenie plików MSDB::

SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID(N'msdb');
GO

name		physical_name
=============================================
MSDBData	d:\MSSQL\data\msdbdata.mdf
MSDBLog		d:\MSSQL\data\msdblog.ldf

Zmieniamy je na nową ścieżkę:

USE master;
GO
ALTER DATABASE msdb
MODIFY FILE (NAME = MSDBData, FILENAME = 'D:\SQLData\MSSQL12.MSSQLSERVER\MSSQL\DATA\msdbdata.mdf');
GO
ALTER DATABASE msdb
MODIFY FILE (NAME = MSDBLog, FILENAME = 'D:\SQLData\MSSQL12.MSSQLSERVER\MSSQL\DATA\msdblog.ldf');
GO

Plik „MSDBData” zostal zmodyfikowany w wykazie systemowym. 
    Nowa sciezka bedzie uzywana przy nastepnym uruchomieniu bazy danych.
Plik „MSDBLog” zostal zmodyfikowany w wykazie systemowym. 
    Nowa sciezka bedzie uzywana przy nastepnym uruchomieniu bazy danych.

To samo robimy z bazą model:

SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID(N'model');
GO

name		physical_name
==========================================
modeldev	d:\MSSQL\data\model.mdf
modellog	d:\MSSQL\data\modellog.ldf

Zmieniamy położenie plików:

USE master;
GO
ALTER DATABASE model
MODIFY FILE (NAME = modeldev, FILENAME = 'D:\SQLData\MSSQL12.MSSQLSERVER\MSSQL\DATA\model.mdf');
GO
ALTER DATABASE model
MODIFY FILE (NAME = modellog, FILENAME = 'D:\SQLData\MSSQL12.MSSQLSERVER\MSSQL\DATA\modellog.ldf');
GO

Plik „modeldev” zostal zmodyfikowany w wykazie systemowym. 
    Nowa sciezka bedzie uzywana przy nastepnym uruchomieniu bazy danych.
Plik „modellog” zostal zmodyfikowany w wykazie systemowym. 
    Nowa sciezka bedzie uzywana przy nastepnym uruchomieniu bazy danych.

Sprawdzamy poprzednimi poleceniami położenie baz:

name		physical_name
========================================================================
MSDBData	D:\MSSQL\MSSQL12.MSSQLSERVER\MSSQL\DATA\msdbdata.mdf
MSDBLog		D:\MSSQL\MSSQL12.MSSQLSERVER\MSSQL\DATA\msdblog.ldf

name		physical_name
========================================================================
modeldev	D:\MSSQL\MSSQL12.MSSQLSERVER\MSSQL\DATA\model.mdf
modellog	D:\MSSQL\MSSQL12.MSSQLSERVER\MSSQL\DATA\modellog.ldf

name		physical_name
========================================================================
master		D:\MSSQL\MSSQL12.MSSQLSERVER\MSSQL\DATA\master.mdf
mastlog		D:\MSSQL\MSSQL12.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

Sprawdzamy położenie plików TEMP:

use tempdb
exec sp_helpfile

name	fileid	filename			filegroup	size	maxsize	growth	usage
-------------------------------------------------------------------------------------
tempdev	1	d:\MSSQL\data\tempdb.mdf	PRIMARY	1048576 KB	Unlimited	10%	data only
templog	2	d:\MSSQL\data\templog.ldf	NULL	    768 KB	Unlimited	10%	log only

Zmieniamy położenie plików TEMP na nową lokalizację:

use master
alter database tempdb modify file (name = tempdev, filename = 'D:\MSSQL\MSSQL12.MSSQLSERVER\MSSQL\Temp\tempdb.mdf')
alter database tempdb modify file (name = templog, filename = 'D:\MSSQL\MSSQL12.MSSQLSERVER\MSSQL\Temp\templog.ldf')

Plik „tempdev” zostal zmodyfikowany w wykazie systemowym. 
    Nowa sciezka bedzie uzywana przy nastepnym uruchomieniu bazy danych.
Plik „templog” zostal zmodyfikowany w wykazie systemowym. 
    Nowa sciezka bedzie uzywana przy nastepnym uruchomieniu bazy danych.

Wszystkie ścieżki zostały zmienione. Możemy teraz zatrzymać bazę, przenieść bazy model i msdb do odpowiednich folderów. Bazy TEMP utworzą się automatycznie przy uruchomieniu bazy (musi tylko istnieć zadeklarowany folder).

Czasami się zdarza że bazy systemowe znajdowały się na partycji której na nowym serwerze w ogóle nie ma i nie możemy "tymczasowo" ich wrzucić do folderu i następnie przenieść w odpowiednie miejsca.
W taki przypadku po odzyskaniu bazy master i automatycznym wyłączeniu serwisu należu uruchomić go ponownie z opcją "/f /T3608"

a następnie uruchomić serwis przyciskiem START. Następnie kolejno pozmieniać wszystkie ściezki do baz systemowych (oprócz bazy master).

Następnie zatrzymać serwis, usunąć parametr i uruchomić normalnie serwis SQL.

Odtwarzanie bazy MODEL

To jest akurat najprostsze, bo wykonujemy to analogicznie jak w przypadku zwykłej bazy użytkownika. Możemy to wykonać z SQL Management Studio lub uruchomić odzyskiwanie w linii poleceń SQLCMD

RESTORE DATABASE model FROM DISK = 'd:\backup\model.bak' WITH REPLACE;
GO

Odtwarzanie bazy MSDB

Tu sprawa też jest w miarę prosta gdyż odtworzenie jej jest analogiczne jak w przypadku zwykłej bazy z tym że aby odtworzyć bazę Msdb należy zatrzymać serwis SQLAgent-a.

RESTORE DATABASE masb FROM DISK = 'd:\backup\msdb.bak' WITH REPLACE;
GO

Po odzyskaniu baz dla pewności serwer restartujemy. Po restarcie baza powinna wstać a w logu nie powinno być żadnych błędów.

Ten wpis został opublikowany w kategorii Microsoft SQL i oznaczony tagami , . Dodaj zakładkę do bezpośredniego odnośnika.

Dodaj komentarz