Different ways to Rename SQL Server Database


Dec 25, 2012

Introduction

In this article we will take a look at different ways in which one can rename a SQL Server Database. The steps mentioned in this article are applicable across SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 and SQL Server 2012.

Important Note: - Before renaming a SQL Server Database you need to make sure that no one is using the database. In case if anyone else is using the database then then all the existing connections needs to be closed and set the database to SINGLE USER MODE. For more information on how to close existing connection and to get exclusive access read the following article titled “How to Get Exclusive Access to SQL Server Database”.

Different ways to Rename SQL Server Database

  • Rename Database Using Database Detach and Attach Method in SQL Server
  • Rename Database Using TSQL Script in SQL Server
  • Rename Database Using SQL Server Management Studio (SSMS) in SQL Server

Rename SQL Server Database Using Database Detach and Attach Method

Use the below script to rename a SQL Server database using database detach and attach method.

Use Master
GO
sp_helpdb RenameDB
GO

EXEC sp_detach_db 'OLDDatabaseName', 'true'
GO

EXEC sp_attach_db @dbname = N'NewDatabaseName',
@filename1 = N'D:\MSSQL\Data\NewDatabaseName.mdf',
@filename2 = N'D:\MSSQL\Data\NewDatabaseName_log.ldf'

GO

Rename SQL Server Database Using TSQL Script

Using the below TSQL Script a Database Administrator can rename an existing SQL Server Database.

USE [master]
GO

ALTER
DATABASE [RenameDB]
SET
SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

ALTER DATABASE [RenameDB]
MODIFY
NAME = MyTechMantra
GO

ALTER DATABASE [MyTechMantra]
SET MULTI_USER

GO

Rename SQL Server Database Using SQL Server Management Studio (SSMS)

1. Connect to SQL Server Instance Using SQL Server Management Studio.

2. Right click the database which you want to rename and choose Properties from drop down menu.

3. In Select a Page option choose Options page and choose Restrict Access option as SINGLE_USER from the drop down menu as shown in the highlighted in the below snippet and click the OK button.

Get Database in SINGLE_USER Mode Using SSMS

4. In Open Connections dialog box, click Yes to close the existing connections.

Open Connection in SQL Server




5. Next in Object Explorer, right click the database which you want to rename and choose Rename option from the drop down menu as shown in the snippet below.

Rename SQL Server Database Using SSMS

6. Enter the Desired Database Name and then refresh the Database node to see the changes.

7. Once the database is renamed successfully, change the restrict access to MULTI_USER option (A in Step 3).

Continue Free Learning...

  • Please leave below your valuable feedback for this arSticle.
  • Feel Free to refer this article to your friends and colleagues using the below “Share this Article” option.
  • Do subscriber to our News Letter to continue your free learning.
  • Don’t forget to Like Us on Facebook and do follow us on Twitter for latest updates.

Share this Article


Geeks who read this article also read…



Follow @MyTechMantra on Twitter
We're on Facebook
Bookmark and Share

"Receive newsletters and special offers about SQL Server, BizTalk and SharePoint from MyTechMantra. We respect your privacy and you can unsubscribe at any time."