Archive

Archive for the ‘SQL’ Category

BISQL # 45 : How to Rename .MDF File and .LDF file ? (i.e – .mdf , .ldf)

August 21, 2011 5 comments

 

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

image

It have MDF file and LDF file as shown in following drives @ physical location

image

Fire a following query first

ALTER DATABASE AuditTest SET OFFLINE

It will make off the Database as shown

image

Now till the time your database is off we have to go manually and make rename option to both MDF and LDF file

image 

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

image

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

            | FaceBook |Twitter | LinkedIn| Google+ | WordPress | RSS |

                                  Copyright © 2011 – 2012 Vishal Pawar

Categories: Query, SQL