2

I renamed a database by right clicking and "Rename" but the mdf and ldf files are not renamed.

Why aren't these files renamed? What would happen if I don't rename the mdf and ldf files?

Tom V
  • 15,670
  • 7
  • 63
  • 86
Genie in the bottle
  • 21
  • 2
  • 1
    the RENAME only rename the Database name. For Physical Filename you have to do it separately – Squirrel Aug 04 '16 at 08:40
  • Why do you want to rename the files? The file name won't affect the operation of the database, or backup/restore operations at all. Renaming the file also means that the database must be taken offline. Perhaps you should leave this to a DBA – Panagiotis Kanavos Aug 04 '16 at 08:45
  • @PanagiotisKanavos: It is recommended that the physical filenames match up with the database name where possible. It can sometimes help admins know what is where and doesn't harm anything. Unfortunately as you point out renaming the files requires you to temporarily detach or otherwise offline the database so it can't be done in production outside your planned maintenance windows. – David Spillett Aug 04 '16 at 13:02
  • 1
    @DavidSpillett the point is that if there is confusion about the database and file names, it's time to call a DBA. On a dev machine there may be only one database file. On a production environment, the same database may use multiple files. Taking the database offline on a dev machine is OK. Doing so in production is not. – Panagiotis Kanavos Aug 04 '16 at 13:40

4 Answers4

6
  1. you could change (rename) file name for MDF and LDF during database restoring
  2. What would happen if I don't rename the mdf and ldf files. nothing, everything will keep working as before.
Tom V
  • 15,670
  • 7
  • 63
  • 86
Zam
  • 176
  • 1
  • 3
  • 2
    I suggest the physical files be renamed to reflect the database name, either during the restore or afterwards. SQL Server doesn't care what the file name is but consistent naming facilitates manageability. – Dan Guzman Aug 04 '16 at 11:20
  • 1
    I recommend setting the logical names of the two files based on the database name for consistency also. – CoveGeek Aug 12 '16 at 00:21
1

Nothing will happen, it will carry on working just as before only with a new logical DB name.

IntoNET
  • 111
  • 2
1

From a server / software point of view changing filenames isn't needed, everything will work as supposed.

Human management is a bit different. It's rather easy to forget that database name was changed and filenames weren't, more of when database lacks proper documentation. Besides it's a good thing to have db and filename names consistent, as it lowers chance for some errors on human side.

This guide might help You if You will want to change filenames (both logical and physical): How do I move SQL Server database files?

Community
  • 1
Marcin S.
  • 723
  • 4
  • 15
1

I know I'm a bit late to this question, but I recently had this project at work where I had to rename a bunch of the databases on several servers. I put together this script to create all the sql commands needed to perform the task. You run it on each sql server, copy the steps for each database individually and double check that everything you want to have happen is going to happen. You can then execute each step. A couple things to watch out for is to make sure that you have xp_cmdshell enabled when you run this, and that the account you run this has permissions to edit the file names on the file system. When you detach a database SQL sets it up so that only the user that detach it has rights to the mdf/ldf files. This script also assumes that you are using the database name as part of the existing logical and physical filenames. 

CREATE Table #DatabasesToRename
(
    database_id int
,   Old_Name varchar(100)
,   New_Name varchar(100)
)

CREATE Table #DatabaseFilesToRename
(
    database_id int
,   Old_Name varchar(100)
,   New_Name varchar(100)
,   OldLogicalName varchar(100)
,   NewLogicalName varchar(100)
,   [OldPath] varchar(1000)
,   [OldFilename] varchar(100)
,   NewPathName varchar(1000)
,   NewFilename varchar(100)
)

insert into #DatabasesToRename
select database_id,
name as OLDDBNAME,
    case 
              when name like '%DBName1%' then replace(name, 'DBName1', 'DBRenamed1')
              when name like '%DBName2%' then replace(name, 'DBName2', 'DBRenamed2')
            else 'Error' end as NewName
from master.sys.databases
WHERE  name like '%DBName1%' 
    or name like '%DBName2%'
order by name


