New user here and, while I'm a seasoned Windows Server Administrator, I am an absolute SQL novice. The company I began working for recently uses a lot of different versions of SQL for different aspects of their business: My boss has specifically tasked me with finding out how to backup SQL custom reports. I guess before I began here they had a server fail. The SQL server instance was backed up using their Cloud Backup Service (as I understand it, we pay for an additional plugin specifically for backing up SQL instances), but upon restoring the instance the custom reports were never recovered. Is there a specific way to backup and recover these custom reports? They have done without them for now, but in the future we need to make sure the custom reports are backed up, as well.
If anyone can provide any advice on how to do this, where to look, or wants to explain SQL and custom reporting in layman's terms, I would appreciate any and all help or pushes in the right direction. If you need any specific info like the version of SQL, etc., I will try and get it and respond with that info.
So, I've done a lot more research and tied the custom reports to .rdl files. The problem I'm encountering now is that the .rdl files are not store locally on the server (and as such cannot be backed up), but are stored as a "BLOB" in the ReportServer database. I've found the link below, which describes how to download all of the .rdl files by using a script through PowerShell:
RS Utility seems like the native SQL tool to use to backup. If anyone at all can help, my question would be am I at least on the right track? Anyone know how we would restore the .rdl files to the database, assuming I am able to download them using a script? It is SQL Server 2008 R2. Again, any comments would be helpful.
Since you are backing up the databases - that would (or should) include the ReportServer and ReportServerTempDB databases. Restoring those databases would include any reports published to that instance of SSRS.
To insure that you can restore the databases and get everything back up and running - you need to save the encryption key and back that up to a safe location. After restoring the SSRS databases you would then restore the encryption key and everything would be recovered.
Thank you so much for your reply. Would the encryption key also be backed up through a SQL maintenance plan? I believe the problem in this instance was that we were using a backup vendor's SQL aware plugin to backup the SQL Databases, so when the DB was restored from our Backupset it did not include the custom reports we are missing. Again, I don't have much experience with SQL, but I'm assuming backups are better created using SQL's Maintenance Plans to ensure all the data is recovered properly?
The other problem that has risen (and I can create a new topic for it) is backing up SQL Express instances, since there is no Maintenance Plan option or SQL Agent. I've been told this can be automated through Task Scheduler, but again not something I have ever done before. Am I on the right track on how to backup the instances of SQL Express?
The encryption key is not backed up with a database backup - it needs to be backed up using the SSRS Configuration Manager and only needs to be done once. Once you have it backed up - make sure it gets copied to a safe location.
I don't know what utility was being used - but the utility would have backed up the database and everything in the database. There is no way a restore 'lost' the custom reports - they existed in the database and were restored. The only thing I can think of is that the reports were not accessible because the encryption key was not applied to the newly restored databases.
As for SQL Express - you can use task scheduled to automate SQLCMD scripts to perform the backups, or you can use the same utility that was used in the above scenario.