How to Rename .MDF File and .LDF 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 !!