There are many cases where IT-managed backup solutions do not scale well to the requirement. One example might be test and development environments where ad-hoc backup and easy restore is vital.
Standard BPC backup method as described in the documentation is most suitable for production environments where data structures and processes have been thoroughly tested and do not change rapidly. The method described below can only create system snapshots without audit and OLAP information. It may not meet enterprise-level disaster recovery requirements but can be used for smaller implementations.
One day I had to analyze existing script logic for a customer. Their development system is synced with production environment every once in a while. As I did not want to blow up their “production” development environment, I created an ad-hoc appset for my specific needs. I also wanted to be able to back up and restore this new appset without involving the IT or technical staff for the reasons most can understand: any extra resources involved in project create unnecessary overhead which is better to avoid if possible.
Get to know the environment – the tools
Server manager requires three files with specific folder hierarchy in order restore the appset: FileDB.zip, WebFolders.zip and SQL.bak.
FileDB.zip and WebFolders.zip are compressed copy of files stored on the server: PC_MS\FileDB\<APPSET>\ for FileDB and PC_MS\WebFolders\<APPSET>\ for Webfolders. We need to grab everything below these folders and zip them up. Well, almost anything, we probably do not want the temporary files stored in the PrivatePublications folders as they may contain large number of obsolete files.
Windows platform provides few tools for end users to take advantage of. The tool for managing entire trees of files and folders has been XCOPY until Windows Server 2008 which includes much better utility called Robocopy. Using this one can configure incremental backups and exclude subfolder such as PrivatePublications which can grow huge if not properly maintained.
SSIS is probably already well-known tool for any BPC enthusiast and requires little introduction.
Putting all together
First we need to configure the SSIS package containing the pieces which dump the files that can be easily consumed by the Server Manager. We start with empty SSIS package and add tree tasks. We also need to configure database server connection.
Execute SQL task
This task contains raw SQL statement which backs up the BPC database.
The following SQL script can be modified as per the specific needs:
EXECUTE master.dbo.xp_create_subdir N’E:\Backup\Nightly\SQL’
BACKUP DATABASE [TestinAppSet] TO DISK = N’E:\Backup\Ad-hoc\SQL\TestinAppSet.bak’ WITH NOFORMAT, INIT, NAME = N’TestinAppSet’, SKIP, NOREWIND, NOUNLOAD, STATS = 10
Execute process task „Copy FileDB“
Back up FileDB using Robocopy with Execute process task.
Enter the follwoing as argument:
“E:\PC_MS\Data\FileDB\TestinAppSet” “E:\Backup\Ad-hoc\FileDB\TestinAppSet” /S /E
Execute process task „Copy WebFolders“
For this we use the same Execute Process Task but we enter different argument this time. Command line below we will exclude all subtrees and files having the name PrivatePublications:
“E:\PC_MS\Data\WebFolder\TestinAppSet” “E:\Backup\Ad-hoc\WebFolder\TestinAppSet” /S /E /XD PRIVATEPUBLICATIONS
Once the SSIS package has been set up in this way it can be added to the DM package list and executed:
At this point it does not have any configurable options but almost anything can be added via MODIFYSCRIPT/GLOBAL variables including dynamic appset and path names inside SSIS task.
It should be noted that Server Manager requires the file database being zipped. It can be done easily prior to restore process or the zipping feature could be built in to the SSIS package itself. In the latter case one can not perform incremental file system backups. Also, zipping requires 3rd party tool which may vary from system to system and can not be standardized.