7.5 Restoring Databases

IMPORTANT:The newly installed SQL Server must have the same collation or sort order as defined in the source databases that you want to restore. For more information about the collation order, see Section 7.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, specify 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:

    1. Select File in the Backup Media list.

    2. Click Add to browse for the database backup file.

    3. 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 and click OK:

    • AC_Configuration database of the older version of Analysis Center to Analysis Center 3.0 or later AC_Configuration database.

    • AC_Warehouse database of the older version of Analysis Center to Analysis Center 3.0 or later AC_Warehouse database.

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

  11. To restore Analysis Service Database (AC_OLAP):

    1. Use the SQL Server Management Studio to connect to SQL Server Analysis Service Instance.

    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 option 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 option 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. Select Allow database overwrite.

    7. Click OK to restore the Analysis Service Database.

    8. 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 accordingly modify the value for the connection string. For example:

      • On SQL Server 2008 computer, specify the following values:

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

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

        • DB Name: AC_Warehouse

      • On SQL Server 2012 computer, specify the following values:

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

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

        • DB Name: AC_Warehouse

    9. Click OK.