Renommer une base de données

Environnement: Windows SQL Server 2012
Base de données: AdventureWorks2012

Problématique

Nous allons exposé ci-dessous une problématique que vous pouvez rencontrer lors du renommage de votre base de données SQL Server.

ALTER DATABASE
   AdventureWorks2012
MODIFY 
   NAME = AdventureWorks2012_old

Lors de l’exécution de cette commande. Nous observons l’erreur suivante :

La base de données ne peut pas être verrouillée de façon exclusive pour effectuer cette opération.

Renommer une base de données – Solution

Pour éviter cela, vous devez passer votre base sql en mode exclusif pour pouvoir la renommer.

ALTER DATABASE
   AdventureWorks2012
SET 
   SINGLE_USER 
WITH 
   ROLLBACK IMMEDIATE

SINGLE_USER : Indique que l’accès à la base de données n’est autorisé qu’à un seul utilisateur à la fois.
WITH ROLLBACK IMMEDIATE : Toutes les transactions incomplètes seront restaurées et les autres connexions à la base de données immédiatement déconnectées.
Nous pouvons maintenant la renommer sans oublier de remettre notre base en mode MUTLI USER.

ALTER DATABASE 
   AdventureWorks2012
MODIFY 
   NAME = AdventureWorks2012_old
GO
ALTER DATABASE 
   AdventureWorks2012_old
SET 
   MULTI_USER

Une fois que votre script sera exécuté le nom de base de données changera cependant le nom logique et le nom du fichier physique ne changera pas.
Vous pouvez vérifier cela en exécutant le code SQL ci-dessous :

SELECT
   name AS [Logical Name],
   physical_name AS [DB File Path],
   type_desc AS [File Type],
   state_desc AS [State]
FROM 
   sys.master_files
WHERE 
   database_id = DB_ID(N'AdventureWorks2012_old')

Dans cette seconde étape, vous devez modifier votre script afin de changer le nom logique de la base de données

 
/*Modification du fichier data*/
ALTER DATABASE 
   AdventureWorks2012_old
MODIFY FILE 
   (NAME= N'AdventureWorks2012_Data', 
   NEWNAME= N'AdventureWorks2012_Data_old')
GO
/*Modification du fichier de log*/
ALTER DATABASE 
   AdventureWorks2012_old
MODIFY FILE 
   (NAME=N'AdventureWorks2012_log',
   NEWNAME= N'AdventureWorks2012_old_log')

Maintenant nous allons détacher la base de données pour renommer les fichiers physique tant qu’elle est attaché, nous ne pourrons pas.

USE [master]
GO
EXEC master.dbo.sp_detach_db
   @dbname = ‘AdventureWorks2012_old’
GO

Une fois la Base de données détachée, nous avons le choix entre renommer les fichiers manuellement depuis l’explorateur Windows ou depuis management studio à l’aide de la commande xp_cmdshell.
Avant de pouvoir utiliser cette commande il faut l’activer sur le serveur à l’aide de la procédure system sp_configure.

USE master
GO
sp_configure 'show advanced options'
GO
/* 0 = Disabled , 1 = Enabled */
sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE WITH OVERRIDE
GO
USE [master]
GO
EXEC xp_cmdshell 'RENAME "d:\AdventureWorks2012\AdventureWorks2012_Data.mdf",
"AdventureWorks2012_old_data.mdf"'
GO
EXEC xp_cmdshell 'RENAME "d:\AdventureWorks2012\AdventureWorks2012_old_log.ldf",
"AdventureWorks2012_old_log.ldf"'
GO

Conclusion

Nous venons de voir comment régler le problème de l’accès exclusive d’une base de données mais aussi comment renommer entièrement une base de données.
L’ensemble du script est disponible sur notre espace github.