August 10, 2017 at 11:42 pm
Hi Experts,
Can anybody help me finding out how to check if the SSRS encryption key have been backup up successfully.
Is there any system table which contains this information?
Thanks in advance,
Dev
August 21, 2017 at 5:43 am
I've done a bit of digging, and i can't see any tables that reference the keys within the report server or report server TempDB.
Are you wanting to see if your backup has worked, or find out the last time it was backed up?
Could you not move the backup to another server and restore the key to ensure that you get the results as expected?
I've not had a backup key fail as of yet.
August 21, 2017 at 7:07 am
Go back it up right now... then you are sure that it is backed up! 🙂
https://docs.microsoft.com/en-us/sql/reporting-services/install-windows/ssrs-encryption-keys-manage-encryption-keys
August 21, 2017 at 9:55 am
[/quote]
Thanks for your research and reply. Actually my customer want a report which shows the backup date of Encryption keys just like we can find about db backup info in msdb..backupset.
August 21, 2017 at 9:58 am
Chris Harshman - Monday, August 21, 2017 7:07 AMGo back it up right now... then you are sure that it is backed up! 🙂
https://docs.microsoft.com/en-us/sql/reporting-services/install-windows/ssrs-encryption-keys-manage-encryption-keys
Thanks for your advice Chris :). I am working on
dev.tridib - Monday, August 21, 2017 9:55 AMI've done a bit of digging, and i can't see any tables that reference the keys within the report server or report server TempDB.
Are you wanting to see if your backup has worked, or find out the last time it was backed up?
Could you not move the backup to another server and restore the key to ensure that you get the results as expected?
I've not had a backup key fail as of yet.
Thanks for your research and reply. Actually my customer want a report which shows the backup date of Encryption keys just like we can find about db backup info in msdb..backupset.
[/quote]
I am able to find it thru powershell but the script is not generic since we need to provide the backup path for every server. That is why looking for metadata information so that the script can be generic.
August 22, 2017 at 8:18 am
What if SSRS is load balanced across 2 SSRS servers with one SSRS REPORTSERVER DB on a seperate SQL server.
Will backing up on one of the SSRS servers be sufficient in a recovery situation?
August 24, 2017 at 4:54 pm
dev.tridib - Monday, August 21, 2017 9:58 AMThanks for your advice Chris :). I am working onI am able to find it thru powershell but the script is not generic since we need to provide the backup path for every server. That is why looking for metadata information so that the script can be generic.
I don't think the path is stored in the database. The basic info for the key is in the ReportServer.dbo.Keys table.
Being that you can move the backup of the key and it wouldn't be stored in the database, storing the path may not be too valuable anyway. Not sure how you are getting the date backed up with Powershell or what backup path you are providing to Powershell.
Are you reading the timestamp for the file itself? Do you have a company policy of some sort on where to backup these keys?
If you are using the default location for the backup of the instance there are some things you could do using the registry.
Or does Powershell has some command to get the last backup of the SSRS encryption key somehow? If it does, it must be available somewhere so I am curious as to what the command is that you would be using or how you are doing this.
Sue
August 27, 2017 at 9:55 pm
Sue_H - Thursday, August 24, 2017 4:54 PMdev.tridib - Monday, August 21, 2017 9:58 AMThanks for your advice Chris :). I am working onI am able to find it thru powershell but the script is not generic since we need to provide the backup path for every server. That is why looking for metadata information so that the script can be generic.I don't think the path is stored in the database. The basic info for the key is in the ReportServer.dbo.Keys table.
Being that you can move the backup of the key and it wouldn't be stored in the database, storing the path may not be too valuable anyway. Not sure how you are getting the date backed up with Powershell or what backup path you are providing to Powershell.
Are you reading the timestamp for the file itself? Do you have a company policy of some sort on where to backup these keys?
If you are using the default location for the backup of the instance there are some things you could do using the registry.
Or does Powershell has some command to get the last backup of the SSRS encryption key somehow? If it does, it must be available somewhere so I am curious as to what the command is that you would be using or how you are doing this.Sue
Hi Sue,
Thanks for your reply. In Powershell I am just getting the file creation date for .snk files. The path is not standard and have to be modified for each server. Powershell does not have any command to get the last backup date for SSRS encryption key.
This is the snippet:$bkpPath = "D:\KeyBackup\*.snk" # This is the dependency area
$file=Get-ChildItem $bkpPath | Select-object @{E={$_.Name};label="EncryptionKey"}, @{E={$_.LastWriteTime};label="BackupDate"}
Dev
August 28, 2017 at 10:40 am
dev.tridib - Sunday, August 27, 2017 9:55 PMHi Sue,
Thanks for your reply. In Powershell I am just getting the file creation date for .snk files. The path is not standard and have to be modified for each server. Powershell does not have any command to get the last backup date for SSRS encryption key.
This is the snippet:$bkpPath = "D:\KeyBackup\*.snk" # This is the dependency area$file=Get-ChildItem $bkpPath | Select-object @{E={$_.Name};label="EncryptionKey"}, @{E={$_.LastWriteTime};label="BackupDate"}
Dev
That's what I thought.
So you would still have the issue that it may not be the latest backup of the key that I mentioned. They can be moved anywhere so finding one on that server doesn't necessarily mean its the last backup. Whatever it is the customer wants with this, you may want to suggest that they just have a policy/documentation to do the backup every xx months or something along those lines. Some procedural type of mechanism to support whatever it is they need with that report.
Sue
August 29, 2017 at 9:36 am
Sue_H - Monday, August 28, 2017 10:40 AMdev.tridib - Sunday, August 27, 2017 9:55 PMHi Sue,
Thanks for your reply. In Powershell I am just getting the file creation date for .snk files. The path is not standard and have to be modified for each server. Powershell does not have any command to get the last backup date for SSRS encryption key.
This is the snippet:$bkpPath = "D:\KeyBackup\*.snk" # This is the dependency area$file=Get-ChildItem $bkpPath | Select-object @{E={$_.Name};label="EncryptionKey"}, @{E={$_.LastWriteTime};label="BackupDate"}
Dev
That's what I thought.
So you would still have the issue that it may not be the latest backup of the key that I mentioned. They can be moved anywhere so finding one on that server doesn't necessarily mean its the last backup. Whatever it is the customer wants with this, you may want to suggest that they just have a policy/documentation to do the backup every xx months or something along those lines. Some procedural type of mechanism to support whatever it is they need with that report.Sue
That's correct. I have communicated the reliability of the report.
Thanks for your suggestion.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply