I'd like to backup my SSAS database via a SQL Server Agent job in SSMS – and I’d also like to append the day number to the end of the file and allow overwrites. This will ensure that I only ever have a month of backups (i.e. Jan 1st backup will be called backup_01.abf, Jan 2nd = backup_02.abf etc.)
I connected to the SSAS DB via SSMS and scripted out the backup procedure, which is as follows:
{
"backup": {
"database": "ExampleDB",
"file": "Backup.abf",
"allowOverwrite": true,
"applyCompression": false
}
}
I believe I can simply add this as a Step in a server agent job as a SQL Server Analysis Services Command.
But how can I then append the day of the month to the file?
Can’t seem to find much about this online
[EDIT]
Using a combination of what Vaishali responded with below, and another article I've found online about this, I've performed the following:
Created a linked server (Link_SSAS) Generated the following script:
Declare @XMLA nvarchar(1000),
@DateSerial nvarchar(35)
-- Get Day Number from GETDATE()
Set @DateSerial = RIGHT('0' + RTRIM(DAY(GETDATE())),2)
-- Create the XMLA string
Set @XMLA = N'<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>ExampleDB</DatabaseID>
</Object>
<File>C:\bak\Backup_' + @DateSerial + '.abf</File>
</Backup>'
-- Execute the string across the linked server
Exec (@XMLA) AT Link_SSAS;
The above xmla runs perfectly if I just execute it through a query window in SSMS However, when I try to put the XMLA into a job-step, i get an error
With Job Type set to T-SQL I get:
The Specified '@server' is invalid (valid values are returned by sp_helpserver)
If I run sp_helpserver, it does show my linked server as being there. Tried disconnected / reconnecting to the server.