SQL Server: Setup Adventure Works Datawarehouse Sample Database

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…

SampleDB_0001

SampleDB_0002

  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

SampleDB_0003

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

SampleDB_0004

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

On Recovery State, select RESTORE WITH RECOVERY

SampleDB_0005

  1. Click OK to start restore

SampleDB_0006

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

YLNApp_201402_0001

  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

YLNApp_201402_0002

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

YLNApp_201402_0005

  1. Refresh Databases, New Database appears in Restoring State

YLNApp_201402_0003C

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

YLNApp_201402_0003b

Step 2A: Restore OLAP Sample Database (GUI)

  1. Connect to SQL Server Analysis Services

  2. On Databases, right click and select Restore…

SampleDB_0007

 SampleDB_0008

  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

SampleDB_0009

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

SampleDB_0010 SampleDB_0011

  1. Click Refresh button to refresh databases list

SampleDB_0012

 

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

YLNApp_201402_0006

  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

YLNApp_201402_0007

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

YLNApp_201402_0008

 

Step 3: Test OLAP restore successful

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

 SampleDB_0013

  1. Click OK to start processing.

SampleDB_0014

  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

SampleDB_0015

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

SampleDB_0016

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

SampleDB_0017

 

Leave a Reply

Your email address will not be published. Required fields are marked *

18 − 18 =

YLNotes: Yunlong Notes © 2017 Frontier Theme