Wednesday, January 26, 2011

Rename .MDF File...

I have been working on a project where the database files had to be rename. The name of the files had to be consistent with the database. Initially I detached the database, renamed the files in Explorer, then tried to re-attach the database, I was getting an error. I came across the following steps (thanks to MSDN news groups) and implemented them, the renaming of the database worked well. This was done on SQL Server Version 2008 R2. In the example the db.mdf and db.ldf file will point to the newly named files.

Note: The following feature does not work in SQL Server 2000.

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
--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.


ALTER DATABASE databaseName SET ONLINE

GO

4 comments:

  1. As a note this doesn't seem to work with MS SQL 2000. The error message is "Database 'databaseName' cannot be opened because it is offline.'

    I couldn't find a good description of what 'offline' really means. I could understand it allow you do manipulate the filename. And I can understand it not allowing you to do much. Seems like MS might have moved from one idea to the other over time.

    ReplyDelete
  2. Thank you Michael for your comments. I should include the note you mentioned about not working in SQL Server 2000. The features listed in the post works in later versions of sql server.

    ReplyDelete
  3. Thanks a lot.. it worked for me..

    ReplyDelete