Can we refresh/overwrite SQL Server Reporting databases.

Question

Can we refresh SQL Server Reporting databases.

We are in the process of the migration of reports from SQLServer 2016 to SQLServer 2019.

Week back we copied the reporting databases to another server and configured the reporting services then released for testing .

Now we would like to copy databases again to new server to sync up the changes from last week, can we restore backups from old server to new server by replacing the existing reporting databases on new server .

Post completion of restore copying encryption key to new server will work or any extra precautions needed here, please advise.

Answer 1

See Moving Report Server Databases to Another Computer (SSRS Native Mode)

Answer 2

Hi @Sai G

Here is an example of restoring an old version of SSRS database backup to a new server: https://www.mssqltips.com/sqlservertip/2692/migrating-sql-reporting-services-to-a-new-server-by-moving-the-reporting-services-databases/, maybe you can use it as a reference.

If the answer is helpful, please click Accept Answer and Up-Vote for the same. If you have any questions, please feel free to let me know.

Best regards,

Aniya

Answer 3

Thank you for your responses,

 

Yes we can refresh/restore the reporting server databases even after we configured the reporting services ,But we should also take the backup of Encryption Key and then restore it on Target server. 

 

Below are the steps need to follow.

• Backup Reportserver and ReportServerTempdb database on source server
• Backup Encryption key from source server using sql server reporting configuration manager
• Restore the backup files on target server with replace option
• Restore the encryption key taken from source server.
• DELETE  keys from [Reportserver]. [dbo].[Keys] WHERE InstanceName LIKE 'oldinstance/sourceinstance'
• Also please take the backups of target server reporting databases and encryption key for safe side.

Thanks &Regards,

Sai