5.5 Restoring Databases

This section describes how to restore the backed up databases.

IMPORTANT:The newly installed SQL Server must have the same collation/sort order as defined in the source databases that you want to restore. For more information about the collation order, see Section 5.2, Preparing for Migration.

To restore the backed up databases:

  1. Start SQL Server Management Studio and connect to the SQL Server.

  2. Expand the SQL Server instance where the AC_Warehouse and AC_Configuration databases are located.

  3. Right-click the database you want to restore and select Tasks > Restore > Database.

  4. In the Destination for restore group box, enter the database name in the To Database field.

  5. In the Source for restore group box, select From device and click [...].

  6. In the Specify Backup dialog box:

    • Select File in the Backup Media list.

    • Click Add to browse for the database backup file.

    • Click OK.

  7. Select Restore in the Select the backup sets to restore group box.

  8. Click Options in Select a page.

  9. Select Overwrite the existing database in the Restore options group box and retain the default options in the Recovery state group box to restore the following databases:

    • AC_Configuration database of the older version of Analysis Center to Analysis Center 2.9 AC_Configuration database.

    • AC_Warehouse database of the older version of Analysis Center to Analysis Center 2.9 AC_Warehouse database.

    Click OK.

  10. Repeat Step 2 through Step 9 to restore each of the Data Mart databases.

  11. To restore Analysis Service Database (AC_OLAP) using the SQL Server Management Studio GUI:

    1. Connect to SQL Server Analysis Service Instance using the SQL Server Management Studio.

    2. In the Object Explorer, right-click the Databases node and select Restore.

    3. In the Restore Database window, click Browse next to the Backup file text box in the Restore Source group box.

    4. In the Locate Databases Files window, specify the path where the AC_OLAP database backup file is stored, and click OK to save the Restore Source information.

    5. Click Browse next to the Storage Location text box in the Restore Target group box.

      Select the path where you want to restore the Analysis Service database. Click OK to save the changes and return to the Restore Database window.

    6. Click OK to restore the Analysis Service Database.

    7. Edit the connection string of the AC_OLAP database to point it to the correct Warehouse. To do this, on the Analysis Services for the AC_OLAP database, expand the node. Go to Data Sources node and expand it. Right-click DS_ACWarehouse, click Properties, and modify the value for the connection string, as shown below:

      (Conditional) If you are using SQL 2008, specify the following values for the parameters:

      • Provider: Native OLE DB\SQL Server Native Client 10.0

      • Server Name: <nameServer\instance where AC_Warehouse DB is located>

      • DB Name: AC_Warehouse

      (Conditional) If you are using SQL 2012, specify the following values for the parameters:

      • Provider: Native OLE DB\SQL Server Native Client 11.0

      • Server Name: <nameServer\instance where AC_Warehouse DB is located>

      • DB Name: AC_Warehouse

      Click OK.