This guide is written to show how to restore SQL Server Analysis sample database “Adventure Works DW” following my convention of SQL Server Backup File Convention.

The guide divides into three steps:

1. Restore OLTP Database
2. Restore OLAP Database
3. Test OLAP Restore

SQL Server Backup File Extension

• SQL Server OLTP Backup File

Data Full        .Full.bak
Data DIff        .Diff.bak
Transaction Log  .trn

• SQL Server Analysis Backup File

Data Full        .Full.abf

 

SQL Server Sample Database Download

1. SQL Server Analysis Datawarehouse:

– AdventureWorksDW_SS2008R2

OLTP   AdventureWorksDW_SS2008R2.Full.bak
OKAP   AdventureWorksDW_SS2008R2.Full.abf

 

Step 1A: Restore OLTP Sample Database (GUI)

1. Connect to SQL Server Database Engine

2. On Databases, right click and select Restore Database…

1. On Source for restore, select From device and click on … button

2. on Specify Backup dialog, click on Add and select backup file and click OK

1. On Destination for restore, select database to restore on To database dropdown list

1. On Options page, select Overwrite the existing database (WITH REPLACE)

On Recovery State, select RESTORE WITH RECOVERY

1. Click OK to start restore

Step 1B: Restore OLTP Sample Database (T-SQL)

1. SQL Server Restore using GUI mode allows restore backup file resides in the server.

2. To restore with backup file on remote share, restore can only be done via T-SQL

3. Open Web Browser and navigate to SQL Server Restore Command Generator Tool

YLNApp: SQL Server Restore Command Generator (201402)

1. Enter Database Name, Backup Vaults, Backup Sources and click Generate button

1. Click on Copy to Clipboard to copy to clipboard (IE only) or copy all text on Restore Command

2. Paste in SQL Server Management Studio and execute

If the database to be restore exist, the script will generate database physical mapping on execution

1. Refresh Databases, New Database appears in Restoring State

1. Scroll down to the last script to invoke database recovery to Online

Step 2A: Restore OLAP Sample Database (GUI)

1. Connect to SQL Server Analysis Services

2. On Databases, right click and select Restore…

 

1. On Restore Source, click on Browse… button

2. Select database files and if the backup is not in default path, enter the path and backup filename

1. On Options, check Allow database overwrite and Overwrite security information: Copy All

1. Click Refresh button to refresh databases list

 

Step 2B: Restore OLAP Sample Database (T-SQL)

1. Open Web Browser and navigate to SQL Server Restore Command Generator Tool

YLNApp: SQL Server Analysis Restore Command Generator (201403)

1. Enter Database Name, Backup Vaults, Backup Sources and click Generate button

1. Click on Copy to Clipboard to copy to clipboard (IE only) or copy all text on Restore Command

2. Connect Analysis Services and Open New Query. Paste and Execute

Note: The security is set to restore from backup. If you do not wish to restore the security setting, set <security> element to SkipMembership or IgnoreSecurity.

For detail, refer to TECHNET: Backing Up, Restoring, and Synchronizing Databases (XMLA)

1. Refresh Databases, New Database appears in Restoring State

 

Step 3: Test OLAP restore successful

1. Connect to SQL Server Analysis Services and right click on OLAP Database and select Process

 

1. Click OK to start processing.

1. If there is a failure in processing, please ensure the account used to connect to OLTP database

In this tutorial, it runs on Service Account NT AUTHORITY\Network Service

The solution for the above issue is to map the account to database with db_owner role

Re-run the process again, SSAS will process correctly.