All the data for the reports in your report manager is stored in the Reportserver database. So it is wise to make a backup of this database every day.
The best way to do this is to create a SQL Server Agent Job and schedule it daily. Enter the SQL Statement below. This makes sure a backup of the Reportserver data is made every day!
{geshi xml:lang=“tsql” lines=“false”}
-- BACKUP ReportServer
-- Alle rapportages staan in deze databases en moeten iedere dag gebackupt worden!
-- UITLEG BACKUP PARAMETERS:
-- NOFORMAT: Specifies that the media header should not be written on all
-- volumes used for this backup operation. This is the default behavior.
-- INIT: Specifies that all backup sets should be overwritten, but preserves
-- the media header. If INIT is specified, any existing backup set on that device is overwritten.
-- STAT: Displays a message each time another percentage completes, and is used to gauge progress.
-- If percentage is omitted, SQL Server displays a message after each 10 percent is completed.
USE master;
BACKUP DATABASE [ReportServer] TO DISK = N’E:SQLBACKUPReportServer.BAK’
WITH NOFORMAT, INIT, NAME = N’ReportServer’, SKIP, NOREWIND, NOUNLOAD, STATS = 10{/geshi}
Note:
The ReportserverTempDB does not need to be backed up
Frequently asked questions
Which database do I back up for Reporting Services?
The ReportServer database. All the data for the reports in Report Manager is stored there. Make a backup of it every day.
How do I schedule a daily backup of the ReportServer database?
Create a SQL Server Agent Job and schedule it daily. The job runs a BACKUP DATABASE [ReportServer] TO DISK statement.
Do I also need to back up the ReportServerTempDB?
No. The ReportServerTempDB does not need to be backed up. Only the ReportServer database holds the reports and must be in the backup every day.
What does the INIT option do in a SQL Server BACKUP DATABASE?
INIT overwrites all existing backup sets on the device, but keeps the media header.
What does STATS = 10 do in a SQL Server backup?
STATS shows a message each time a certain percentage is done. That way you can follow the progress. If you leave out the percentage, SQL Server shows a message after every 10 percent.
Comments
Related blogs
Subtotal Bold in a Matrix
SSRS Best Practice I - Limit datasets
How do I back up my SSRS report as RDL