Wednesday, January 16, 2013

Raname SQL Server Database

How to rename SQL Server Database?

For Microsoft documentation, please visit this link.

Renaming database from SQL Server has limitations and securities restriction. Some of them are:

  • You cannot rename the system database.
  • You cannot rename when there are open transactions and connections.
  • You must have an ALTER rights from the database.

There are two ways how to do it. You can use:

  • By ObjectExplorer
  • Transact-SQL.

Object Explorer

Show the Object Explorer visiting the View -> Object Explorer menu. Then go to the instance of the SQL Server and expand the Databases folder. Select the database that you like to rename and press F2. The Object Explorer will then show you the editable entry for that database. Type the new name of the database and press Enter.

Below is the screenshot of how to do it.


Transact-SQL

The new way how to do it via SQL is to use the most popular ALTER DATABASE MODIFY NAME command. Below is the sample how to use it.

USE master;
GO
ALTER DATABASE AdventureWorks2013
Modify Name = Northwind;
GO

Another way to do it is to use the SP_RENAMEDB command. For Microsoft documentation please visit this link.

ALTER DATABASE AdventureWorks2013 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
SP_RENAMEDB 'AdventureWorks2013', 'Northwind'
GO
ALTER DATABASE Northwind SET MULTI_USER
GO

Note: This feature will be removed in a future version of Microsoft SQL Server.

No comments:

Post a Comment

Place your comments and ideas