By: Scott Murray
Overview
Now that you have your SSRS reports and website up and running, you need to think about the backup processes you will employ to keep your work from succumbing to a disaster.
Source Control - check!
First, if possible, I would recommend using a source control system to maintain the report, data source, solution, and project files that you are developing in SSDT-BI (Visual Studio). A source control system, such as Team Foundation Server (TFS), will allow you to maintain multiple iterations of each of these files. If you do not use a source control system, you should have a file backup process in place to copy files to an alternate location. Additionally, if you customize your SSRS settings, it would be beneficial to also backup and source control the report server configuration files such as, Rsreportserver.config, Rssvrpolicy.config, Rsmgrpolicy.config, Reportingservicesservice.exe.config, and Web.config.
Backup Databases - check!
After setting source control and / or backup for your SSRS files, the next step is to regularly backup your ReportServer and ReportServerTempDB databases. The backup process is no different than the method you would use to backup other databases; for details on completing a full database, I would recommend this tutorial.
Also, be sure that if you are using a 3rd party backup tool, it is completing SQL Server native backups and not just a file system or image backup. The size of the reporting services databases is usually rather small, so full backups can normally be easily executed.
Backup Encryption Key - check!
When security is employed by SSRS, it applies encryption to the following items:
- Data source credentials.
- The unattended user account information which is used to connect to a remote server in order get external images or data.
- Encrypted values in a subscription.
- Credentials used to connect to the Report Server database itself.
In the event you restore a SSRS database, the encryption key will need to be loaded onto the new server to allow that server to read and utilize all the items noted in the above list. Otherwise an error will result when attempting to navigate to the Report Server. Of course, you could recreate a SSRS key on the new server and then redeploy all the data sets, data sources, and reports. In that situation though, you would still have to recreate all the security for those folders (and related reports) too. Having the key file provides a quick and efficient way to restore security for a restored SSRS instance.
Backing up (and restoring the key) can all be done from the SQL Server 2017 Reporting Services Configuration Manager.
Clicking on the Backup button allows for the creation of the SSRS Key file. Be sure to use a sophisticated password for the key file, keep the SNK encryption file somewhere safe (source control maybe!), and also log the password (for remembering later) in your password manager.
In the event a SSRS database needs to be restored onto a new server, the key file is loaded using the restore option. Once you select the location and file, the password needs to be entered.
The key can also be regenerated using the change option or deleted using the delete option. However, note the delete option requires you to re-establish encrypted items including data source connections and subscriptions. This option is a last resort effort.
With a backup plan in place, you are now ready to show off your report server work to your boss, your colleagues, and whomever else will listen!
