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

Answer 1

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.

Answer 2

1. Detach (right click on database)

2. Rename both files (ldf and mdf) : C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA

3. Attach (right click on "Databases" top folder)

Answer 3

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.

Answer 4

1. Backup the original database
2. Drop the original database
3. 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.

Answer 5

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

1. Open and connect to the SQL server where the database you wanted to rename is located.
2. 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);

3. And then Right click on the OldDatabaseName, select Tasks and then choose Take Offline

4. 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.
5. Go back to Microsoft SQL Server Management Studio. Right click on the OldDatabaseName, select Tasks and then choose Bring Online.
6. Finally, go ahead and rename your OldDatabaseName to the NewDatabaseName. You are done :-)

Answer 6

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

Answer 7

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