A.3 Restoring Repositories on the New SQL Server

To restore repositories on the new computer:

1. On the new computer, stop the SQL Server Agent service.

2. To ensure that the repository you are restoring is not the default database for the account you are using to perform the restore, complete the following steps:

   1. In Microsoft SQL Server Management Studio, expand SQL_Server_name\Security\Logins.

   2. Right-click the account you are using and select Properties.

   3. On the General page, note the selection in the Default database list.

3. (Conditional) If the default database for the account you are using is the repository you are restoring, either change the default database for the account, or log in to SQL Server Management Studio with a different account.

4. To ensure that no users are connected to the new repository, complete the following steps:

   1. Expand SQL_Server_name\Databases.

   2. Right-click the repository and select New Query.

   3. In the query window, type the following command, and then click Execute:

      USE master
      GO
      Exec sp_who2
      GO

   4. In the results table, check the DBName column for the repository name. The column should not contain entries for the repository.

5. (Conditional) If the DBName column contains entries for the repository, to close the open connections, complete the following steps for each connection:

   1. In the SPID column for the row in which the repository name appears, note the SPID number.

   2. Right-click the repository and select New Query.

   3. In the query window, type the following command, and then click Execute:

      kill SPID_number

6. Repeat Step 4 and Step 5 until the DBName column does not contain entries for the repository.

7. Right-click the repository and select Tasks > Restore > Database.

8. On the General page, complete the following steps:

   1. Select the From device radio button and click the button to specify the backup device.

   2. On the Specify Backup window, select File from the Backup media list, and then click Add.

   3. On the Locate Backup File window, browse to the location where you saved the backup copy, select the backup file, and then click OK.

   4. Click OK to return to the General page.

   5. Select the backup set to restore.

9. On the Options page, complete the following steps:

   1. Under Restore options, select the Overwrite the existing database (WITH REPLACE) check box.

   2. Under Recovery state, select the RESTORE WITH RECOVERY radio button.

   3. Click OK to restore the repository.

10. After the restore completes, restart the SQL Server Agent service.