There are normally 2 main approaches to backup SQL SSAS databases:
1. Backup SSAS Databases at a scheduled time
2. Backup SSAS databases whenever after a process is performed.
Although there is nothing wrong with these 2 approaches, they are both not the best. They are both not very easy to be managed, especially when there are lots of databases under same SSAS instance. A better way is to intelligently backup SSAS databases based on the data change. A job can be set up to check the data change for the SSAS databases at a scheduled interval and if an SSAS database has been detected a change either data or schema then it will be backed up; if it has nothing been changed then a new backup is not needed. In this post, I will use an SSIS package to intelligently manage the SSAS database backup. My solution is based on the logic below:
1. Only modified SSAS databases (Data or schema) will be backed up
2. A new added SSAS database will be always backed up
3. SSAS backups can be traced. The backup history will be kept.
4. backup file retention can be configured (how many backup file need to be kept?)
To start with, I need 2 SSAS maintenance tables to manage the backup solution. I use the below script for my tables: (there are some columns in this script is not specific for the backup purpose, but as they are related to the SSIS package, they will be kept.)
CREATE TABLE [dbo].[Database_Backup_Admin]
(
[Database_Name] [nvarchar](128) NOT NULL,
[Data_Modified] [datetime] NULL,
[Backup_Date] [datetime] NULL,
[DNS_Instance_Name] [nvarchar](128) NOT NULL,
[Instance_Type] [nvarchar](50) NOT NULL,
[Backup_file_to_Keep] [tinyint] NULL,
[Is_Current] [bit] NULL,
[Is_Backuped_Latest] [bit] NULL,
[CacheWarm_Date] [datetime] NULL,
[Is_CacheWarm_Current] [bit] NULL,
[Is_CacheWarm_On] [bit] NULL,
CONSTRAINT [pk_Database_BackUP_Admin] PRIMARY KEY CLUSTERED
(
[Database_Name] ASC,
[DNS_Instance_Name] ASC,
[Instance_Type] ASC
)
);
CREATE TABLE [dbo].[Database_Backup_History]
(
[Database_Name] [nvarchar](128) NOT NULL,
[Backup_Date] [datetime] NOT NULL,
[DNS_Instance_Name] [nvarchar](128) NOT NULL,
[Instance_Type] [nvarchar](50) NOT NULL,
[Backup_file_Name] [nvarchar](128) NULL,
[Backup_file_Size] [int] NULL,
[Backup_start_Time] [datetime] NULL,
[Backup_End_Time] [datetime] NULL,
[Backup_Duration_Second] [int] NULL,
CONSTRAINT [pk_Database_Backup_History] PRIMARY KEY CLUSTERED
(
[Database_Name] ASC,
[Backup_Date] ASC,
[DNS_Instance_Name] ASC,
[Instance_Type] ASC
)
);
Once the tables are created, I created an SSIS package with the layout as below:
From the package task flow, the first 2 steps are to check if all the databases in our Database_Backup_Admin table are up to date. If a database was in the server but now is removed, then the Is_Current column of the table for the database will be set to 0. If there is a new database added into the server, it will be loaded into the table. There is a slowly changing dimension data transformation used for for this check. The DBSchema_Catalogs DMV is used to check the SSAS databases in the server instance. The data flow task for the database check as below:
SSAS database data change can be verified by MDschema_cubes DMV. The last_data_update column of MDschema_cubes has a time stamp for the cube data change. The package will update the Data_Modified column for Database_Backup_Admin table based on the last_data_update column of MDschema_Cubes.
After the Data_Modified column is updated, a comparison between Data_Modified and Backup_date will be performed. If the backup_date is older than Data_Modified, then Is_Backuped_Latest will be marked as False, which suggests a new backup is needed.
The last for each loop container is used to backup SSAS database by using XMLA backup command. A logic is also used to remove the older backup files based on the configuration how many backup files to keep. The Scrip used to remove the old backup file:
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO
Imports System.Windows.Forms
<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
Enum ScriptResults
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End Enum
Public Class CompareFileLastWriteTime
Implements IComparer
Public Function Compare(ByVal x As Object, ByVal y As Object) As Integer Implements IComparer.Compare
'this "Compare" code is bollowed from: http://geekswithblogs.net/ntsmith/archive/2006/08/17/88250.aspx
Dim File1 As FileInfo
Dim File2 As FileInfo
File1 = DirectCast(x, FileInfo)
File2 = DirectCast(y, FileInfo)
Compare = DateTime.Compare(File2.LastWriteTime, File1.LastWriteTime)
End Function
End Class
Public Sub Main()
'
' Add your code here
Dim BackupDir As String = Dts.Variables("BackupDir").Value.ToString
Dim DatabaseName As String = Dts.Variables("DatabaseName").Value.ToString
Dim FilesToKeep As Integer = CInt(Dts.Variables("FilesToKeep").Value)
Dim NewBackupFile = New FileInfo(BackupDir & Dts.Variables("BackupFileName").Value.ToString)
Dim directory = New DirectoryInfo(BackupDir)
Dim AllBackups = directory.GetFiles(DatabaseName & "_*")
Dim filename As String = ""
Dim oldesttime As Date = Now
Dim FilesCount As Integer = 0
Array.Sort(AllBackups, New CompareFileLastWriteTime)
Dts.Variables("BackupEndTime").Value = Now
For Each entry In AllBackups
If oldesttime > entry.LastWriteTime Then
oldesttime = entry.LastWriteTime
filename = entry.FullName
FilesCount += 1
If FilesCount > FilesToKeep AndAlso filename <> "" Then
File.Delete(filename)
FilesCount -= 1
End If
End If
Next
Dts.Variables("BackupFileSize").Value = CInt(NewBackupFile.Length / 1024)
Dts.TaskResult = ScriptResults.Success
End Sub
End Class
The package has the below variables:
For these variables, these variables need to be configured:
1. InstanceName
2. DatabaseName
3. BackupDir (Olap backup directory, please note that you need to replace the single \ with \\ if you set value through sql agent job)
4. TimeZone (As the SSAS DMVs record the times as the UTC time, so you need to configure this TimeZone to your local time, for instance, New Zealand is 12.)
5. BackupFilesToKeep (How many backup files need to be kept)
The BackupXMLA variable is an expression, which will be changed dynamically for each loop:
"<Backup xmlns=\"http://schemas.microsoft.com/analysisservices/2003/engine\">
<Object>
<DatabaseID>" + @[User::DatabaseName] + "</DatabaseID>
</Object>
<File>" + @[User::BackupFileName] + "</File>
</Backup>"
This is the command to do the SSAS database backup.
I have attached the package here.
Intelligent_OLAPDB_Backup_BICOE.dtsx (289.11 kb)
An SQL agent job can set up to perform the backup and remember to set the variables below to your own values:
