Methods To Rename SQL Server Database
Sometimes it happens that we need to rename SQL Server database because the database might have been restored using different names or the original name of the database is based on the some project that is no longer relevant to the database. Whatever the reason behind the scenes, this can be easily done.
In this article we look how to rename SQL Server database using different options.
Before Renaming SQL Server Database
- ALTER permission is required on the database to rename
- File group name (.ndf .mdf) are not changed, when renamed the database
- You cannot rename the System database
Make sure that no one is using database if someone is using the database, then close all the existing connections and set the database to SINGLE USER mode.
To change the restrict access to SINGLE USER connect to SQL Server Instance. Right click on the database and from the drop down menu select Properties
From the Database Properties window, under Select a page pane, click on Option and select Restrict Action option as SINGLE_USER then click Ok
From Open Connections dialog box, click on Yes
Rename SQL Server Database Using SQL Server Management Studio
If you are running SQL Server Management Studio, then right click on the database which you want to rename and from the drop down menu select Rename
Enter the new database name (desired database name) and to see the changes refresh the database.
Rename SQL Server Database Using Transact-SQL
Use the below Transact-SQL script to rename SQL Server Database
Rename SQL Server Database Using Detach And Attach
You can also use detach and attach feature to rename SQL Server Database. For this first detach the SQL Server database and give the new database name while attaching the database. This can be done via SQL Server Management Studio and Transact-SQL:
Use the following T-SQL command to rename SQL Server database
Using SQL Server Management Studio
Detaching SQL Server Database using SQL Server Management Studio
Attaching and renaming SQL Server database using SQL Server Management Studio
Note: The techniques mention above allow you to rename the database but the physical file still have the same name.
Rename Physical Files
When SQL Server is detached then SQL Server releases the lock on the physical files and you can rename the physical files.
This article describes how to rename SQL Server database and physical file using SQL Server Management Studio, Transact-SQL and via database detach and attach method.