How to rename the Physical Database Files

Question

I have used tsql to detach a database like this:

EXEC sp_detach_db @dbname = 'my_db'

I then made use of PHP to rename the physical files. I was able to rename the mdf file but not the ldf file! I even tried a dos command REN but that didn't work for the ldf file either!

I wanted to ask, is there something special about the physical log files that allow it not to be renamed?

Is there a better way of doing this?

Thanks all

I am trying to do a proper re-name. I can't create a database with the same name once I have detached a database since the physical file names are still the same. — Abs, Jan 21 '11 at 12:09
Related [Changing the name of a SQL database](http://stackoverflow.com/questions/4080430) — James Jenkins, Oct 21 '16 at 14:41

score 19 · Answer 1 · answered Aug 29 '15 at 14:07

19

Detach the Database, Rename the files, Attach it again.

answered Aug 29 '15 at 14:07

Babak

237
2
6

Only thing is make a note of the path and file name of the .mdf,.ndf and .ldf files associated with your DB before you perform the detach operation. If you have multiple databases who are creating their data, secondary data and log files at the same default SQL Server install location then you might get confused later on while reattaching the DB. – RBT May 30 '16 at 08:59
4

You can use the SSM GUI detach, use the [windows explorer to rename the files,](http://stackoverflow.com/questions/4080430/changing-the-name-of-a-sql-database/18324583#18324583) but you need to use [TSQL to re-attach](https://msdn.microsoft.com/en-us/library/ms190209.aspx) – James Jenkins Oct 21 '16 at 14:21
For anyone using SQL Server on Linux you may need to use the sqlcmd tool from the command line to re-attach as per [Microsoft documentation](https://learn.microsoft.com/en-us/sql/relational-databases/databases/attach-a-database?view=sql-server-ver15#TsqlProcedure). I tried through SSMS and had no luck – The Thirsty Ape Apr 16 '20 at 19:13
I found this simple approach worked (SSMS 2012) and there was no need to run scripts or do anything else as per the other suggestions. – Fandango68 Feb 01 '23 at 05:58
It may have worked, but not with version 15. – Gustav Jul 27 '23 at 14:10

score 16 · Answer 2 · answered Nov 13 '18 at 19:41

16

Backup the original database
Drop the original database
Restore the original database from the backup, but with different name; the files of the restored database will be also automatically named taking into account new database name.

answered Nov 13 '18 at 19:41

Sevenate

6,221
3
49
75

3

So simple! Thanks. – MikeZ May 15 '21 at 19:59
For some reason logical file name remains the same (in my case) but that's not a problem since physical file name really changes – Brains Sep 12 '21 at 02:52
This did not work for me at all. – Doug Kimzey Jun 12 '22 at 13:09

sverrehundeide · Answer 3 · 2013-06-27T13:16:03.843

The "ALTER DATABASE (your database) MODIFY FILE" command will only rename the logical names. This post shows how to use xp_cmdshell to also rename the physical files: http://www.mssqltips.com/sqlservertip/1891/best-practice-for-renaming-a-sql-server-database/

Please note the following:
1. xp_cmdshell will be executed under the user which the SQL Server process runs as, and might not have the file system permissions required to rename the database files
2. For security reasons, remember to disable xp_xmdshell

The following is an example of how the renaming can be done based on the mentioned blog post. It will replace the database MyDB with the database NewMyDB. The original MyDB (renamed to MyDB_OLD) will be left detached.

-- Enable xp_cmdshell:
sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE
GO

-- Get physical file names:
declare @MyDBOriginalFileName nvarchar(300) = (select physical_name FROM sys.master_files where name = 'MyDB')
declare @MyDBLogOriginalFileName nvarchar(300) = (select physical_name FROM sys.master_files where name = 'MyDB_log')
declare @NewMyDBOriginalFileName nvarchar(300) = (select physical_name FROM sys.master_files where name = 'NewMyDB')
declare @NewMyDBLogOriginalFileName nvarchar(300) = (select physical_name FROM sys.master_files where name = 'NewMyDB_log')
declare @Command nvarchar(500)
declare @Sql nvarchar(2000)

IF (EXISTS (select * from sys.databases where name = 'NewMyDB') 
AND EXISTS (select * from sys.databases where name = 'MyDB'))
BEGIN
    USE master

    ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    ALTER DATABASE NewMyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

        -- Set new database name
        ALTER DATABASE MyDB MODIFY NAME = MyDB_OLD
        ALTER DATABASE NewMyDB MODIFY NAME = MyDB

        -- Update logical names
        ALTER DATABASE MyDB_OLD MODIFY FILE (NAME=N'MyDB', NEWNAME=N'MyDB_OLD')
        ALTER DATABASE [MyDB] MODIFY FILE (NAME=N'NewMyDB', NEWNAME=N'MyDB')

        EXEC master.dbo.sp_detach_db @dbname = N'MyDB_Old'
        EXEC master.dbo.sp_detach_db @dbname = N'MyDB'

        -- Rename physical files
        SET @Command = 'RENAME "' + @MyDBOriginalFileName + '" "MyDB_OLD.mdf"'; PRINT @Command
        EXEC xp_cmdshell @Command
        SET @Command = 'RENAME "' + @MyDBLogOriginalFileName + '" "MyDB_OLD_log.mdf"'; PRINT @Command
        EXEC xp_cmdshell @Command
        SET @Command = 'RENAME "' + @NewMyDBOriginalFileName + '" "MyDB.mdf"'; PRINT @Command
        EXEC xp_cmdshell @Command
        SET @Command = 'RENAME "' + @NewMyDBLogOriginalFileName + '" "MyDB_log.mdf"'; PRINT @Command
        EXEC xp_cmdshell @Command

        -- Attach with new file names
        declare @NewMyDBFileNameAfterRename nvarchar(300) = replace(@NewMyDBOriginalFileName, 'NewMyDB',  'MyDB')
        declare @NewMyDBLogFileNameAfterRename nvarchar(300) = replace(@NewMyDBOriginalFileName, 'NewMyDB_log',  'MyDB_log')
        SET @Sql = 'CREATE DATABASE MyDB ON ( FILENAME = ''' + @NewMyDBFileNameAfterRename + '''), ( FILENAME = ''' + @NewMyDBLogFileNameAfterRename + ''') FOR ATTACH'
        PRINT @Sql
        EXEC (@Sql)

    ALTER DATABASE MyDB SET MULTI_USER 

END

-- Disable xp_cmdshell for security reasons:
GO
sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'xp_cmdshell', 0
RECONFIGURE WITH OVERRIDE
GO

Please, extend your answer with some Extract/Summary from the link provided. The link itself is not the answer. And also ... link could become obsolete — Radim Köhler, Jun 27 '13 at 08:10

score 9 · Answer 4 · edited Mar 24 '19 at 03:41

9

You can do it using an ALTER DATABASE statement - like this:

ALTER DATABASE database_name
   MODIFY FILE ( NAME = logical_file_name, 
                 FILENAME = ' new_path/os_file_name_with_extension ' )

You need to modify each file separately, e.g. if you have multiple data files, you need to modify each of those.

For details, see the Technet documentation on this topic.

edited Mar 24 '19 at 03:41

Shiva

20,575
14
82
112

answered Jan 21 '11 at 12:25

marc_s

732,580
175
1,330
1,459

For that to actually work, I have to manually move the file or rename the file, correct? SQL Server will not do this for me and this is the bit I am trying to automate. – Abs Jan 21 '11 at 12:58
4

Exactly, after you execute above command you need to switch database offline, physically rename the file and switch it back online. – ulath Jan 14 '15 at 09:06

score 8 · Answer 5 · answered Nov 25 '16 at 01:57

The simplest way to rename SQL server physical database files is:

Open and connect to the SQL server where the database you wanted to rename is located.
Execute the following script in the query window in order to change the physical and logical names. Remember to replace all the "OldDatabaseName" with the new name of the database ("NewDatabaseName") you want to change its name to. Replace all NewDatabaseName with the new name you want to set for your database

use OldDatabaseName

ALTER DATABASE OldDabaseName MODIFY FILE (NAME='OldDatabaseName', FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\NewDatabaseName.mdf');

ALTER DATABASE OldDatabaseName MODIFY FILE (NAME='OldDatabaseName_log', FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\NewDatabaseName_log.ldf');

ALTER DATABASE OldDatabaseName MODIFY FILE (NAME = OldDatabaseName, NEWNAME = NewDatabaseName);
ALTER DATABASE OldDatabaseName MODIFY FILE (NAME = OldDatabaseName_log, NEWNAME = NewDatabaseName_log);

And then Right click on the OldDatabaseName, select Tasks and then choose Take Offline
Go to the location (C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\...) where the physical files are located and rename them to the NewDatabaseName you specified in number 2. Remember to check the absolute path of these files to be used on your computer.
Go back to Microsoft SQL Server Management Studio. Right click on the OldDatabaseName, select Tasks and then choose Bring Online.
Finally, go ahead and rename your OldDatabaseName to the NewDatabaseName. You are done :-)

You can also do `ALTER DATABASE NewDatabaseName SET OFFLINE` and then do the filename changes before doing `ALTER DATABASE NewDatabaseName SET ONLINE`. — cusman, Feb 13 '17 at 16:34
This will only work if SSMS has file access to the DATA folder. — Gustav, Jul 27 '23 at 14:19

John Smith · Answer 6 · 2021-08-01T00:00:32.497

2

Detach (right click on database)
Rename both files (ldf and mdf) : C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA
Attach (right click on "Databases" top folder)

edited Aug 01 '21 at 00:00

answered Jul 27 '21 at 22:05

John Smith

31
4

1

Wow - I really wish I could get this to work... – Doug Kimzey Jun 12 '22 at 13:20
It may have worked, but not with version 15. – Gustav Jul 27 '23 at 14:09

score 0 · Answer 7 · answered Apr 25 '23 at 23:34

I came across this old thread, and none of the answers quite did what I wanted, and I overlooked Sverre's for some reason. I put this together since I needed to clean up a lot of horrible inconsistent lower-env SQL server database file placement and consolidate disk/folder structures. Hope this helps someone.

-- create a script that generates necessary commands to relocate a set of databases from
-- one location to another and some other stuff
-- 1) offline database
-- 2) alter databases modify files
-- 3) generates OS file move commands
-- 3.5) includes renaming the files to match database AND file type/enumeration
-- 4) online databaases databases using create database for attach commands

--== declare some configuration variavbles ==--
DECLARE @newpath            SYSNAME = 'G:\DefaultInstance\QA'
DECLARE @databasewildcard   SYSNAME = '%_NEW'

--== start with a commands table ==--
IF OBJECT_ID('tempdb..#commands') IS NOT NULL DROP TABLE #commands
CREATE TABLE #commands ([id] INT IDENTITY, [command] VARCHAR(MAX))

IF OBJECT_ID('tempdb..#excludedatabases') IS NOT NULL DROP TABLE #excludedatabases
CREATE TABLE #excludedatabases ([id] INT IDENTITY, [database] SYSNAME)

INSERT INTO #excludedatabases ([database]) VALUES ('exceptiondatabase_NEW')

--== insert commands : offline databases ==--
INSERT INTO #commands ([command])
    SELECT
     --'EXEC master.dbo.sp_detach_db @dbname = N' + QUOTENAME(sd.[name],'''')+ ';'
     'USE [master]; ALTER DATABASE ' + QUOTENAME(sd.[name]) + ' SET OFFLINE WITH ROLLBACK IMMEDIATE;'
    FROM sys.databases sd
    WHERE sd.[name] LIKE @databasewildcard
    AND sd.[name] NOT IN    (SELECT
                             ed.[database]
                            FROM #excludedatabases ed)

--== insert commands : modify logical physical file name ==--
INSERT INTO #commands ([command])   
    SELECT
     'ALTER DATABASE ' + QUOTENAME(sd.[name]) + ' MODIFY FILE (NAME = N' + QUOTENAME(smf.[name],'''') + ', FILENAME = N' + QUOTENAME(@newpath + '\'
                                                                    +   CASE
                                                                            WHEN smf.[type_desc] = 'ROWS'   THEN sd.[name] + '_data_file' + RIGHT('00' + CAST(smf.[file_id] AS VARCHAR),2) + '.mdf'
                                                                            WHEN smf.[type_desc] = 'LOG'    THEN sd.[name] + '_log_file' + RIGHT('00' + CAST(smf.[file_id] AS VARCHAR),2) + '.ldf'
                                                                        END
                                                                    ,'''') + ');'
    FROM sys.master_files smf
    JOIN sys.databases sd ON smf.[database_id] = sd.[database_id]
    WHERE sd.[name] LIKE @databasewildcard
    AND sd.[name] NOT IN    (SELECT
                             ed.[database]
                            FROM #excludedatabases ed)

--== insert commands : generate OS move commands ==--
INSERT INTO #commands ([command]) VALUES ('/* --===== comment out these commands as a block - execute in file system =====--')

INSERT INTO #commands ([command])
    SELECT
     --'MOVE ' + QUOTENAME(smf.[physical_name],'"') + ' ' + QUOTENAME(@newpath + '\','"') AS [command]
     'MOVE ' + QUOTENAME(smf.[physical_name],'"') + ' ' + QUOTENAME(@newpath + '\'
                                                                    +   CASE
                                                                            WHEN smf.[type_desc] = 'ROWS'   THEN sd.[name] + '_data_file' + RIGHT('00' + CAST(smf.[file_id] AS VARCHAR),2) + '.mdf'
                                                                            WHEN smf.[type_desc] = 'LOG'    THEN sd.[name] + '_log_file' + RIGHT('00' + CAST(smf.[file_id] AS VARCHAR),2) + '.ldf'
                                                                        END
                                                                    ,'"') AS [command]
    FROM sys.master_files smf
    JOIN sys.databases sd ON smf.[database_id] = sd.[database_id]
    WHERE sd.[name] LIKE @databasewildcard
    AND sd.[name] NOT IN    (SELECT
                             ed.[database]
                            FROM #excludedatabases ed)

INSERT INTO #commands ([command]) VALUES ('*/ --===== comment out these commands as a block - execute in file system =====--')

--== insert commands : online databases ==--
INSERT INTO #commands ([command])
    SELECT
     'USE [master]; ALTER DATABASE ' + QUOTENAME(sd.[name]) + ' SET ONLINE WITH ROLLBACK IMMEDIATE;'
    FROM sys.databases sd
    WHERE sd.[name] LIKE @databasewildcard
    AND sd.[name] NOT IN    (SELECT
                             ed.[database]
                            FROM #excludedatabases ed)

SELECT * FROM #commands

Please note that the same CAVEAT applies, I didn't use XP_CMDSHELL because permissions on our filesystems are NUTS!! So, I just generated MOVE commands to be run as a step to this work. Sadly a requirement for my environment until we get a handle on this inherited environment.

How to rename the Physical Database Files

7 Answers7

Linked