insert into #DatabaseFilesToRename
select  mf.database_id, 
    dtr.Old_Name, dtr.New_Name,
    name as OldLogicalName,
    case 
              when name like '%DBName1%' then replace(name, 'DBName1', 'DBRenamed1')
              when name like '%DBName2%' then replace(name, 'DBName2', 'DBRenamed2')
              else 'Error' end as NewLogicalName,
    LEFT(physical_name,LEN(physical_name) - charindex('\',reverse(physical_name),1) + 1) [OldPath], 
       REVERSE(LEFT(REVERSE(physical_name),CHARINDEX('\', REVERSE(physical_name), 1) - 1)) [OldFilename],
    case    
          when LEFT(physical_name,LEN(physical_name) - charindex('\',reverse(physical_name),1) + 1) like '%DBName1%' then replace(LEFT(physical_name,LEN(physical_name) - charindex('\',reverse(physical_name),1) + 1), 'DBName1', 'DBRenamed1')
              when LEFT(physical_name,LEN(physical_name) - charindex('\',reverse(physical_name),1) + 1) like '%DBName2%' then replace(LEFT(physical_name,LEN(physical_name) - charindex('\',reverse(physical_name),1) + 1), 'DBName2', 'DBRenamed2')
              else 'Error' end as NewPathName,
    case    
          when  REVERSE(LEFT(REVERSE(physical_name),CHARINDEX('\', REVERSE(physical_name), 1) - 1))  like '%DBName1%' then replace( REVERSE(LEFT(REVERSE(physical_name),CHARINDEX('\', REVERSE(physical_name), 1) - 1)) , 'DBName1', 'DBRenamed1')
              when  REVERSE(LEFT(REVERSE(physical_name),CHARINDEX('\', REVERSE(physical_name), 1) - 1))  like '%DBName2%' then replace( REVERSE(LEFT(REVERSE(physical_name),CHARINDEX('\', REVERSE(physical_name), 1) - 1)) , 'DBName2', 'DBRenamed2')
          else 'Error' end as NewFilename

from master.sys.master_files as mf
join #DatabasesToRename as dtr
    on dtr.database_id = mf.database_id
order by mf.database_id, name

select *
from #DatabasesToRename
order by Old_Name

select *
from #DatabaseFilesToRename dftr
order by Old_Name, OldLogicalName



select database_id, Old_Name, New_Name, 
Step1_SingleUser = '
/* Set Database as a Single User */
ALTER DATABASE ['+Old_Name+'] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO'
from #DatabasesToRename
order by Old_Name


select database_id, Old_Name, New_Name, 
Step2_AlterLogicalNames = '
/* Change Logical File Name */
ALTER DATABASE ['+Old_Name+'] MODIFY FILE (NAME=N''' + dftr.OldLogicalName + ''', NEWNAME=N''' + dftr.NewLogicalName + ''')
GO
'
from #DatabaseFilesToRename dftr
order by Old_Name, OldLogicalName

select database_id, Old_Name, New_Name, 
Step3_Detach = '
/* Detach Database */
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'''+Old_Name+'''
GO
'
from #DatabasesToRename
order by Old_Name


select database_id, Old_Name, New_Name,
case when NewPathName = 'Error' THEN '--No path to rename: ' + OldPath
else '
/* Rename paths and filenames */
USE [master]
GO
EXEC xp_cmdshell ''RENAME "' + dftr.OldPath + '", "'+ REVERSE(LEFT(REVERSE(left(dftr.NewPathName,len(dftr.NewPathName)-1)),CHARINDEX('\', REVERSE(left(dftr.NewPathName,len(dftr.NewPathName)-1)), 1) - 1)) +'"''
GO
'
end as Step4_RenamePath,
Step5_RenamePhysicalFilename ='
/* Rename paths and filenames */
USE [master]
GO
EXEC xp_cmdshell ''RENAME "' + case when dftr.NewPathName = 'Error' THEN dftr.OldPath else dftr.NewPathName end + dftr.OldFilename + '", "'+ dftr.NewFilename +'"''
GO
'
from #DatabaseFilesToRename dftr
order by Old_Name, OldLogicalName


select dtr.database_id, dtr.Old_Name, dtr.New_Name,
Step6_AttachDatabase = '/* Attach Renamed Test Database Online */
USE [master]
GO
CREATE DATABASE '+ dtr.New_Name +' ON 
( FILENAME = N'''+case when dftrd.NewPathName = 'Error' THEN dftrd.OldPath else dftrd.NewPathName end + dftrd.NewFilename +''' ),
( FILENAME = N'''+case when dftrl.NewPathName = 'Error' THEN dftrl.OldPath else dftrl.NewPathName end + dftrl.NewFilename +''' )
FOR ATTACH
GO' 

from #DatabasesToRename dtr
join #DatabaseFilesToRename dftrd --mdf/ndf files
    on dftrd.database_id = dtr.database_id
    and (dftrd.NewFilename like '%.mdf' OR dftrd.NewFilename like '%.ndf')
join #DatabaseFilesToRename dftrl --log files
    on dftrl.database_id = dtr.database_id
    and dftrl.NewFilename like '%.ldf'
order by dtr.Old_Name


select database_id, Old_Name, New_Name, 
Step7_MultiUser = '
/* Set Database as a Single User */
ALTER DATABASE ['+New_Name+'] SET MULTI_USER'
from #DatabasesToRename
order by Old_Name




---------Clean up
DROP TABLE #DatabasesToRename
DROP TABLE #DatabaseFilesToRename
Tim J
  • 163
  • 1
  • 7