|Script to backup SSAS database and delete old backup files (using ascmd.exe)|
|Written by Vidas Matelis|
|Thursday, 10 February 2011 00:55|
Here is simple script that will backup one Microsoft SQL Server Analysis Services (SSAS) database and will include timestamp in backup file name. Also this script will delete backup files that have date of creation older than specified number of days.
Please note that most of this script comes in from readme file for ascmd utility. I just added backup timestamp (code comes from another example of the same document) and added logic to delete older backup files.
To run this script you need to download source code for ascmd utility and then compile it to executable.
This script has 2 parts:
You must modify backup.bat file before execution. You must setup values for SSASBackupFolder, SSASScriptFolder, SSASDBName and KeepBackupDays (names are self-explanatory). If any of above listed parameters have a space (if folder or DB Name), then you will need to include string values in the double quotes. It is also very likely that you will need to change how you generate timestamp as this depends on your regional date format.
After successful backup this script will delete backup files from the same folder that are older than KeepBackupDays parameter value. Backup file age will be based not on the timestamp included in the file name, but on file creation date.
You can schedule execution of this script from SQL Server Agent.
Below is source code for both scripts
ForFiles /P %SSASBackupFolder% /M %SSASDBName%*.abf /D -%KeepBackupDays% /C "cmd /c del @path"
Use at your own risk!
- Script to automate SSAS partition management (SQL + SSIS)
- PowerShell script to process all dimensions and cubes in one DB limiting workload
- SSIS package to backup all Analysis Services databases on one instance
- SSIS Package to process all dimensions in one SSAS Database
- SSIS Package to drop and create partitions based on data in SQL Server table