Reattaching a restored backup of a database

If you need to query sessions from an older database that is offline and detached, you can restore the database from a backup and reattach it to your auditing installation. You might need to do this is your auditing installation is large, you do frequent database rotation, and you don’t keep many databases attached and online.

Understand that after you restore a database from a backup, you need to fix a couple of settings in the database before you can reattach it to your auditing installation. During the backup operation, the database owner and trustworthy properties get set in such a way that prevents you from reattaching the database to your auditing installation unless you fix these properties.

To reattach a restored database to your auditing installation:

  1. Run the following command to reset the database owner to [sa]:
    ALTER AUTHORIZATION ON DATABASE::<db_name> TO [sa]
  2. Run the following command to reset the trustworthy property:

    ALTER DATABASE <db_name> SET TRUSTWORTHY ON

    You can now reattach the database to your auditing installation.