Introduction
SQL Server 7.0 permettait de déplacer les bases systèmes model
et msdb
avec
les procédures sp_detach_db
et sp_attach_db
: cette procédure n’est plus
autorisée avec SQL Server 2000 et SQL Server 2005. Par ailleurs, avec SQL
Server 2005, la nouvelle base de données système cachée appelée
mssqlsystemresource
fait son apparition.
Voici un guide pratique pour déplacer les bases systèmes SQL Server 2005 à travers un cas concret.
Les bases de données systèmes master
, model
, tempdb
, msdb
et
mssqlsystemresource
sont déplacées du répertoire
C:\MSSQL\MSSQLSERVER\MSSQL.1\MSSQL\DATA
vers le répertoire E:\MSSQL\Data
.
Déplacement des bases systèmes model, msdb et tempdb
Cette procédure s’applique uniquement au déplacement des bases systèmes
model
, msdb
et tempdb
, elle ne concerne pas le déplacement des bases systèmes
master
et mssqlsystemresource
(ce cas de figure est présenté dans un paragraphe ultérieur).
La vue sys.master_files
La vue sys.master_files
liste les noms logiques et
localisations physiques des fichiers de bases de données pour les bases de
données systèmes model
, msdb
et tempdb
.
SELECT db_name(database_id) AS DbName, name, physical_name AS CurrentLocation FROM sys.master_files WHERE database_id IN (DB_ID('model'),DB_ID('tempdb'),DB_ID('msdb')) ORDER BY database_id
DbName name CurrentLocation ------------------------------------------------------------------------ tempdb tempdev C:\MSSQL\MSSQLSERVER\MSSQL.1\MSSQL\DATA\tempdb.mdf tempdb templog C:\MSSQL\MSSQLSERVER\MSSQL.1\MSSQL\DATA\templog.ldf model modeldev C:\MSSQL\MSSQLSERVER\MSSQL.1\MSSQL\DATA\model.mdf model modellog C:\MSSQL\MSSQLSERVER\MSSQL.1\MSSQL\DATA\modellog.ldf msdb MSDBData C:\MSSQL\MSSQLSERVER\MSSQL.1\MSSQL\DATA\MSDBData.mdf msdb MSDBLog C:\MSSQL\MSSQLSERVER\MSSQL.1\MSSQL\DATA\MSDBLog.ldf
Génération automatique des commandes ALTER DATABASE pour déplacer les bases model, msdb et tempdb
Les bases de données systèmes model
, msdb
et tempdb
sont déplacées grâce à
la commande ALTER DATABASE MODIFY FILE
. Les commandes ALTER DATABASE
sont
exécutées lorsque le serveur SQL Server 2005 est en mode single user et en mode
restauration exclusive de la base master
(master-only recovery mode). Pour
minimiser l’indisponibilité du serveur SQL Server, les commandes ALTER DATABASE
peuvent être préparées automatiquement grâce à la requête ci-dessous :
SELECT 'ALTER DATABASE ' + db_name(database_id) + ' MODIFY FILE (NAME=''' + name + ''', FILENAME=''' + replace(physical_name, 'C:\MSSQL\MSSQLSERVER\MSSQL.1\MSSQL\DATA\', 'E:\MSSQL\DATA\') + ''')' + char(10) + 'go' + char(10) FROM sys.master_files WHERE database_id IN (DB_ID('model'),DB_ID('tempdb'),DB_ID('msdb')) ORDER BY database_id
ALTER DATABASE tempdb MODIFY FILE (NAME='tempdev', FILENAME='E:\MSSQL\DATA\tempdb.mdf') go ALTER DATABASE tempdb MODIFY FILE (NAME='templog', FILENAME='E:\MSSQL\DATA\templog.ldf') go ALTER DATABASE model MODIFY FILE (NAME='modeldev', FILENAME='E:\MSSQL\DATA\model.mdf') go ALTER DATABASE model MODIFY FILE (NAME='modellog', FILENAME='E:\MSSQL\DATA\modellog.ldf') go ALTER DATABASE msdb MODIFY FILE (NAME='MSDBData', FILENAME='E:\MSSQL\DATA\MSDBData.mdf') go ALTER DATABASE msdb MODIFY FILE (NAME='MSDBLog', FILENAME='E:\MSSQL\DATA\MSDBLog.ldf') go
Le résultat de la requête est conservé précieusement et sera exécuté
ultérieurement avec sqlcmd
. Dans la suite de cet article, les commandes ALTER
DATABASE
sont conservées dans le fichier SQL altersysmodelmsdbtempdb.sql
.
Procédure de déplacement des bases model, msdb et tempdb
Redémarrage du serveur SQL Server en mode single user et master-only recovery (respectivement -f , -T3608)
Arrêter le serveur SQL avec la commande NET STOP
depuis une commande DOS
:
Instance par défaut | Instance nommée (instancename ) |
---|---|
|
|
Si le service SQL Server Agent est actif, accepter également l’arrêt de ce service dépendant :
net stop MSSQLSERVER
The following services are dependent on the SQL Server (MSSQLSERVER) service. Stopping the SQL Server (MSSQLSERVER) service will also stop these services. SQL Server Agent (MSSQLSERVER) Do you want to continue this operation? (Y/N) [N]: Y The SQL Server Agent (MSSQLSERVER) service is stopping. The SQL Server Agent (MSSQLSERVER) service was stopped successfully. The SQL Server (MSSQLSERVER) service is stopping. The SQL Server (MSSQLSERVER) service was stopped successfully.
Démarrer ensuite le serveur SQL en mode single user (-f
) et master-only
recovery (-T3608
) avec la commande NET START
depuis une commande DOS en
spécifiant les paramètres /f et /T3608
Instance par défaut | Instance nommée (instancename ) |
---|---|
|
|
net start MSSQLSERVER /f /T3608
The SQL Server (MSSQLSERVER) service is starting. The SQL Server (MSSQLSERVER) service was started successfully.
Pour vérifier le bon démarrage en mode single user et master-only recovery, repérer les lignes ci-dessous dans le fichier de log du serveur SQL :
2009-11-25 18:25:21.34 spid4s SQL Server started in single-user mode …
2009-11-25 18:25:21.34 spid4s Recovering only master database because traceflag 3608 was specified …
2009-11-25 18:25:21.34 spid4s Starting up database 'master'.
Exécution des commandes ALTER DATABASE
Le fichier SQL altersysmodelmsdbtempdb.sql
généré précédemment et contenant
les commandes ALTER DATABASE
pour les bases systèmes model
, msdb
et tempdb
est
exécuté avec le binaire sqlcmd
.
Le message "The file .... has been modified in the system catalog. The new
path will be used the next time the database is started.
" doit s’afficher pour
chaque commande ALTER DATABASE
.
Instance par défaut | Instance nommée (instancename ) |
---|---|
|
|
The file "tempdev" has been modified in the system catalog. The new path will be
used the next time the database is started.
The file "templog" has been modified in the system catalog. The new path will be
used the next time the database is started.
…
Déplacement des fichiers et redémarrage en mode normal
Le serveur SQL Server est arrêté avec la commande NET STOP
et
les fichiers des bases de données model
, msdb
et tempdb
sont déplacés vers
leurs nouvelles localisations :
move /Y C:\MSSQL\MSSQLSERVER\MSSQL.1\MSSQL\DATA\tempdb.mdf E:\MSSQL\DATA
move /Y C:\MSSQL\MSSQLSERVER\MSSQL.1\MSSQL\DATA\templog.ldf E:\MSSQL\DATA
…
Le serveur SQL est redémarré en mode normal avec la commande NET START
Instance par défaut | Instance nommée (instancename ) |
---|---|
|
|
L’interrogation de la vue sys.master_files
permet de vérifier le déplacement
effectif des fichiers pour les bases de données systèmes model
, msdb
et
tempdb
.
SELECT db_name(database_id) AS DbName, name, physical_name AS CurrentLocation, state_desc FROM sys.master_files WHERE database_id IN (DB_ID('model'),DB_ID('tempdb'),DB_ID('msdb')) ORDER BY database_id
DbName name CurrentLocation state_desc ------------------------------------------------------------- tempdb tempdev E:\MSSQL\DATA\tempdb.mdf ONLINE tempdb templog E:\MSSQL\DATA\templog.ldf ONLINE …
Déplacement des bases master et mssqlsystemresource
Interdépendance entre la base système Resource et la base master
La base de données Resource mssqlsystemresource
est une nouvelle base de
données système dans SQL Server 2005 et dépend complètement de la localisation
de la base de données master
.
Si la base de données master
est déplacée, la base de données Resource mssqlsystemresource
doit être déplacée
également et au même endroit que la base master
.
Par ailleurs, la base de données Resource ne doit pas être déplacée dans des systèmes de fichiers NTFS encryptés ou compressés : dans une telle configuration les performances sont dégradées et les migrations impossibles.
Les étapes pour déplacer simultanément les bases master
et
mssqlsystemresource
sont décrites dans les paragraphes qui suivent.
Procédure de déplacement simultané des bases master et mssqlsystemresource
Dans cette procédure, des clés de registres sont modifiées, clés qu’il faut modifier avec de grandes précautions.
Modification des paramètres de démarrage de SQL Server pour la base master dans la base de registres
Les paramètres -d
et -l
sont toujours donnés au démarrage d’un serveur SQL
Server, qu’il s’agisse d’une instance par défaut ou d’une instance nommée. Le
paramètre -d
donne le chemin du fichier de données de la base master
et le
paramètre -l
le chemin du fichier de journal de transactions de la base master
.
Dans le contexte de cet article, SQL Server, qui est une instance par défaut, démarre avec les paramètres de démarrage suivants :
-dC:\MSSQL\MSSQLSERVER\MSSQL.1\MSSQL\DATA\master.mdf;
-eC:\MSSQL\MSSQLSERVER\MSSQL.1\MSSQL\LOG\ERRORLOG;
-lC:\MSSQL\MSSQLSERVER\MSSQL.1\MSSQL\DATA\mastlog.ldf
Les paramètres de démarrage qui précisent la localisation des fichiers de la
base master
sont consignés au sein de la base de registres dans la clé
HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL
Server\MSSQL.<#n>\MSSQLSERVER\Parameters
. <#n>
identifie
l’instance par son numéro #n.
Cas d’une instance par défaut MSSQLSERVER
Pour une instance par défaut MSSQLSERVER, le numéro #n
est toujours 1 et la
clé est HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL
Server\MSSQL.1\MSSQLSERVER\Parameters
.
Cas d’une instance nommée
Pour une instance nommée (par exemple l’instance nommée MOSS_INTERNET
), le
numéro #n
est facilement identifiable grâce à la clé de registre
HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\Instance Names\SQL
:
HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\Instance Names\SQL |
||
---|---|---|
Name | Type | Data |
MOSS_INTERNET |
REG_SZ |
MSSQL.2 |
MOSS_INTRANET |
REG_SZ |
MSSQL.1 |
À partir de ces informations, les paramètres de démarrage donnant la
localisation de la base master
pour l’instance nommée MOSS_INTERNET
sont donc
stockés dans la clé HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL
Server\MSSQL.2\MSSQLSERVER\Parameters
.
Dans le cas d’une instance par défaut pour exemple, les paramètres de démarrage sont enregistrés dans la base de registres de la façon suivante :
HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLSERVER\Parameters |
||
---|---|---|
Name | Type | Data |
SQLArg0 |
REG_SZ |
-dC:\MSSQL\MSSQLSERVER\MSSQL.1\MSSQL\DATA\master.mdf |
SQLArg1 |
REG_SZ |
-eC:\MSSQL\MSSQLSERVER\MSSQL.1\MSSQL\LOG\ERRORLOG |
SQLArg2 |
REG_SZ |
-lC:\MSSQL\MSSQLSERVER\MSSQL.1\MSSQL\DATA\mastlog.ldf |
Pour déplacer la base master
, les valeurs SQLArg0
et SQLArg2
sont modifiées
dans la base de registres afin de refléter la future nouvelle localisation de
fichiers de la base master
.
HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLSERVER\Parameters |
||
---|---|---|
Name | Type | Data |
SQLArg0 |
REG_SZ |
-dE:\MSSQL\DATA\master.mdf |
SQLArg1 |
REG_SZ |
-eC:\MSSQL\MSSQLSERVER\MSSQL.1\MSSQL\LOG\ERRORLOG |
SQLArg2 |
REG_SZ |
-lE:\MSSQL\DATA\mastlog.ldf |
Déplacement de la base master et redémarrage en mode single user
L’instance est arrêtée avec NET STOP
et les fichiers de données
de la base master master.mdf
et mastlog.ldf
sont déplacés vers leur future
localisation :
move /Y C:\MSSQL\MSSQLSERVER\MSSQL.1\MSSQL\DATA\master.mdf E:\MSSQL\DATA
move /Y C:\MSSQL\MSSQLSERVER\MSSQL.1\MSSQL\DATA\mastlog.ldf E:\MSSQL\DATA
L’instance ne doit surtout pas être redémarrée en mode normal immédiatement après le déplacement des fichiers
de la base master
car il reste la base mssqlsystemresource
à déplacer également
à côté de la base master
.
L’instance est redémarrée en mode single user et master-only recovery (-f
et
-T3608
)
Déplacement de la base mssqlsystemresource
Le serveur SQL étant en mode en single user, la localisation des fichiers de
la base mssqlsystemresource
est modifiée avec les commandes ALTER DATABASE
. Les
noms logiques du fichier de données et du fichier de journal de la base
mssqlsystemresource
sont respectivement data
et log
(non modifiables).
ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=data, FILENAME= 'E:\MSSQL\Data\mssqlsystemresource.mdf'); go ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=log, FILENAME= 'E:\MSSQL\Data\mssqlsystemresource.ldf'); go
The file "data" has been modified in the system catalog. The new path will be used the next time the database is started. The file "log" has been modified in the system catalog. The new path will be used the next time the database is started.
Le répertoire de la base mssqlsystemresource
est obligatoirement celui de la base master
et il est interdit de modifier les noms logiques de la base mssqlsystemresource
.
La base de données mssqlsystemresource
doit être mise en mode lecture seule
:
ALTER DATABASE mssqlsystemresource SET READ_ONLY;
go
L’instance SQL Server est ensuite arrêtée avec NET STOP
pour déplacer les
fichiers de la base mssqlsystemresource
vers leur nouvel emplacement :
move /Y C:\MSSQL\MSSQLSERVER\MSSQL.1\MSSQL\DATA\master.mdf E:\MSSQL\DATA
move /Y C:\MSSQL\MSSQLSERVER\MSSQL.1\MSSQL\DATA\mastlog.ldf E:\MSSQL\DATA
Vérifications
L’opération de déplacement de la base master
et mssqlsystemresource
est
terminée en redémarrant en mode normal l’instance SQL Server avec NET START
.
Consulter le fichier de log du serveur pour vérifier le bon redémarrage.