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

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

August 21, 2011 Leave a comment Go to 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
  1. Sanjeet Kumar
    January 19, 2013 at 9:40 am
    Reply

    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

    • Vishal Pawar
      February 3, 2013 at 12:37 pm
      Reply

      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

  2. Jyothi
    January 3, 2014 at 12:57 pm
    Reply

    Thanks,Its very clear,but taking lots of time to run this ALTER DATABASE DBNAME SET OFFLINE

    • Vishal Pawar
      January 5, 2014 at 1:05 pm
      Reply

      True..Jyothi
      Also depends on how big your DB and some your infrastructure support..
      Thanks for stop by and Comments ..

  1. August 22, 2011 at 1:55 am
    Link Resource # 18 : Aug 19 – Aug 22 « Dactylonomy of Web Resource

Leave a Reply

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. ( Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out /  Change )

Cancel

Connecting to %s

%d bloggers like this: