Moving the Microsoft SQL Server Database to Another Machine

This article only applies if your MS SQL Server database is only for Secret Server.

Follow the steps below for moving MS SQL Server database for Secret Server.

Task 1: Backing up and Restoring the Database

To back up your Secret Server installation:

  1. Enable the maintenance mode.

  2. Stop the Secret Server site in Internet Information Server (IIS) to prevent any changes to the database.

  3. Navigate to the directory where Secret Server is installed.

  4. Copy the folder (holding the application) to your back up location.

  5. Open your SQL Server Management Studio.

  6. Right click the database your Secret Server is running on, and select Tasks > Backup.

  7. Click the Add button. You are prompted to enter a file path for the .bak file. This can be the final destination (not recommended) or a temporary one (for later moving to a back up location).

  8. Make sure SQL Server has permissions for this location. That is, create (if needed) and or grant access to the account that will access the database (see the Installation for account creation instructions). See Running the IIS Application Pool As a Service Account (Task 2) for details.

  9. Copy the resulting database backup file (.bak) to your backup location.

You can also automate steps 2-4 using the command: osql -S myserver\SQLEXPRESS -E - Q "BACKUP DATABASE SECRETSERVER TO DISK = 'c:\backup\ss.bak'.
We recommend taking the old database offline after all steps are complete.

Task 2: Connecting Secret Server to the New Database

  1. Restart your Secret Server website in IIS.

  2. Log on Secret Server as a local admin.

  3. Navigate to https://<your_SS_URL>/Setup/Database. The Database Configuration page appears:

    image-20200810114614427

    The setting here are stored in C:\inetpub\wwwroot\Secretserver\database.config. You can back that file up to revert or simply return to this page to reset the connection again. See the Privilege Manager documentation if you need to change its configuration too.

  4. Click the Edit button. The page becomes editable.

  5. Type your new SQL Server location (server name) and database.

  6. Click the Save Database Connection Settings button, and the site will connect to the new database.

  7. Your site is now pointing the new database.

To roll back changes and restore the original database, complete both tasks again to move the database back to the original database server.
If you are also moving the Secret Server application to another server, see Moving Secret Server to Another Machine for more information.

The steps in Task 2 should be performed for each web node in a deployment. Making the change on one node only will not propagate to the other nodes.