Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

Remove Database Mirroring (SQL Server)

Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

This topic describes how to remove database mirroring from a database in SQL Server 2016 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



Requires ALTER permission on the database.

Arrow icon used with Back to Top link [Top]

To remove database mirroring

  1. During a database mirroring session, connect to the principal server instance, in Object Explorer, click the server name to expand the server tree.

  2. Expand Databases, and select the database.

  3. Right-click the database, select Tasks, and then click Mirror. This opens the Mirroring page of the Database Properties dialog box.

  4. In the Select a Page pane, click Mirroring.

  5. 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.

Arrow icon used with Back to Top link [Top]

To remove database mirroring, use the Database Properties. use the Mirroring page of the Database Properties dialog box.

To remove database mirroring

  1. Connect to the Database Engine of either mirroring partner.

  2. From the Standard bar, click New Query.

  3. Issue the following Transact-SQL statement:


    where database_name is the mirrored database whose session you want to remove.

    The following example removes database mirroring from the AdventureWorks2012 sample database.


Note 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 intent 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:

    Important noteImportant

    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.

Arrow icon used with Back to Top link [Top]

Community Additions

© 2015 Microsoft