How to rename a database along with its datafile in SQL Server

SQL Server Version: SQL server 2012
DB Name: soumya

Step 1.First find out the datafile details of the db:-

sp_helpdb soumya
soumya    1    E:\MSSQLSERVER\MSSQL11.MSSQLSERVER\MSSQL\DATA\soumya.mdf    PRIMARY    6144 KB    Unlimited    1024 KB    data only
soumya1_log    2    E:\MSSQLSERVER\MSSQL11.MSSQLSERVER\MSSQL\DATA\soumya_1.ldf    NULL    4224 KB    2147483648 KB    10%    log only


To rename the existing db:-
EXEC sp_renamedb 'soumya', 'test'

or you can simply right click on the database and rename it from ssms.

Once rename database has been done

Step 2:-Put the database into single usermode so we can change the datafile of that particular database.


ALTER DATABASE test SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

Step 3:- Detach the database.

USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'test'
GO

Step 4:-Now Rename the physical files from OS level according to the new db name.
Once renaming is done, attach physical files, attach the database, using the following T-SQL script:

USE [master]
GO
CREATE DATABASE test ON
( FILENAME = N'E:\MSSQLSERVER\MSSQL11.MSSQLSERVER\MSSQL\DATA\test.mdf' ),
( FILENAME = N'E:\MSSQLSERVER\MSSQL11.MSSQLSERVER\MSSQL\DATA\test_1.ldf' )
FOR ATTACH
GO

Step 5:-To change the logical name of database file:-

ALTER DATABASE test MODIFY FILE (NAME=N'soumya', NEWNAME=N'test')
GO
ALTER DATABASE test MODIFY FILE (NAME=N'soumya_1', NEWNAME=N'test_1')
GO

Step 6.Now check the db details.

sp_helpdb test

test    1    E:\MSSQLSERVER\MSSQL11.MSSQLSERVER\MSSQL\DATA\test.mdf    PRIMARY    6144 KB    Unlimited    1024 KB    data only
test1_log    2    E:\MSSQLSERVER\MSSQL11.MSSQLSERVER\MSSQL\DATA\test_1.ldf    NULL    4224 KB    2147483648 KB    10%    log only



Please share your ideas and opinions about this topic.

If you like this post, then please share it with others.
Please subscribe on email for every updates on mail.