How to Rename the database in SQL Server
Renaming the database involves two steps.
1. Renaming the Logical Name of files involved in the database.
2. Renaming the Physical Name of the files involved in the database.
Logical Name/ Physical Name:
Logical Name is the name given to the Data files and Log files of the database in SQL Server.
Physical Name is the name given to the Data files and Log files of the database residing on DISK.
How to find out the files which are assosicated with the database.
Connect to the database which needs to be renamed and execute the following query will give the details.
use Databasename
select * from sys.database_files
GO
Presteps to do before renaming the database:
1. Please have a healthy backup file before any database related operation is performed on the database.
2. User needs to get the exclusive access on the database to rename the logical files.
2.1 If exclusive access is not obtained, then execute the below query to get exclusive access
Use Databasename
GO
alter database Databasename set multi_user with rollback immediate;
GO
3. Now rename the Logical filenames by executing the below set of queries.
alter database Databasename Modify file (NAME='logicalfilename_of_mdf' , NEWNAME ='NEW_LOGICAL_NAME_MDF')
GO
alter
database Databasename Modify file (NAME='logicalfilename_of_ldf' , NEWNAME ='NEW_LOGICAL_NAME_LDF')
GO
Above steps should be repeated for all the logical files. Suppose if you have 'n' LDF files, then you have repeat the step 'n' times untill all the logical files are renamed.
4. Now rename the physical files of the database by executing the below set of queries.
EXEC master.dbo.sp_detach_db @dbname = N'database_name','D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\TLOGS\databasename_log.ldf',
'D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\databasename.mdf'
5. Above step has detached the database from SQL Server. Now rename the physical MDF and LDF's of the database by going to the path where the files reside on disk.
6. Now Attach the database. Detach and Attached can also be performed by GUI method in very simple way.
Attach the database:
Create database databasename ON (FILENAME ='D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\databasename.mdf'),
(FILENAME='D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\TLOGS\databasename_log.ldf') for ATTACH
Go
By performing the above steps, Renaming of database is successfully done.
Hope the above information helps.
Thanks.
