Events
Mar 31, 11 PM - Apr 2, 11 PM
The ultimate SQL, Power BI, Fabric, and AI community-led event. March 31 - April 2. Use code MSCUST for a $150 discount.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Applies to:
SQL Server
This topic describes how to remove database mirroring from a database in SQL Server by using SQL Server Management Studio or Transact-SQL. At any time, the database owner can manually stop a database mirroring session by removing mirroring from the database.
In This Topic
Before you begin:
To remove database mirroring, using:
Follow Up: After Removing Database Mirroring
Requires ALTER permission on the database.
During a database mirroring session, connect to the principal server instance, in Object Explorer, click the server name to expand the server tree.
Expand Databases, and select the database.
Right-click the database, select Tasks, and then click Mirror. This opens the Mirroring page of the Database Properties dialog box.
In the Select a Page pane, click Mirroring.
To remove mirroring, click Remove Mirroring. A prompt asks for confirmation. If you click Yes, the session is stopped and mirroring is removed from the database.
To remove database mirroring, use the Database Properties. use the Mirroring page of the Database Properties dialog box.
Connect to the Database Engine of either mirroring partner.
From the Standard bar, click New Query.
Issue the following Transact-SQL statement:
ALTER DATABASE database_name SET PARTNER OFF
where database_name is the mirrored database whose session you want to remove.
The following example removes database mirroring from the AdventureWorks2022
sample database.
ALTER DATABASE AdventureWorks2022 SET PARTNER OFF;
Note
For information about the impact of removing mirroring, see Removing Database Mirroring (SQL Server).
If you intend to restart mirroring on the database
Any log backups taken on the principal database after mirroring was removed must all be applied to the mirror database before you can restart mirroring.
If you do not intend to restart mirroring
Optionally, you can recover the former mirror database. On the server instance that was the mirror server, you can use the following Transact-SQL statement:
RESTORE DATABASE database_name WITH RECOVERY;
Important
If you recover this database, two divergent databases with the same name are online. Therefore, you need to ensure that clients can access only one of them-typically the most recent principal database.
Remove the Witness from a Database Mirroring Session (SQL Server)
Establish a Database Mirroring Session Using Windows Authentication (SQL Server Management Studio)
Establish a Database Mirroring Session Using Windows Authentication (Transact-SQL)
Example: Setting Up Database Mirroring Using Certificates (Transact-SQL)
Database Mirroring (SQL Server)
Setting Up Database Mirroring (SQL Server)
Always On Availability Groups (SQL Server)
Events
Mar 31, 11 PM - Apr 2, 11 PM
The ultimate SQL, Power BI, Fabric, and AI community-led event. March 31 - April 2. Use code MSCUST for a $150 discount.
Register todayTraining
Module
Get started with SQL Database in Microsoft Fabric - Training
Learn how SQL Database in Microsoft Fabric works, the key concepts, and practical examples to help users SQL Database effectively as part of their analytics solutions.
Certification
Microsoft Certified: Azure Database Administrator Associate - Certifications
Administer an SQL Server database infrastructure for cloud, on-premises and hybrid relational databases using the Microsoft PaaS relational database offerings.