Wednesday, January 9, 2013

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 !!

No comments:

Post a Comment