Rename Logical and Physical files along with Database name using TSQL

Last night I ran into a situation when I started creating a new database using SSMS, it was complaining me ‘Database already exists’. I quickly checked object explorer and was not able to find the database with the same name. I was confused and lost for a second, and then I quickly went to check LDF and MDF files, I was able to find the LDF and MDF files with same name. That’s the problem which concluded me that some time I changed Database name and Logical file names of a database (that’s why I am not able find the name in object explorer) to another name leaving behind the physical file names without change (which I was able to find in physical path). So when I started to create a new database with same physical file name, it is throwing me error.

Above problem made me to quickly write a script with some online research to help SQL Developers change logical and physical file names along with Database name. Please check out below code. To explain code, first we are going to set the database into single user mode. Then change the logical file names followed by the change to database name. But tricky part comes when renaming physical files. We need to use shell commands to change the names of physical files. So for that we need to configure SQL Server to run shell commands using sp_configure. On the later part we need to detach the database, then run the RENAME command to change physical file names. Now re-attach the database to engine with new names. Finally bring back renamed database to multi user mode.

IMPORTANT:

There can be permissions issues when we try to rename the physical files due to ownership conflict. Check out below code along with its comments which addresses permissions issues.

/************************ Set TestDB tp Single User Mode **************************/
ALTER DATABASE [TestDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO


/**************** Change Logical File Names of MDF and LOG files ******************/
ALTER DATABASE [TestDB] MODIFY FILE (NAME=N'TestDB', NEWNAME=N'TestDBNew');
ALTER DATABASE [TestDB] MODIFY FILE (NAME=N'TestDB_log', NEWNAME=N'TestDBNew_log');
GO


/************************ Change TestDB Name to TestDBNew *************************/
ALTER DATABASE [TestDB] MODIFY Name = [TestDBNew];
GO


/********************************* IMPORTANT **************************************
To change Physical file names through TSQL code, we need to execute shell commands.
To enable shell commands first we need to configure server using sp_configure with
override. But I have to warn about using this method because of following reason. 

By default all database physical files are under SQL Server account ownership. Under 
this ownership, when any other user tries to change the name using shell commands,
there will be an error 'Access is Denied'. I mean, when you try to execute the below
statements under any other different (windows or database) login, it is going to give
an error. 

To fix this error, we need to change the physical files ownership to the login which 
is going to run these commands. Along side, you need to have default permissions 
inheritance enabled on these files, so that other accounts with full control will be 
given access to these files as in normal cases and all other operations at file system
level will operate normally. But I am strongly against to the idea of changing the 
ownership as it might give any other kind of unknown problems over time.

Workaround to this problem, is to detach the database as shown below, then skip the
renaming code, go manually to the folder where the MDF and LDF files were present 
and change the name manually by righ-click -> rename. Then come back and run Attach
code (as shown below).
**********************************************************************************/

/*************** Change Server settings to execute Shell commands *****************/
USE [master]
GO
sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE WITH OVERRIDE
GO


/********************** Dettach Database to give new names ************************/
EXEC [master].[dbo].[sp_detach_db] @dbname = N'TestDBNew'
GO


/*************** Change physical file names of MDF and LDF files ******************
At this step you need to give permissions to the user as mentioned in above comments.
**********************************************************************************/
EXEC xp_cmdshell 
     'RENAME "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TestDB.mdf","TestDBNew.mdf"'
EXEC xp_cmdshell 
     'RENAME "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TestDB_log.ldf","TestDBNew_log.ldf"'
GO


/************************ Attach Database with new names **************************/
CREATE DATABASE [TestDBNew] ON
        (FILENAME = 
                N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TestDBNew.mdf'),
        (FILENAME = 
                N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TestDBNew_log.ldf')
 FOR ATTACH
 GO


 /************************ Set TestDB tp Multi User Mode **************************/
ALTER DATABASE [TestDBNew] SET MULTI_USER;
GO

You may also like...