Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Rename SQL Server physical database files using T-SQL


By:   |   Read Comments (1)   |   Related Tips: 1 | 2 | 3 | 4 | More > Database Administration

Problem

In a previous tip, I demonstrated how to rename physical database files for a SQL Server database using SQL Server Management Studio. In this tip, I will demonstrate how to do this same process using T-SQL code.

Solution

The physical database file name is the name of the actual file which is created in your operating system directories during database creation. Here, I will show the step by step method to rename a physical database file by using the detach-attach method with T-SQL commands.

NOTE: BE SURE TO TAKE A FULL BACKUP OF THE DATABASE BEFORE MAKING ANY CHANGES. DO NOT MAKE ANY CHANGES IN PRODUCTION WITHOUT PROPER TESTINGS IN LOWER-LIFE CYCLE ENVIRNOMENTS.

Rename the Physical Database File Name of a SQL Server Database using T-SQL

Step 1 - Check the Current Physical SQL Server Database File Names

We created a database in a previous tip named Manvendra which we will use for this demonstration.

Check the physical database file names and the location of your database files. Run the below T-SQL command to get this information.

USE Manvendra
GO

SELECT file_id, name as [logical_file_name], physical_name
FRFROM sys.database_files

Check physical file names of the SQL Server database in Management Studio

Step 2 - Detach the SQL Server Database

We can see the physical database file names in the above screenshot, now we will detach this database to make changes to the database file names. Run the below T-SQL commands to get this done. If there is any critical transactions running it's recommended to let them complete first then go ahead with this step.

ALTER DATABASE Manvendra SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

sp_detach_db 'Manvendra'

Detach the SQL Server Database

Once the above commands complete, the database will be detached. You can validate whether the database is detached by refreshing the database folder in SQL Server Management Studio. You can see the database is not showing in the Databases folder below.

Validate the SQL Server database has been detached

Step 3 - Rename SQL Server Database Physical Files in Windows

Now go to the database file location and rename the database files accordingly. Below are screenshot of the database files which we are going to rename.

Review the SQL Server database file names

Below is a screenshot of the database files after renaming.

Rename the SQL Server database files

Step 4 - Attach the SQL Server Database

Now, the next step is to attach the database using the renamed database files. Run the below command to attach the database using the new file names.

USE master
GO

CREATE DATABASE [Manvendra] ON
( NAME = N'Manvendra', FILENAME = N'F:\MSSQL12.MSSQLSERVER\MSSQL\DATA\Manvendra.mdf'),
( NAME = N'Manvendra_1', FILENAME = N'F:\MSSQL12.MSSQLSERVER\MSSQL\DATA\Manvendra_1.ndf'),
( NAME = N'Manvendra_2', FILENAME = N'F:\MSSQL12.MSSQLSERVER\MSSQL\DATA\Manvendra_2.ndf'),
 ( ( NAME = N'Manvendra_log', FILENAME = N'F:\MSSQL12.MSSQLSERVER\MSSQL\DATA\Manvendra_log.ldf')
FOR ATTACH
GO

Attach the SQL Server database

Step 5 - Validate the SQL Server Database Changes

Now we will validate the change and whether the database is attached and operational. Run the below command to check the database status.

SELECT name as [Database_Name], State_desc from sys.databases

Validate the SQL Server database has been attached

As we can see database is attached to the SQL Server instance and is operational.  Our final step is to validate that the database file names were changed. Run the below command which we ran in step 1 to validate this change.

USE Manvendra
GO
SELECT file_id, name as [logical_file_name], physical_name
FROM sys.database_files

Validate the SQL Server database changes

We can see the database file names have been changed.

Next Steps
  • Follow the proper change process while implementing this change.
  • Do not change physical database file names of your production database until it is required or needed.
  • First test this process in a lower life cycle environment and then replicate the change in production once approved.
  • Explore more knowledge on SQL Server Database Administration Tips.


Last Update:






About the author





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Tuesday, September 27, 2016 - 4:01:05 AM - Thomas Franz Back To Top

Why do you not use something like

ALTER DATABASE Manvedra MODIFY FILE ( NAME = manvedra, FILENAME = 'c:\temp\my_new_manvedra.mdf')

after you take the database offline and moved / renamed the file(s)?


Learn more about SQL Server tools