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.
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
Connect to either partner.
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 impact of removing database mirroring, see Removing Database Mirroring.
Optionally, you can recover the former mirror database. On the server instance that was the mirror server, enter:
RESTORE DATABASE <database_name> WITH RECOVERY;
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.