Renaming the Physical Filename for Datafiles in SQL Server 2008

Can anyone tell me how to change the physical filename of the datafiles? There doesn't seem to be any documentation on this, yet its quite easy to change the logical filename.

February 2nd, 2010 6:21pm


 There are several ways to make this change, however to rename the physical database files at operating system level you will have to take the database offline

1. Use SSMS to take the database Offline (right-click on Database, select Tasks, Take Offline), change the name of the files at the OS level and then Bring it Online.

2. You could Detach the database, rename the files and then Attach the database pointing to the renamed files to do so.

3. You could Backup the database and then restore, changing the file location during the restore process.

4. using T SQL

ALTER DATABASE databaseName SET OFFLINE
GO


ALTER DATABASE databaseNAme MODIFY FILE (NAME =db, FILENAME = 'C:\Program
Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\db.mdf')
GO

--if changing log file name

ALTER DATABASE  databaseNAme MODIFY FILE (NAME = db_log, FILENAME =
'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\db.ldf')
GO

ALTER DATABASE databaseName SET ONLINE
GO

for more info http://technet.microsoft.com/en-us/library/ms174269.aspx

  • Edited by Chirag Shah Tuesday, February 02, 2010 4:20 PM typo
  • Proposed as answer by LekssEditor Tuesday, February 02, 2010 4:27 PM
  • Unproposed as answer by LekssEditor Tuesday, February 02, 2010 4:32 PM
  • Proposed as answer by LekssEditor Tuesday, February 02, 2010 4:35 PM
  • Marked as answer by Alex Feng (SQL)Moderator Tuesday, February 09, 2010 2:32 AM
Free Windows Admin Tool Kit Click here and download it now
February 2nd, 2010 7:17pm

Before you set the database online , you would have to manually change the filename at the OS level.
Failing on this part will not let you to start up the database.

February 2nd, 2010 7:36pm

 

Hello

I have using T SQL to rename on SQL Server 2008 R2 64bit as described in alternative 4 but I could not get it to work.  Is there something else that I need to do first?

 

Thanks

Free Windows Admin Tool Kit Click here and download it now
February 16th, 2011 6:36pm

 

Hello

I have using T SQL to rename on SQL Server 2008 R2 64bit as described in alternative 4 but I could not get it to work.  Is there something else that I need to do first?

 

September 4th, 2012 10:36am

Where it says NAME=db and NAME=db_log these aren't the names of your database but the logical names of your files for the database.

Al

Free Windows Admin Tool Kit Click here and download it now
February 20th, 2015 3:47am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics