Renaming a SQL Server Database is not tricky, you can do it in a couple of ways, but before actually renaming the database, there are a couple of things which you should know.
In this article, we’ll guide you on the way to rename a SQL Server Database the proper way without messing with anything.
Also Read >>>> How To Resolve Login Failure SQL Server - Error Code 18456
Prerequisites
Things to think about before renaming the SQL Server Database are Limitations & Restrictions and Security Permissions related to the task.
For renaming a SQL Server Database, you'll either use SQL Server Management Studio or Transact-SQL, and we’ve covered both methods below.
Limitations & Restrictions
Below are the limitations & restrictions associated with the task of renaming a SQL Server database:
● If you’re renaming a database in Azure SQL Database, it is important to confirm that no users are connected to that database.
● Renaming a system database is not permitted.
● Database renaming isn't allowed when users are accessing the SQL Server database.
For closing any open connection to the database, you'll set the database in single-user mode.
Security Permissions
For performing specific tasks on the SQL Server database, the user should be assigned with certain permissions. To rename, the SQL Server database name ALTER permission is necessary.
Set Database to Single – User Mode
For closing any open connections and to stop other users from connecting to the database while the process of renaming is going on, you can set the database to single-user mode.
Make sure of the prerequisites mentioned below before proceeding to set the database to single-user mode:
Confirm that AUTO_UPDATE_STATISTICS_ASYNC option is set OFF. If the choice is about to ON, you can’t access the database in single-user mode (as the background thread wont to update statistics establishes a reference to the database).
Using SQL Server Management Studio
Follow the steps given below to line the database in Single-User mode using Server Management Studio:
● Open Object Explorer and connect and expand an instance of the SQL Server Database Engine.
● Select the database, right-click, and click on Properties.
● In Database Properties, click on the Options page.
● Select Single, from the Restrict Access option.
● If other users are connected with the database, Open Connections message will appear.
For changing the property, close all other connections and click Yes.
Done, you’ve set the database to single-user mode using SQL Server Management Studio successfully.
Using Transact-SQL
Execute the below steps to line the database in single-user mode using Transact-SQL:
● Establish a connection with the Database Engine.
● Select New Query on the standard bar.
● Type the code in given below syntax in the query window and click Execute.
USE master;
GO
ALTER DATABASE
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE
SET READ_ONLY;
GO
ALTER DATABASE
SET MULTI_USER;
GO
You’ve successfully set the database in single-user mode using Transact-SQL.
Rename SQL Server Database
→ Using SQL Server Management Studio
Now that you’ve understood the limitations & restrictions and security permissions and have set the database in single-user mode to disconnect other users connected to the database, you can now proceed to the renaming stage.
Steps given below can help you to rename a SQL Server or Azure SQL Server using SQL Server Management Studio:
● Open Object Explorer and establish a connection with a SQL instance.
● Again, confirm that no other users are connected to the database, else use the above method to line the SQL Server Database in Single-User mode.
● In Object Explorer, expand Databases.
● Select the database you wish to rename, right-click on it, and click Rename.
● Assign the database with a new name and click Ok.
You’ve renamed the SQL Server database successfully using SQL Server Management Studio.
→ Using Transact-SQL
This method not only will help you in renaming the SQL Server Database using T-SQL, but it will also assist you to set the database in single-user mode, and after renaming, place the database back in multi-user mode.
Follow the below steps:
● For your instance, establish a reference to the master database.
● Launch a query window.
● Type the code (in the given syntax) in the query window and click Execute.
USE master;
GO
ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE MODIFY NAME = ;
GO
ALTER DATABASE SET MULTI_USER
GO
Database renaming has processed successfully, and therefore the process included setting the database in single-user mode and upon database renaming, reverting it to multi-user mode using Transact-SQL.
Sysinfo SQL Database Recovery
There may be times when the SQL Database file(s) – MDF/NDF, become corrupt or inaccessible and all your crucial data would be at high risk of data loss, that data could be of your customers, and gathering all the information again would waste lots of hours. This won’t be good for business. Such situations are very critical, as finding the right solution becomes necessary.
Sysinfo SQL Database Recovery is the best find you can have on the Internet; its repair and recovery features outperform other recovery software. Some examples are recover large size database file(s) – both MDF/NDF, create the backup script of restored databases, recover multiple database files at once, etc.
SQL Database Recovery tool offers support for different versions of Microsoft SQL Server – 2016, 2014, 2012, 2008, etc. It doesn’t matter which version of SQL Server you’re using on your system, download the tool from the website, install it, and jump on using the software.
Conclusion
Renaming the SQL Server Database requires proper acknowledgment of all the measures associated with it; so, without learning the process specifics, it is possible that you may mess up. And in case if something goes wrong, which we hope not to, then, Sysinfo SQL Database Recovery tool is your one-stop solution.
Frequently Asked Questions (FAQs)
- Can you rename a SQL Server?
- Can we rename database in MySQL?
- Can we rename SQL Server instance name?
- How do you rename a server?
- How do I edit a MySQL database?
- How do you edit a database?
- What is Rename command in SQL?
Other Articles:
No comments:
Post a Comment