How to Rename a Database and its Logical and Physical Files

As part of the database migration steps, you should also add the steps to rename the database if needed and its logical and physical files. While there are no requirements that the logical and physical file names need to align with the actual database name, providing self-documenting names will avoid confusion. In this blog, we will explore the required steps to query the existing logical and physical file name, rename a database, rename the logical file names, and rename the physical file names.

In a previous blog, How to Restore Your Backups from Striped Backup Files, we restored the AdventureWorks2012 database to create a new database, AdventureWorks2012_NEW. We can query the sys.master_files system table to view its logical and physical file names:

-- Querying the logical and physical file names

SELECT db_name(mf.database_id) as DatabaseName

, mf.name as LogicalFileName

, mf.type_desc

, mf.physical_name

FROM sys.master_files as mf

WHERE db_name(mf.database_id) = 'AdventureWorks2012_NEW'

Script: QueryingLogicalPhysicalFilesName.sql

Output:

Screen-shot: Logical and Physical File Names

In the following exercises, we will:

1. Rename the AdventureWorks2012_NEW database as AW.
2. Modify the logical files for the AW database to reflect the new database name.
3. Set the AW database offline so that we can rename the physical files.
4. Enable the xp_cmdshell system stored procedure.
5. Rename the physical files of the AW database.
6. Set the AW database online.
7. Verify the new names of the logical and physical files.

To rename the AdventureWorks2012_NEW database as AW, run the following script:

-- Rename the AdventureWorks2012_NEW database to AW

-- Set the AdventureWorks2012_NEW to single user for exclusive access

-- Rename it

-- Set it back to multi-user

USE master

GO

ALTER DATABASE [AdventureWorks2012_NEW]

SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE [AdventureWorks2012_NEW]

MODIFY NAME = AW

ALTER DATABASE [AW]

SET MULTI_USER

Script: RenameDB.sql

If you query the sys.master_files system table again, you will notice that while the database name was modified, the logical and physical file names stayed the same as they were with the previous database name:

-- Querying the logical and physical file names for the AW database

SELECT db_name(mf.database_id) as DatabaseName

, mf.name as LogicalFileName

, mf.type_desc

, mf.physical_name

FROM sys.master_files as mf

WHERE db_name(mf.database_id) = 'AW'

Script: QueryingLogicalPhysicalFileNamesAWDB.sql

Output:

Screen-shot: AW database logical and physical file names

We will now modify the logical file names and then query the sys.master_files system table to check on the logical file name. As you can see, the logical file names have been updated.

-- Renaming the logical file names of the AW database

-- 1. Set the AW database to single user mode

-- 2. Modify the logical file names

-- 3. Set the AW database back to multi-user mode

-- 4. Query the sys.master_files system table

USE master

GO

ALTER DATABASE [AW]

SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE [AW] MODIFY FILE (NAME=N'AdventureWorks2012_Data', NEWNAME=N'AW_Data')

ALTER DATABASE [AW] MODIFY FILE (NAME=N'AdventureWork_Data2', NEWNAME=N'AW_Data2')

ALTER DATABASE [AW] MODIFY FILE (NAME=N'AdventureWorks2012_Log', NEWNAME=N'AW_Log')

ALTER DATABASE [AW]

SET MULTI_USER WITH ROLLBACK IMMEDIATE

GO

-- Querying the logical and physical file names for the AW database

SELECT db_name(mf.database_id) as DatabaseName

, mf.name as LogicalFileName

, mf.type_desc

, mf.physical_name

FROM sys.master_files as mf

WHERE db_name(mf.database_id) = 'AW'

Script: RenameLogicalFileNames.sql

Output:

Screen-shot: Logical File Names Updated

To rename the physical file name, we will need to set the database offline so that the files will not be in use:

-- Set the AW database offline

USE master

GO

ALTER DATABASE [AW]

SET OFFLINE WITH ROLLBACK IMMEDIATE

Script: SetAWDBOffline.sql

