Déplacer la base tempdb avec SQL Server 2008 R2

Introduction

Dans cet article, la base tempdb d’un serveur SQL Server 2008 R2 est déplacée vers un autre disque. La base tempdb est déplacée en 4 étapes :

1. Obtention de la liste des fichiers de la base tempdb avec la vue sys.master_files.
2. Génération dynamique des commandes ALTER DATABASE pour déplacer la base tempdb vers le nouveau disque.
3. Lancement des commandes ALTER DATABASE.
4. Redémarrage du moteur SQL Server et vérification du déplacement de la base tempdb.

La base tempdb est déplacée du répertoire D:\MSSQL10_50.MSSQLSERVER\MSSQL\Data vers le répertoire E:\MSSQL10_50.MSSQLSERVER\MSSQL\Data.

Lister les fichiers de la base tempdb avec sys.master_files

La vue sys.master_files liste rapidement les fichiers de la base de données tempdb :

use master
go
select name, physical_name as CurrentLocation
from sys.master_files
where database_id = db_id(N'tempdb');
go
          name               CurrentLocation
------------- ----------------------------------------------------
tempdev       D:\MSSQL10_50.MSSQLSERVER\MSSQL\Data\tempdb.mdf
templog       D:\MSSQL10_50.MSSQLSERVER\MSSQL\Data\templog.ldf
tempdev_02    D:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\tempdev_02.ndf
tempdev_03    D:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\tempdev_03.ndf
tempdev_04    D:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\tempdev_04.ndf

Générer dynamiquement les commandes ALTER DATABASE pour déplacer la base tempdb

L’exemple ci-dessous génère dynamiquement les commandes ALTER DATABASE pour déplacer la base tempdb à partir de la vue sys.master_files. Pour cela la variable Transact SQL @new_location définit le nouveau répertoire qui hébergera la base tempdb :

use master
go
declare @new_location nvarchar(520)
select @new_location='E:\MSSQL10_50.MSSQLSERVER\MSSQL\Data'

select 'alter database tempdb ' + char(10)
+ 'modify file (name = ''' + name + ''', filename = '''
+ @new_location
+ '\'
+ substring(physical_name,
  len(physical_name) - (charindex('\', reverse(physical_name)) -2),
  len(physical_name)) + ''')'
+ char(10) + 'go' + char(10)
from sys.master_files
where database_id= db_id(N'tempdb')
go
          alter database tempdb 
modify file (name = 'tempdev',
  filename = 'E:\MSSQL10_50.MSSQLSERVER\MSSQL\Data\tempdb.mdf')
go

alter database tempdb 
modify file (name = 'templog',
  filename = 'E:\MSSQL10_50.MSSQLSERVER\MSSQL\Data\templog.ldf')
go

alter database tempdb 
modify file (name = 'tempdev_02',
  filename = 'E:\MSSQL10_50.MSSQLSERVER\MSSQL\Data\tempdev_02.ndf')
go

alter database tempdb 
modify file (name = 'tempdev_03',
  filename = 'E:\MSSQL10_50.MSSQLSERVER\MSSQL\Data\tempdev_03.ndf')
go

alter database tempdb 
modify file (name = 'tempdev_04',
  filename = 'E:\MSSQL10_50.MSSQLSERVER\MSSQL\Data\tempdev_04.ndf')
go

Déplacement de la base tempdb

Lancer les commandes ALTER DATABASE générées dynamiquement précédemment :

alter database tempdb 
modify file (name = 'tempdev_04', filename = 'E:\MSSQL10_50.MSSQLSERVER\MSSQL\Data\tempdev_04.ndf')
go
…
          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.
The file "tempdev_02" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "tempdev_03" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "tempdev_04" has been modified in the system catalog. The new path will be used the next time the database is started.

La base tempdb est recréée à chaque démarrage du service SQL Server. Les fichiers sont automatiquement créés au nouvel emplacement dès le redémarrage du moteur. Tant que le moteur n’est pas redémarré, SQL Server continue à utiliser les fichiers de données et les journaux à l’ancien emplacement.

Redémarrer le service SQL Server avec une invite de commandes DOS en mode Run As Administrator.

net stop MSSQLSERVER

The SQL Server (MSSQLSERVER) service is stopping.
The SQL Server (MSSQLSERVER) service was stopped successfully.

net start MSSQLSERVER
          
The SQL Server (MSSQLSERVER) service is starting.
The SQL Server (MSSQLSERVER) service was started successfully.

Redémarrer le service de l’agent MSSQLSERVER si celui-ci est nécessaire dans l’instance MS SQL Server.

Vérification du déplacement de la base tempdb

Interroger à nouveau la vue sys.master_files pour vérifier le déplacement effectif de la base tempdb :

use master
go
select name, physical_name AS CurrentLocation
from sys.master_files
where database_id = db_id(N'tempdb');
go
          name               CurrentLocation
------------------ ----------------------------------------------------
tempdev            E:\MSSQL10_50.MSSQLSERVER\MSSQL\Data\tempdb.mdf
templog            E:\MSSQL10_50.MSSQLSERVER\MSSQL\Data\templog.ldf
tempdev_02         E:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\tempdev_02.ndf
tempdev_03         E:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\tempdev_03.ndf
tempdev_04         E:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\tempdev_04.ndf

Lorsque tout est vérifié, les fichiers de tempdb encore éventuellement présents dans l’ancien répertoire peuvent être supprimés.