How To Rename .mdf and .ldf files

How To Rename .mdf and .ldf files

This is simple but useful post about renaming the mdf and ldf file of Sql Server Database. First time when I was doing same I followed the below steps:

1: I detached the database From SQL Server Instance,

2: Renamed the files,

3: Then I tried to re-attach the database, I was getting an error.

And then I followed the steps provided by MSDN.

In this example I am going to change the name of .mdf file as “VimalTest.mdf” from “VIMAL.mdf” and log file as “VimalTest_log.ldf “ from VIMAL_LOG.ldf” .

Let see the name of database before renaming; 

Here are files at OS Level:

Now make your database Offline using below TSQL or use SSMS->databasename->Tasks>TakeOffline
ALTER DATABASE VIMAL SET OFFLINE

--Where VIMAL is Database name.

Now Run the below:
--For Data File
ALTER DATABASE VIMAL MODIFY FILE (NAME =VIMAL, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\VimalTest.mdf')

GO
--For Log File
ALTER DATABASE VIMAL MODIFY FILE (NAME = VIMAL_log, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\VimalTest_LOG.Ldf')

GO

Now rename both the data and log files at the OS level ,which are lying at:

'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\

After renaming it will look like:

Now bring database online using: 

 ALTER DATABASE Vimal SET ONLINE

 GO

Now Let see the description of our database i.e. Vimal using Sp_helpdb 'Vimal' .

Its done... B I N G O :)

Thanks!!!!!