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:
- Restore OLTP Database
- Restore OLAP Database
- 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
- SQL Server Analysis Datawarehouse:
OLTP AdventureWorksDW_SS2008R2.Full.bak OKAP AdventureWorksDW_SS2008R2.Full.abf
Step 1A: Restore OLTP Sample Database (GUI)
- Connect to SQL Server Database Engine
-
On Databases, right click and select Restore Database…
- On Source for restore, select From device and click on … button
-
on Specify Backup dialog, click on Add and select backup file and click OK
- On Destination for restore, select database to restore on To database dropdown list
- On Options page, select Overwrite the existing database (WITH REPLACE)
On Recovery State, select RESTORE WITH RECOVERY
- Click OK to start restore
Step 1B: Restore OLTP Sample Database (T-SQL)
- SQL Server Restore using GUI mode allows restore backup file resides in the server.
-
To restore with backup file on remote share, restore can only be done via T-SQL
-
Open Web Browser and navigate to SQL Server Restore Command Generator Tool
YLNApp: SQL Server Restore Command Generator (201402)
- Enter Database Name, Backup Vaults, Backup Sources and click Generate button
- Click on Copy to Clipboard to copy to clipboard (IE only) or copy all text on Restore Command
-
Paste in SQL Server Management Studio and execute
If the database to be restore exist, the script will generate database physical mapping on execution
- Refresh Databases, New Database appears in Restoring State
- Scroll down to the last script to invoke database recovery to Online
Step 2A: Restore OLAP Sample Database (GUI)
- Connect to SQL Server Analysis Services
-
On Databases, right click and select Restore…
- On Restore Source, click on Browse… button
-
Select database files and if the backup is not in default path, enter the path and backup filename
- On Options, check Allow database overwrite and Overwrite security information: Copy All
- Click Refresh button to refresh databases list
Step 2B: Restore OLAP Sample Database (T-SQL)
- Open Web Browser and navigate to SQL Server Restore Command Generator Tool
YLNApp: SQL Server Analysis Restore Command Generator (201403)
- Enter Database Name, Backup Vaults, Backup Sources and click Generate button
- Click on Copy to Clipboard to copy to clipboard (IE only) or copy all text on Restore Command
-
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)
- Refresh Databases, New Database appears in Restoring State
Step 3: Test OLAP restore successful
- Connect to SQL Server Analysis Services and right click on OLAP Database and select Process
- Click OK to start processing.
- 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.