How to: Set Up a Mirror Database to Use the Trustworthy Property

Updated: 5 December 2005

When a database is backed up, the TRUSTWORTHY database property is set to OFF. Therefore, on a new mirror database TRUSTWORTHY is always OFF. If the database needs to be trustworthy after a failover, extra setup steps are necessary after mirroring begins.

For information about this database property, see TRUSTWORTHY Database Property.

  1. On the principal server instance, verify that the principal database has the Trustworthy property turned on.

    SELECT name, database_id, is_trustworthy_on FROM sys.databases 

    For more information, see sys.databases (Transact-SQL).

  2. After starting mirroring, verify that the database is currently the principal database, the session is using a synchronous operating mode, and the session is already synchronized.

    SELECT database_id, mirroring_role, mirroring_safety_level_desc, mirroring_state_desc FROM sys.database_mirroring

    For more information, see sys.database_mirroring (Transact-SQL).

  3. Once the mirroring session is synchronized, manually fail over to the mirror database.

    This can be done in either SQL Server Management Studio or using Transact-SQL:

  4. Turn on the trustworthy database property using the following ALTER DATABASE command:


    For more information, seeALTER DATABASE (Transact-SQL).

  5. Optionally, manually failover again to return to the original principal.

  6. Optionally, switch to asynchronous, high-performance mode by setting SAFETY to OFF and ensuring that WITNESS is also set to OFF.

    In Transact-SQL:

    In SQL Server Management Studio:

Community Additions