BISQL # 45 : How to Rename .MDF File and .LDF file ? (i.e – .mdf , .ldf)
Hi Friends,
Yes !! You have read true heading we can rename MDF file
Some time in project lifecycle will definitely required to rename .MDF file , Mostly during deployment at various stages.
Lets see how we can achieve same !!
For example we have Audit Test as database in our server as shown
It have MDF file and LDF file as shown in following drives @ physical location
Fire a following query first
ALTER DATABASE AuditTest SET OFFLINE
It will make off the Database as shown
Now till the time your database is off we have to go manually and make rename option to both MDF and LDF file
But we have register this or make SQL server aware of this event so now type alter script execute the following script in SSMS
GO ALTER DATABASE AuditTest MODIFY FILE (NAME =AuditTest, FILENAME = 'C:\Program Files\Microsoft SQL Server\ MSSQL10_50.MSSQLSERVER\MSSQL\DATA\RenameAudit.mdf') GO ALTER DATABASE AuditTest MODIFY FILE (NAME = AuditTest_log, FILENAME ='C:\Program Files\Microsoft SQL Server\ MSSQL10_50.MSSQLSERVER\MSSQL\DATA\RenameAudit.ldf') GO
And set our Database on again for its usability
ALTER DATABASE AuditTest SET ONLINE
GO
So Output of all above script is shown
Hope this helps !!
Thanks a lot for visiting and reading Blog.
For more interesting information on SQL we can also look into similar topics such as
· BISQL # 46 : Information of SQL Server Database Services and Startup account
· BISQL # 47 : How to find Details of Any Column in Entire Database in One Query
· BISQL # 48 : How to Generate SELECT script for all tables in a Database
· BISQL # 49 : SQL Server Denali Feature # 1–FileTables #1 – Introduction,Theory
· BISQL # 50 : SQL Server Denali Feature # 1- FileTables # 2 – Demo , Scripting
Hope you will like this post on renaming of .MDF file & .LDF file.
If you really like reading my blog and understood at least few thing then please don’t forget to subscribe my blog.
If you want daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog : Link Resource Website
For More information related to BI World visit my Mentalist Blog
Link Resource Blog >> Daily Interesting links
SQL Server Mentalist >> SQL Learning Blog
Business Intelligence Mentalist >> BI World
Connect With me on
Copyright © 2011 – 2012 Vishal Pawar
SQL Server Mentalist 
Hey Dear,
Thanks for your post, I know it useful, but please give more explanation for last step.
In your post #45, when i executed ALTER DATABASE AuditTest SET ONLINE at last, my database is not going online. But gives an error message:
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file “D:\DATABASE\SHIP.mdf”. Operating system error 5: “5(Access is denied.)”.
File activation failure. The physical file name “D:\DATABASE\SHIP.ldf” may be incorrect.
Msg 945, Level 14, State 2, Line 1
Database ‘TestDB’ cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
So, please help me, provide more information –> sanjeetkumar2233@gmail.com, 9916026315
Try this
1.Run SQL Server with window user OR sa account and TRY
2.User must have permission to drived and all .. Open SSMS as admin and try
3.See is there any memory Problem with DISK
Thanks,Its very clear,but taking lots of time to run this ALTER DATABASE DBNAME SET OFFLINE
True..Jyothi
Also depends on how big your DB and some your infrastructure support..
Thanks for stop by and Comments ..