SQL Server - Database in Use When Renaming a Database in SQL Server

Database in Use When Renaming a Database in SQL Server

Q

Why I am getting this error when renaming a database in SQL Server?

✍: FYIcenter.com

A

If you are trying to rename a database that is in use, you will get an error message like this: "The database could not be exclusively locked to perform the operation."

Before renaming a database, you must stop all client sessions using this database. Otherwise, you will get an error as shown in this tutorial example:

1. Launch one instance of SQL Server Management Studio and run:

USE FyiCenterComData
GO

2. Keep the first instance running and launch another instance of SQL Server Management Studio:

ALTER DATABASE FyiCenterComData 
   MODIFY NAME = FyiCenterData
GO
Msg 5030, Level 16, State 2, Server LOCALHOST\SQLEXPRESS
The database could not be exclusively locked to perform 
   the operation.

Obviously, the first instance is blocking the "ALTER DATABASE" statement.

⇒Managing Databases and Physical Files in SQL Server

⇒⇒SQL Server Database Tutorials

2016-11-20, 644👍, 0💬

ONLINE/OFFLINE - Database States in SQL Server
What are database states in SQL Server? A database is always in one specific state. For example, these states include ONLINE, OFFLINE, or SUSPECT. To verify the current state of a database, select the state_desc column in the sys.databases catalog view. The following table defines the database state... 2016-11-20, 611👍, 0💬

Creating Databases with Specified Physical Files in SQL Server
How to create database with physical files specified in SQL Server? If you don't like the default behavior of the CREATE DATABASE statement, you can specify the physical database files with a longer statement: CREATE DATABASE database_name ON (NAME = logical_data_name, FILENAME = physical_data_name,... 2016-11-24, 609👍, 0💬

OFFLINE - Taking a database offline in SQL Server
How to set a database state to OFFLINE in SQL Server? If you want to move database physical files, you should take the database offline by using the "ALTER DATABASE" statement with the following syntax: ALTER DATABASE database_name SET OFFLINE The following tutorial example will bring "FyiCenterComD... 2016-11-20, 574👍, 0💬

Renaming Database Names in SQL Server
How to rename databases in SQL Server? If don't like the name of a database, you can change it by using the "ALTER DATABASE" statement with the following syntax: ALTER DATABASE database_name MODIFY NAME = new_database_name The tutorial example below shows you how change the database name from "FyiCe... 2016-11-24, 570👍, 0💬

All rights in the contents of this web site are reserved by the individual author. fyicenter.com does not guarantee the truthfulness, accuracy, or reliability of any contents.