Move SQL Reporting Database to new server

Moving a Sql Reporting Database from one server to another is quite simple thanks to the tools available. We were tasked with an initiative to consolidate and migrate a bunch of servers to VMs as well as upgrading all SQL Server instances from 2008 R2 to 2016.

The SSRS webservice instances had been moved to a VM previously. This post will demonstrate the simple steps to move the Reporting DB to a new server and configure the SSRS web server accordingly. We have decided to go the backup/restore route vs. detaching and attaching. For restoring, Microsoft advises as follows:

When restoring the databases, be sure to include the MOVE argument so that you can specify a path. Use the NORECOVERY argument to perform the initial restore; this keeps the database in a RESTORING state, giving you time to review log backups to determine which one to restore. The final step repeats the RESTORE operation with the RECOVERY argument.
The MOVE argument uses the logical name of the data file. To find the logical name, execute the following statement: RESTORE FILELISTONLY FROM DISK=’C:\ReportServerData.bak’;
To find the file position, execute the following statement: RESTORE HEADERONLY FROM DISK=’C:\ReportServerData.bak’;
When restoring the database and log files, you should run each RESTORE operation separately.

  1. Perform a Backup of the Reporting Database.
  2. Export the Encryption Keys from the Report server using Reporting Services Configuration Manager (RSCM).
  3. Restore the Database to the new server.
  4. Shut down the service via Report Services Configuration Manager. Our environment is load balanced so we were able to have zero down time. First take one web server out of rotation, perform steps below,  add back to rotation and move onto the next server.  As we migrated the initial web server to the new database, the remaining servers continue serve reports from the old database instance until taken offline.

    Stop Reporting Server
    Stop Reporting Server
  5. Make note of the Current report server Database Credentials. If the login does not exist in Target server add it.

    Current Report Server Database Credentials
    Current Report Server Database Credentials
  6. Change the Current Reporting Database in RSCM

    Current Report Server Database
    Current Report Server Database
  7. Import the encryption key
  8. Start up the Report Service

A Note, per Microsoft:

A Reporting Services installation requires that the SQL Server Database Engine instance include the RSExecRole role. Role creation, login registration, and role assignments occur when you set the report server database connection through the Reporting Services Configuration tool. If you use alternate approaches (specifically, if you use the rsconfig.exe command prompt utility) to configure the connection, the report server will not be in a working state. You might have to write WMI code to make the report server available.

 If you have issues with subscriptions or see the following error: The EXECUTE permission was denied on the object ‘xp_sqlagent_notify’, database ‘mssqlsystemresource’, schema ‘sys’ try running the following grants:

GRANT EXECUTE ON master.dbo.xp_sqlagent_notify TO RSExecRole
GO
GRANT EXECUTE ON master.dbo.xp_sqlagent_enum_jobs TO RSExecRole
GO
GRANT EXECUTE ON master.dbo.xp_sqlagent_is_starting TO RSExecRole
GO
USE msdb
GO
— Permissions for SQL Agent SP’s
GRANT EXECUTE ON msdb.dbo.sp_help_category TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_add_category TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_add_job TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_add_jobserver TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_add_jobstep TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_add_jobschedule TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_help_job TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_delete_job TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_help_jobschedule TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_verify_job_identifiers TO RSExecRole
GO
GRANT SELECT ON msdb.dbo.sysjobs TO RSExecRole
GO
GRANT SELECT ON msdb.dbo.syscategories TO RSExecRole
GO


Leave a Reply