Rename SQLServer Database

Steps:


Open Microsoft SQL Server Management Studio.
Connect to the server where in the DB you want to rename existing database.
Modify the following script accord to Db name and run it –
Old- TestLocalDB
Rename to – Testdb_DEV

USE [TestLocalDB];

ALTER DATABASE TestLocalDB MODIFY FILE (NAME = ‘TestLocalDB’, FILENAME = ‘C:\Program Files\Microsoft SQL Server\localServer\MSSQL\DATA\Testdb_DEV.mdf’);
ALTER DATABASE TestLocalDB MODIFY FILE (NAME = ‘TestLocalDB_log’, FILENAME = ‘C:\Program Files\Microsoft SQL Server\localServer\MSSQL\DATA\Testdb_DEV_log.ldf’);
ALTER DATABASE TestLocalDB MODIFY FILE (NAME = TestLocalDB, NEWNAME = Testdb_DEV);

ALTER DATABASE TestLocalDB MODIFY FILE (NAME =TestLocalDB_log, NEWNAME= Testdb_DEV_log);

Right click on the DB and select Tasks>Take Offline.
Go to the location that MDF and LDF files are located and rename them exactly as you specified in first two alter commands. If you changed the folder path, then you need to move them there.

Go back to Microsoft SQL Server Management Studio and right click on the DB and select Tasks>Bring Online.
Now  rename DB to the new name.

Other way:
On Old Db..Take Offline.
Go to the path of  old db mdf file. Rename the mdf file with new DB name take back up/copy at other place.
Delete the Old Db.
Create new Db(blank) with new name.
Detach the new db(blank).. Replace the mdf files with back up file taken in step 1.
Then attach the new db.

Posted in SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s