How to: Remove Database Mirroring (Transact-SQL)

At any time, the database owner can manually stop a database mirroring session by removing mirroring from the database.

Important

Before mirroring can be restarted, any log backups taken on the principal database after mirroring was removed must all be applied to the mirror database.

To remove database mirroring

  1. Connect to either partner.

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

    This statement removes the database mirroring session and removes mirroring from the database. You can specify OFF on either partner. For more information about the effect of removing database mirroring, see Removing Database Mirroring.

  3. Optionally, you can recover the former mirror database. On the server instance that was the mirror server, enter:

    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.

See Also

Other Resources

ALTER DATABASE (Transact-SQL)
Database Mirroring
Database Mirroring How-to Topics

Help and Information

Getting SQL Server 2005 Assistance