Przenoszenie domyślnych katalogów w SQL Server (!!!nie testowane !!!)

utworzone przez dsp_dozorca | lip 5, 2017 | Blog, Porady, Wiedza | 0 komentarzy

Originale TUTAJ Moze  się kiedyś przyda!!!

Instalacja SQL Server do domyślnych katalogach mogą być trudne do maintan przede wszystkim dlatego, Microsoft zmienia domyślną ścieżkę do katalogu danych w każdej wersji głównej. Na przykład domyślne ścieżki do plików bazy danych dla instancji domyślnej w SQL 2005-2008 R2 są:

SQL 2005 -% Program Files% \ Microsoft SQL Server \ MSSQL.1 \ MSSQL \ Data
SQL 2008 -% Program Files% \ Microsoft SQL Server \ MSSQL10.MSSQLSERVER \ MSSQL \ Data
SQL 2008 R2 -% Program Files% \ DATA Microsoft SQL Server \ MSSQL10_50.MSSQLSERVER \ MSSQL \

Wykonywanie przywracania bazy danych z jednego serwera na inny z różnymi wersjami może dodać złożoności z poleceniem restore ponieważ ścieżka docelowa nie jest spójna. Również po aktualizacji w miejscu SQL Server pliki bazy danych nie zostały przeniesione z poprzedniej domyślnej ścieżce do nowego więc serwer może mieć MDF i LDF pliki w katalogu z łudząco nazwie ścieżki.

Korzystanie z katalogu Standardowy
Mam standaryzowane moje instalacje użyć tej samej ścieżki dla wszystkich lokalizacjach MDF i LDF plików. Upraszcza to odbudowała skryptów i zmniejsza złożoność podczas migracji, ponieważ wszystkie pliki danych na wszystkich serwerach są w tej samej ścieżce. I zawsze moje pliki dziennika na osobnym dysku z plików danych są więc w tych samych ścieżek na różnych dyskach, na przykład:

Logs – y:\mssql\%instanceName%\data
Data – z:\mssql\%instanceName%\data

Po zainstalowaniu i wszystkich plikach mdf i ldf znajdują się w nowych, ulepszonych lokalizacjach standardowych, znalazłem trzy dodatkowe katalogi, które chciałem ujednolicić i przejść do domyślnej ścieżki:

database engine default backup directory
%SQL Install Directory%\MSSQL10_50.MSSQLSERVER\MSSQL\BACKUP
sql agent working directory
%SQL Install Directory%\MSSQL10_50.MSSQLSERVER\MSSQL\JOBS
sql agent log directory
%SQL Install Directory%\MSSQL10_50.MSSQLSERVER\MSSQL\LOG

Konfiguracja nie jest dostępna za pośrednictwem interfejsu GUI SSMS lub podczas instalacji. Jedynym sposobem ich zmiany są bezpośrednio w rejestrze lub za pomocą skryptu t-sql, który zmienia rejestr.
Oto skrypt, który zmieni je na dowolną ścieżkę wybranej ścieżki i ma świadomość, że poprawny klucz rejestru jest aktualizowany dla dowolnej instancji. Uwaga – ten skrypt używa procedury rozszerzonej xp_instance _regwrite,

w przyszłości może nie działać .

USE [msdb]
GO
 
-- change the @instancename to whatever named instance this is for, the rest will sort itself out ----------------------
DECLARE @instancename nvarchar(255) = N'mssqlserver' -- use 'mssqlserver' for the default instance
 
DECLARE @mssqlpath nvarchar(255) = N'z:\mssql\' + @instancename
DECLARE @dbengine_defaultbackup_path nvarchar(255) = @mssqlpath + N'\backup'
DECLARE @sqlagent_errorlogfile nvarchar(255) = @mssqlpath + N'\log\SQLAGENT.OUT'
DECLARE @sqlagent_workingdirectory_path nvarchar(255) = @mssqlpath + N'\jobs'
 
--SELECT @dbengine_defaultbackup_path, @sqlagent_workingdirectory_path, @sqlagent_errorlogfile
 
-- update the database engine's default backup directory path ---------------------------------------------------------
EXECUTE [master].dbo.xp_instance_regwrite
 N'HKEY_LOCAL_MACHINE'
 , N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer'
 , N'BackupDirectory'
 , N'REG_SZ'
 , @dbengine_defaultbackup_path
 
-- update the SQL Agent working directory -----------------------------------------------------------------------------
EXECUTE [master].dbo.xp_instance_regwrite
 N'HKEY_LOCAL_MACHINE'
 , N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'
 , N'WorkingDirectory'
 , N'REG_SZ'
 , @sqlagent_workingdirectory_path
 
-- update the SQL Agent log file location -----------------------------------------------------------------------------
EXECUTE msdb.dbo.sp_set_sqlagent_properties @errorlog_file = @sqlagent_errorlogfile
 
-- update the setup's SQL Data Root path ------------------------------------------------------------------------------
EXECUTE [master].dbo.xp_instance_regwrite 
 N'HKEY_LOCAL_MACHINE'
 , N'SOFTWARE\Microsoft\MSSQLServer\Setup'
 , N'SQLDataRoot'
 , N'REG_SZ'
 , @mssqlpath

Zmodyfikuj skrypt, aby używać dowolnej ścieżki domyślnej. Usługi SQL Server i SQL Agent będą musiały zostać ponownie uruchomione, aby rozpocząć korzystanie z nowych lokalizacji plików. Gdy nowe lokalizacje są w użyciu, możesz usunąć stare katalogi.

Aktualizacja

Po próbie uaktualnienia jednego z moich serwerów do programu SQL Server 2012 stwierdziłem, że instalator miałby błąd podczas instalowania silnika bazy danych. Bałagan był:

“The Database Engine system data directory in the registry is not valid.”

Znalazłem klucz HKLM\software\microsoft\microsoft sql server\%instance_name%\setup\SQLDataRoot  .Odszukuje  ścieżkę dostępu do baz danych systemu. Ten klucz został utworzony podczas instalacji 2008 R2 i pliki zostały następnie przeniesione, więc to, co zostało utworzone w czasie instalacji, nie jest poprawne.

Jeśli zmodyfikujesz ten klucz na ścieżce skonfigurowanej w pliku @mssqlpath ze skryptu powyżej instalatora 2012, działa prawidłowo. Zmieniłem również skrypt, ostatnia sekcja wprowadza odpowiednią zmianę w celu uniknięcia tego wyjątku w przyszłości.

PDF oryginału