Rename SQL Server Filenames on Disk

3/18/2013 1:39:44 AM

The following is to change the name(s) of the filenames for a SQL Server database.

--1. Rename the Database (using SSMS - right click db -> Rename) -=*Remember the old db name

--2. Change Logical Db File names (not file names on disk)
--The assumption from here down is that you have a 1 data file and 1 log file. If you are unsure, right click your db -> Properties -> Files to see exactly what files with their proper logical filename and location you have/

alter database NewDatabaseName modify file (name='MyOldDatabaseName', newname='NewDatabaseName')
alter database NewDatabaseName modify file (name='MyOldDatabaseName_log', newname='NewDatabaseName_log')
 
--3. Take Db offline (using SSMS - right click db -> Tasks -> Take Offline)

--4. Change Db Reference to Files
ALTER DATABASE NewDatabaseName  MODIFY FILE (NAME = NewDatabaseName, 
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\NewDatabaseName.mdf') --Path May Vary
GO
 
--if changing log file name
ALTER DATABASE  NewDatabaseName MODIFY FILE (NAME = NewDatabaseName_log, FILENAME =
'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\NewDatabaseName_log.ldf') --Path May Vary
GO

--5. Rename Db Files on disk

--6. Bring Db back online (using SSMS - right click db -> Tasks -> Bring Online)