Backup Basics for SQL Server Analysis Services

Backup Basics for SQL Server Analysis Services $(document).ready(function () { $(".rmRootLink:contains('subscribe')").attr('href', '/Subscribe/tabid/444/List/1/CategoryID/99/Level/a/Default.aspx?code=WP211XHB '); }); Windows IT Pro For Admins SuperSite For Tech Enthusiasts SQL Server Mag For DBAs DevProConnections For Developers SharePointPro Connections For IT & Dev SQL Server Magazine

The Smart Guide to Building World-Class Applications

About Advertise Subscribe Follow Us RSS Why Join My Account Register Sign in Search InstantDoc IDGO Browse By: Author | Issue

Homenewsblogsbusiness intelligencedatabase administrationdatabase developmentPuzzled by T-SQLSQL SelectSQL Server BISQL Server Questions AnsweredTool TimeT-SQL and PowerShell Scriptingproducts & reviewsawards centralbuyer's guidereviewsnewslettersEventsRegister for DevConnections Conferences & WorkshopseLearning SeminarsRoadshowsVirtual EventsWeb Seminarscommunityfollow usForumsuser group centralresourcesebooksessential guidespodcaststip guidestrials & downloadsvideos & moreweb seminarswhite papersmoreHP MicrosoftConverged ApplicationsConverged Applications BlogMicrosoft UKsubscribe var dartrnum=Math.floor(Math.random() * 100000);document.write(' document.write(' Home » Backup & Recovery » Backup Basics for SQL Server Analysis Services

Browse By Category Business IntelligenceDatabase AdministrationKevin Kline: Thoughts from a SQL Server Old TimerDatabase DevelopmentT-SQL & PowerShell Scripting Doublelick Ads document.write(' March 17, 2011 07:29 AMBackup Basics for SQL Server Analysis Services Make sure that your backup strategy includes SSAS databases bookmarkreprintsEmailPrintCommentsRating: (0)Tyler ChessmanSQL Server MagazineInstantDoc ID #129420

If you ask DBAs about the backup strategy for their relational databases, you’ll likely get fairly long answers. If you ask the same question about SQL Server Analysis Services (SSAS) databases, you might get blank stares. Traditionally, SSAS hasn’t had the same set of retention and disaster recovery requirements as its relational counterpart. But as the adoption and mission-critical nature of SSAS databases continue to increase, DBAs have to consider their backup options for this data, too.

SSAS backups are fairly straightforward if you know about the structure of SSAS databases and the backup basics, including scheduling options and performance considerations. Unless specifically noted, the concepts and features discussed here apply to SQL Server 2008 R2, SQL Server 2008, and SQL Server 2005 across all editions that include SSAS (Standard, Enterprise, and Developer, plus Datacenter in SQL Server 2008 R2).

 

SSAS Database Structure

Like its relational counterpart, an SSAS backup operation creates a backup file for each database. An SSAS database is made up of several components:

Data sources and associated data source viewsCubesShared dimensionsData-mining structuresRolesAssembly references

Conceptually, an SSAS backup file contains all these components. In practice, however, the storage mode used by the database objects affects what’s in the database and, therefore, the contents of the backup file. Specifically, the storage mode of the dimensions and measure group partitions determine what’s in the SSAS database. Multidimensional OLAP (MOLAP) partitions and dimensions create a copy of the source system data and store it along with any aggregations in the SSAS database. In Relational OLAP (ROLAP) storage, the source data and aggregations are stored in the source system. Hybrid OLAP (HOLAP) storage keeps the source data in the source system but stores aggregations in the SSAS database. To determine the storage mode that’s being used, you can look at the storage mode property for each measure group partition and dimension.

During a backup, SSAS doesn’t query the source system for additional data. So, if you back up a database containing a cube that has ROLAP partitions, the SSAS backup doesn’t back up any of the fact data or aggregations. Therefore, the source system must be backed up separately when you’re using ROLAP or HOLAP storage. Table 1 summarizes the storage modes and resulting contents of an SSAS backup file.

Table 1: SSAS Backup Storage Modes

Besides the source system, you might need to back up other SSAS components separately:

If you’re using the write-back feature in SSAS 2005, you must make sure that the relational write-back tables are backed up separately. Write-back data in SSAS 2008 uses MOLAP storage by default, but it can also use relational storage.If you’re using the query log (which is configured as a relational database table) to collect statistical information for your SSAS database, the query log must be backed up separately. You can determine the name and location of the relational table and whether the query log is being used by looking at the Log\QueryLog properties for the SSAS instance.Backup Basics

SSAS backups can be performed manually through SQL Server Management Studio (SSMS). After you connect to an SSAS instance, right-click a database and click Back Up to open the Backup Database window, which is shown in Figure 1. An SSAS backup file has a default filename extension of .abf, and it’s stored in the Microsoft SQL Server\instance_name\OLAP\Backup folder.

Figure 1: Backup Database window in SSMS

Unlike a relational database backup file, an SSAS backup file can contain only one backup. In other words, you can’t create backup sets. If you want to retain multiple backups, you must create multiple files. Also, SSAS doesn’t provide options for differential backups or log file backups.

By default, two check boxes are selected in the Backup Database window: Apply compression and Encrypt backup file. When the Apply compression option is selected, SSAS compresses the backup file, which saves disk space at the expense of higher CPU utilization during the backup operation. When the Encrypt backup file option is selected, SSAS accepts a user-supplied password, which it uses to encrypt the backup file. Note that, unlike the password for a SQL Server relational database backup set, this password prevents the reading of the backup data by other means. If you choose to encrypt SSAS backups, make sure that you keep a record of the password in a safe and separate location. For strong password considerations, see the “Back Up Password Protection” section of the SQL Server Books Online (BOL) topic “Backing Up and Restoring an Analysis Services Database”.

FirstPrevious123NextLast Add a Comment

Hi Tyler,

Nice article! Cannot believe it was posted on the same day as I am looking for the ways to backup / restore OLAP SQL Server 2008 DB.

Do you by chance know how to move the OLAP cube to another server and specifically how to do that for TFS (Team Foundation Server) ?

Thanks in advance,
Yelena Varshal

Yelena Varshal3/17/2011 2:36:28 PM
You must log on before posting a comment.

Are you a new visitor? Register Here document.write('Related ResourcesA Jump Start to SQL Server BI
EBook by MicrosoftThe Essential Guide to SQL Server 2005 BI
Essential Guide by Dell4 Killer Features That Will Change SQL Server Backup As You Know It
Tip Guide by IderaMoving SQL Server to the x64 Platform
EBook by AMDMore Copy Paste fails with large result sets.

In SQL 2008 Management Studio. The copy fails on with large result set. I can select and copy small portions and paste in excel, notepad, word ect.......222-96701

More Posts Monitoring encryption keys created by TDE using SQL Audit Query Correlation to Resource Usage - % of X Upgrading from 2008 to 2008 R2 with SSIS document.write(' document.write(' GOOGLE LINKS SPONSORED LINKSdocument.write(' FEATURED LINKSdocument.write(' Downloads

Do you have to create custom code just to determine who has what permissions? Try Security Explorer, an enterprise security management solution for SQL, SharePoint, and more. As SQL expert Robert Davis reports, “DBAs who deal with compliance regulations will find this tool indispensible.”

Tip Guides

Migrating to Windows 7 comes with one major drawback: Win7 “application obsolescence.” This tip guide explains how VMware ThinApp can encapsulate applications in self-contained executables and how you can avoid the ten most common Win7 application migration pitfalls.

Web Seminars

Despite the promise of cloud computing, IT administrators may be hesitant to entrust the performance of their business-critical processes to a 3rd party. View this webinar to learn how you can reap the benefits of cloud computing while maintaining absolute control over your data.

eLearning Series

Windows IT Pro brings the experts direct to you to share their real-world perspective, experience, and expertise. During each event, three sessions stream in real time, allowing you to learn, to ask questions, and to get solutions.
Upcoming event: SQL Server Consolidation

SQL Server Magazine

DevProConnections ITTV Left-Brain SharePointPro Connections SQL Server Magazine SuperSite for Windows Windows IT Pro © 2011 Penton Media, Inc.

Home About Us Advertise Customer Service Privacy Statement Subscribe/Renew Terms Of Use

Windows is a trademark of the Microsoft group of companies. Windows IT Pro is used by Penton Media Inc. under license from owner.

function AttachAds() { var midpos = $("#wrapper").offset(); var midwidth = $("#wrapper").width(); var leftwidth = $("#leftad").width(); $("#leftad").css({ "left": (midpos.left - leftwidth) + "px", "top": midpos.top + "px" }); $("#rightad").css({ "left": (midpos.left + midwidth) + "px", "top": midpos.top + "px" }); } $(window).resize(AttachAds); AttachAds(); Sponsored Introduction Continue on (or wait seconds) ×

document.write('

View the original article here