I have several SSAS databases. I only want to script out the XMLA for them on a nightly basis. This will be a second tier backup in addition to the regular backups we have.
How do I auto-generate XMLA scripts for all the databases?
backupscriptingsql-server-2012ssas
I have several SSAS databases. I only want to script out the XMLA for them on a nightly basis. This will be a second tier backup in addition to the regular backups we have.
How do I auto-generate XMLA scripts for all the databases?
This can in fact be done. There are probably a few ways to do it, and here is a fairly straightforward example. For this solution, you will use a combination of:
Execute Script Task
that uses the Analysis Management Objects (AMO).VariableName|Scope|Type
BackupDir | Package level or Foreach level | String
DatabaseId | Package level or Foreach level | String
DatabaseName | Package level or Foreach level | String
InstanceForDatasource | Package level | String
ConnectionString | Package level | String
Set the EvaluateAsExpression property to true.
Set the Expression property as follows: Data Source="+ @[User::InstanceForDatasource] +";Provider=MSOLAP.4;Integrated Security=SSPI;
Please note that this approach can be expanded upon to have the entire ConnectionString be dynamic and dictated by the calling SQL Agent job step if necessary.
XMLAScript | Package or Foreach level | String
Use a real connection in design time so that the metadata plays nice.
Creating the connection managers now isn't required, but it makes it easier for later. For each task in the process, you will have the appropriate connection manager available in the drop-down without the need to create any on-the-fly.
ADO.NET
[User::ConnectionString]
OLEDB
[User::ConnectionString] + "Format=Tabular;"
Analysis Services
[User::ConnectionString] = "Impersonation Level=Impersonate;"
Here, you will create a Foreach based on the Catalogs schema rowset. This will get us the DatabaseName
for each database in the instance and the DatabaseName
will be put into its corresponding variable.
Set the script to use Visual Basic.
Set the ReadOnlyVariables and ReadWriteVariables as follows:
User::ConnectionString,User::DatabaseName,User::InstanceForDatasource
User::BackupDir,User::DatabaseId,User::XMLAScript
Edit the Script
Add a reference to the Analysis Management Objects (AMO) assembly.
Right-click on the project name (should be the top-most item in the Project Explorer in the Visual Studio window that opened when you clicked Edit Script) and select Add Reference.
Add a reference to the Analysis Services Objects component. The dll for AMO should be located in <SQL Server InstallationDrive>:\Program Files\Microsoft SQL Server\<SQL Server Version>\SDK\Assemblies
.
In the script header, add an Imports statement to use AMO: Imports Microsoft.AnalysisServices
Replace the contents of Public Sub Main() with the following script:
Public Sub Main()
'
Dim ASServer As New Microsoft.AnalysisServices.Server()
Dim AsDatabase As New Microsoft.AnalysisServices.Database
Dim ASConn As String = ""
Dim ASDatabaseName As String = ""
Dim ASDatabaseId As String = ""
'Create a variable that uses the dynamic ConnectionString variable
ASConn = Dts.Variables("ConnectionString").Value
'What database are we working with in this iteration of the Foreach?
ASDatabaseName = Dts.Variables("DatabaseName").Value
'Use the Analysis Services AMO to get the DatabaseId for this DatabaseName.
' It is necessary to get the DatabaseId because the XMLA backup command requires the DatabaseId,
' and if at any point the database is renamed, the DatabaseId will differ from the DatabaseName.
Try
'Establish the connection to SSAS.
ASServer.Connect(ASConn)
'Get the database.
AsDatabase = ASServer.Databases.FindByName(ASDatabaseName)
'***ONLY IF the EstimatedSize > 0***.
' --> If it is 0, it could be a corrupted database which causes the backup process to stop.
' --> In that case, we will leave the DatabaseId variable blank to be a visiblie indicator that the db is corrupt.
'***ONLY IF the number of cubes > 0***.
' --> The databse could be corrupt or backup could hang if there are no cubes.
If AsDatabase.EstimatedSize > 0 And AsDatabase.Cubes.Count > 0 Then
'Get the DatabaseId
'Retrieve the DatabaseId from the Databases collection.
ASDatabaseId = ASServer.Databases.FindByName(ASDatabaseName).ID
Else
Dts.Events.FireWarning(0, "Verifying database", "The estimated size and/or number of cubes in the database does not meet the requirements. Estimated Size= " & AsDatabase.EstimatedSize & "; " & "Number of cubes= " & AsDatabase.Cubes.Count, String.Empty, 0)
End If
Catch ex As Exception
'Couldn't connect. Do not error out because maybe the next iteration will succeed.
Dts.Events.FireWarning(0, "Establishing SSAS Connection", "Unable to connect to the SSAS Server with ConnectionString= '" & ASConn & "'. Error: " & ex.ToString, String.Empty, 0)
End Try
'Print info for output while testing/debugging.
Dts.Events.FireInformation(0, "Establishing SSAS Connection", "Connection established. ConnectionString= '" & ASConn & "'.", String.Empty, 0, True)
'Put the DatabaseId into the SSIS variable.
Dts.Variables("DatabaseId").Value = ASDatabaseId
'If we were able to retrieve the DatabaseId, then generate the backup directory and filename and the XMLA script.
If ASDatabaseId <> "" Then
'Print info for output while testing/debugging.
Dts.Events.FireInformation(0, "Retrieving DatabaseId", "DatabaseId retrieved for " & ASDatabaseName & ". DatabaseId= '" & ASDatabaseId & "'.", String.Empty, 0, True)
'Get timestamp for the backup file
Dim timeStamp As String = Now.Year & _
Right("0" & Now.Month, 2) & _
Right("0" & Now.Day, 2) & _
Right("0" & Now.Hour, 2) & _
Right("0" & Now.Minute, 2) & _
Right("0" & Now.Second, 2)
'Get the filename and location for the backupfile
Dim filePath As String = "\\<servershare>\" & Dts.Variables("ServerForDatasource").Value & "\" & ASDatabaseName
Dim fileName As String = ASDatabaseName & "_" & timeStamp & ".abf"
'Put the filepath into the SSIS variable
Dts.Variables("BackupDir").Value = filePath
'Print info for output while testing/debugging
Dts.Events.FireInformation(0, "Retrieving full backup path", "BackupPath= '" & filePath & "\" & fileName & "'.", String.Empty, 0, True)
'Build XMLA script
Dim xmlaScript As String = ""
xmlaScript = "<Backup xmlns=""http://schemas.microsoft.com/analysisservices/2003/engine"">" & _
"<Object><DatabaseID>" & ASDatabaseId & "</DatabaseID></Object>" & _
"<File>" & filePath & "\" & fileName & "</File>" & _
"</Backup>"
'Put the XMLA script into the SSIS variable
Dts.Variables("XMLAScript").Value = xmlaScript
'Print info for output while testing/debugging.
Dts.Events.FireInformation(0, "Generate XMLAScript", "XMLAScript= '" & xmlaScript & "'.", String.Empty, 0, True)
Else
'Print info for output while testing/debugging.
'If DatabaseId is empty, we will not perform the rest of the tasks in the Foreach container.
Dts.Events.FireWarning(0, "Retrieving DatabaseId", "Unable to retrieve the DatabaseId for Database= '" & ASDatabaseName & "'.", String.Empty, 0)
End If
' Return success
Dts.TaskResult = ScriptResults.Success
End Sub
It is important to set UseDirectoryIfExists
to true
to avoid an error if the backup directory already exists.
This precedence constraint will handle a missing DatabaseId. The DatabaseId would be missing if a connection to the SSAS server could not be established in the vbscript, or if the database is corrupted. You do not want to back up a corrupted database. It will bring the backup to a halt.
Example of Expression: `@[User::DatabaseId] !=""
InstanceForDatasource
to the instance name for the step.
Property Path
: \Package.Variables[User::InstanceForDatasource].Properties[Value]
Notes:
To make the entire backup directory dynamic, you would just have to add another variable for that and account for it in the vbscript.
Again, if necessary, the entire connection string variable could be set by the SQL Agent job instead of just the instance name.
No you don't need to install the Ola's solution again since the initially installed commandexecute.sql and databasebackup.sql would have been saved on master DB as SP.
You just need to create a new job , as one created early but this time for 5 left over DB;s and schedule it weekly per you're timings.
EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',-- Name of those 5 DB's would come here
@Directory = 'C:\Backup',-- You're backup path
@BackupType = 'FULL',
@Verify = 'Y',
@Compress = 'Y',
@CheckSum = 'Y'
You can use above script while creating the T-SQL job and schedule it accordingly.
More details on how to do above refer to schedule job
Also, just to make sure both, initial backup and this newly created backup job not to run on same time you can put a condition or per the analysis when you're daily backup job completes, you can schedule this new weekly/nightly backup job for remaining 5 DB's
Best Answer
You can do it using powershell.
References :