While you can browse to the corresponding folders to rename the data and log files through the GUI, we will use the extended stored procedure xp_cmdshell to rename the files. First, you must enable it:

-- Enable xp_cmdshell

USE master

GO

SP_CONFIGURE 'show advanced options'

GO

SP_CONFIGURE 'xp_cmdshell', 1

GO

RECONFIGURE WITH OVERRIDE

GO

Script: EnableXP_CMDSHELL.sql

We will now rename the physical files of the AW database:

-- Rename the physical files for the AW database

USE master

GO

EXEC xp_cmdshell 'RENAME "E:\SQLData\AdventureWorks2012_NEW_Data.mdf", "AW_Data.mdf"'

EXEC xp_cmdshell 'RENAME "L:\SQLLog\AdventureWorks2012_NEW_log.ldf", "AW_log.ldf"'

EXEC xp_cmdshell 'RENAME "E:\SQLData\AdventureWork_NEW_Data2.ndf", "AW_Data2.ndf"'

GO

Script: RenamePhysicalFileNamesAW.sql

To update the file names and bring the AW database online, run the following script:

-- Update the file names and bring the AW database online

USE master

GO

ALTER DATABASE [AW] MODIFY FILE (NAME = AW_Data, FILENAME = 'E:\SQLData\AW_Data.mdf')

ALTER DATABASE [AW] MODIFY FILE (NAME = AW_Log, FILENAME = 'E:\SQLData\AW_Log.ldf')

ALTER DATABASE [AW] MODIFY FILE (NAME = AW_Data2, FILENAME = 'E:\SQLData\AW_Data2.ndf')

GO

ALTER DATABASE [AW]

SET ONLINE

Script: UpdateFileNames.sql

If you query the sys.master_files system table again for the AW database, you will see that the database name, logical file names, and physical file names are now all updated:

-- Querying the logical and physical file names for the AW database

SELECT db_name(mf.database_id) as DatabaseName

, mf.name as LogicalFileName

, mf.type_desc

, mf.physical_name

FROM sys.master_files as mf

WHERE db_name(mf.database_id) = 'AW'

Script: QueryingLogicalPhysicalFileNamesAWDB.sql

Output:

Screen-shot: AW database logical and physical file names updated

Datavail Script: Terms & Conditions

By using this software script (“Script”), you are agreeing to the following terms and condition, as a legally enforceable contract, with Datavail Corporation (“Datavail”). If you do not agree with these terms, do not download or otherwise use the Script. You (which includes any entity whom you represent or for whom you use the Script) and Datavail agree as follows:

1. CONSIDERATION. As you are aware, you did not pay a fee to Datavail for the license to the Script. Consequently, your consideration for use of the Script is your agreement to these terms, including the various waivers, releases and limitations of your rights and Datavail’s liabilities, as setforth herein.
2. LICENSE. Subject to the terms herein, the Script is provided to you as a non-exclusive, revocable license to use internally and not to transfer, sub-license, copy, or create derivative works from the Script, not to use the Script in a service bureau and not to disclose the Script to any third parties. No title or other ownership of the Script (or intellectual property rights therein) is assigned to you.
3. USE AT YOUR OWN RISK; DISCLAIMER OF WARRANTIES. You agree that your use of the Script and any impacts on your software, databases, systems, networks or other property or services are solely and exclusively at your own risk. Datavail does not make any warranties, and hereby expressly disclaims any and all warranties, implied or express, including without limitation, the following: (1) performance of or results from the Script, (2) compatibility with any other software or hardware, (3) non-infringement or violation of third party’s intellectual property or other property rights, (4) fitness for a particular purpose, or (5) merchantability.
4. LIMITATION ON LIABILITY; RELEASE. DATAVAIL SHALL HAVE NO, AND YOU WAIVE ANY, LIABILITY OR DAMAGES UNDER THIS AGREEMENT.
   You hereby release Datavail from any claims, causes of action, losses, damages, costs and expenses resulting from your downloading or other use of the Script.
5. AGREEMENT. These terms and conditions constitute your complete and exclusive legal agreement between you and Datavail.