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.
http://www.techbrothersit.com/2016/02...
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)
http://www.techbrothersit.com/
Auf dieser Seite können Sie das Online-Video How to Rename Database in SQL Server - SQL Server / T-SQL Tutorial Part 26 mit der Dauer stunde minuten sekunde in guter Qualität ansehen, das der Benutzer TechBrothersIT 16 Februar 2016 hochgeladen hat, den Link mit Freunden und Bekannten teilen, dieses Video wurde auf Youtube bereits 12,604 Mal angesehen und es wurde von 50 den Zuschauern gefallen. Viel Spaß beim Betrachtenden Zuschauern gefallen!
