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

Lip 5, 2017

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