How to Rename Database in SQL Server - SQL Server / T-SQL Tutorial Part 26

Показать описание

How to Rename Database in SQL Server - SQL Server / T-SQL Tutorial

Link to post for scripts used in the video.

Scenario:

We often face the situation where we need to rename the database. Think about situation, you are working as SQL Server developer for Financial firm and they have the database name TechBrothersIT and they would like to rename to Techbrothers. You need to provide the scripts that can be run in QA, UAT and Production environment.

Solution:

Renaming by GUI:
Renaming database is very easy, you can simply Right Click on Database and then choose Rename.

You will see that the name of database will prompt you for change. Go ahead and change it and hit Enter.

How to Rename Database by using TSQL:
You can use TSQL To rename database in SQL Server.

1) By using Sp_Rename System Stored Procedure
We can use sp_rename system Stored Procedure to rename database in SQL server. Let's say we would like to rename TechBrtohersIT to TechBrother. We can use below script.

sp_renamedb 'OldDatabaseName','NewDatabaseName'

EXEC sp_renamedb 'TechBrothersIT','TechBrothers'

2) Use Alter Database with Modify
We can use below script to rename. In this example, I am renaming TechBrothersIT To TechBrothers.

USE master;
GO
ALTER DATABASE TechBrothersIT
Modify Name = TechBrothers ;
GO

Common Error:
Let's say that the database is in use by different applications. you might get below error.

Msg 5030, Level 16, State 2, Line 4
The database could not be exclusively locked to perform the operation.

In this case, you can kill all connections before you run rename script. Below script can be used to kill all connections on a database.Make sure with your team and other teams before you kill all connections on database for renaming.

Check out our website for Different SQL Server, MSBI tutorials and interview questions
such as SQL Server Reporting Services(SSRS) Tutorial
SQL Server Integration Services(SSIS) Tutorial
SQL Server DBA Tutorial
SQL Server / TSQL Tutorial ( Beginner to Advance)