Mediumcube.com Blog Information about technologies at Mediumcube and our services

July 5, 2013

How to rename MSSQL database MDF and LDF files

Filed under: Technical — admin @ 12:31 pm

This is a step by step tutorial on how to change the MS SQL databse file names (file.mdf and file.ldf) or their location. The items in red need to be replaced with the names used in your database. This has been tested on SQL 2005/2008/2012.

The method will work for renaming the whole DB, its files and logical unitsĀ  or you can use Steps 3-6 only to change the location of the .mdf and .ldf files:

1) Rename the actual database MYDB ==> MYDBold in the SSMS (SQL Studio Manager) (This is only necessary if you want to rename the actual DB as well)

2) Open Query window for the MYDBold and Change the logical names in the DB. These names can be found from right clicking on the DB -> Properties -> Files tab:
ALTER DATABASE MYDBold MODIFY FILE (NAME = MYDB_data, NEWNAME = MYDBold_data);
ALTER DATABASE MYDBold MODIFY FILE (NAME = MYDB_log, NEWNAME = MYDBold_log);

3) Alter files for DB, changing to the new .mdf and .ldf file location:
GO
ALTER DATABASE MYDBold MODIFY FILE (NAME =MYDB_Data, FILENAME = ‘D:\SQL_Data\MYDBold.mdf‘)

GO

ALTER DATABASE MYDBold MODIFY FILE (NAME = MYDBold_log, FILENAME =’D:\SQL_Data\MYDBold.ldf‘)

GO

4) Take the DB offline in SSMS (Right click on the DB -> Tasks -> Take Offline). (If DB takes very long to go offline, you can try to Detach with Drop/Update checked). This will allow the DB to go offline.

5) Change the actual physical file names on the hard drive

6) Bring the DB back online, and whola! Your DB is now attached to the new storage device

No Comments

No comments yet.

RSS feed for comments on this post. TrackBack URL

Sorry, the comment form is closed at this time.

Powered by WordPress