With a distinct lack of up-to-date, fully featured or built-in options to get Reporting Services content cleanly from A to B, it can often be a challenging task maintaining proper Development and QA environments or even moving reports from a SharePoint integrated installation to a native mode one, and vice versa.
由于明显缺乏最新的，功能齐全的或内置的选项，无法从A到B干净地获取Reporting Services内容，因此维护适当的开发和QA环境甚至从集成了SharePoint的报表中移动报表通常是一项艰巨的任务安装为纯模式，反之亦然。
I want to explore the two most efficient methods of bulk-migrating Reporting Services content & also explore other options I’ve used over the years and those that have come and gone.
我想探索批量迁移Reporting Services内容的两种最有效的方法，并探索我多年来使用的其他选项以及已经出现和消失的选项。
RS脚本 (RS Scripter)
You may still come across this first migration tool if you search for Reporting Services Migration or similar. There are several highly ranked links with publication dates in the last 5 years too! Don’t be fooled though, this tool is now 12 years old. The .NET application was created by SQL Server MVP Jasper Smith. This article “The Reporting Services Scripter”, (written by Kevin Kline back in 2006) describes the tool with a now defunct link to its original download page. (WebArchive)
如果您搜索Reporting Services迁移或类似内容，则可能仍然会遇到第一个迁移工具。 在过去的5年中，也有一些排名很高的链接与发布日期！ 不过，不要被愚弄了，这个工具已经有12年历史了。 .NET应用程序是由SQL Server MVP Jasper Smith创建的。 本文“ The Reporting Services Scripter” （由Kevin Kline在2006年编写）描述了该工具，该工具的原始下载页面现已失效。 （ WebArchive ）
This was a great tool to easily identify content in SSRS & migrate it directly to a target instance or download it to be uploaded later. It’s a shame it’s not still maintained as it became obsolete with the introduction of SQL Server 2008 R2. These new components and anything introduced since is not supported by RSScripter so they won’t be migrated. (Eg. Linked Reports & History)
这是一个很好的工具，可以轻松地识别SSRS中的内容并将其直接迁移到目标实例，或者下载它以便以后上传。 遗憾的是，由于SQL Server 2008 R2的引入，它已经过时了，所以它仍然没有得到维护。 这些新组件以及此后引入的任何组件均不受RSScripter支持，因此不会迁移。 （例如，链接的报告和历史记录）
You can still easily find the tool in a host of locations and it works fine for your normal folders, reports and data sources (though not the credentials).
您仍然可以在许多位置轻松找到该工具，并且该工具适用于常规文件夹，报告和数据源（尽管不是凭据）。
Reporting Services迁移工具 (Reporting Services Migration Tool)
This tool is often another high ranking result when searching for SSRS migration tools. Released by Microsoft in 2012, it looks like a promising, “official” result. “Perfect! There is hope!”…maybe not.
搜索SSRS迁移工具时，该工具通常是另一个高排名的结果。 由微软于2012年发布，它看起来是一个有希望的“官方”结果。 “完善！ 有希望！”……也许没有。
This tool works by generating a full array of PowerShell scripts based on your source content, whether that be from a native or SharePoint integrated instance.
此工具通过根据您的源内容（无论是来自本机实例还是SharePoint集成实例）生成完整的PowerShell脚本来工作。
The first main roadblock is that you can only deploy to a SharePoint Integrated instance. Even though the PowerShell scripts are right there for you to edit, the source content is all explicitly called out and any potential alterations to force a deployment to a Native mode instance will involve a great deal of time editing and debugging. (Yes, I’ve spent hours doing this before!).
第一个主要障碍是您只能部署到SharePoint集成实例。 即使您可以在那里编辑PowerShell脚本，也将显式调出源内容，并且任何可能的更改（强制将其部署到纯模式实例）都将花费大量的时间进行编辑和调试。 （是的，我之前已经花了几个小时！）。
If your target instance is a SharePoint integrated installation then great! Read no further. It’s not the easiest application to use but it will get you from A to B and you can dissect & edit the PowerShell code if you want to customize your migration.
如果您的目标实例是SharePoint集成安装，那就太好了！ 继续阅读。 它不是最容易使用的应用程序，但是它将使您从A转到B，如果要自定义迁移，则可以剖析和编辑PowerShell代码。
One feature that this tool has above almost all of the rest is that you can include Report History (if this is a feature you use) and not just the history settings. Although that does come with a stern warning:
该工具几乎具有其余所有功能之一，就是您可以包括“报告历史记录”（如果您使用的是此功能），而不仅仅是历史记录设置。 尽管这确实带有严厉的警告：
Please note that migrating report history requires direct modification to data in the report server catalog and is not publicly supported. Backing up the catalog before migration and verifying correctness of the reports after migration are strongly recommended.
请注意，迁移报告历史记录需要直接修改报告服务器目录中的数据，并且不受公共支持。 强烈建议在迁移之前备份目录并在迁移之后验证报告的正确性。
You can still find the Reporting Services Migration Tool hosted by Microsoft but there seems to have been no further development beyond the initial 2012 release despite this line in the description:
您仍然可以找到Microsoft托管的Reporting Services迁移工具 ，但是尽管说明中有以下内容，但似乎在2012年初始版本之后没有进一步的开发：
- Target server must be SharePoint integrated mode. (We are working on support for native mode.) 目标服务器必须为SharePoint集成模式。 （我们正在努力支持纯模式。）
Rs.exe VB脚本 (Rs.exe VB Script)
This Microsoft Page “Sample Reporting Services rs.exe Script to Copy Content between Report Servers” describes a sample script created by Benjamin Satzger at Microsoft, to migrate content between two Report Servers using the rs.exe application present in both modes of Reporting Services installation.
此Microsoft页面“在Reporting Server之间复制内容的示例Reporting Services rs.exe脚本”描述了Microsoft的Benjamin Satzger创建的示例脚本，该示例脚本使用存在两种Reporting Services安装方式的rs.exe应用程序在两个Report Server之间迁移内容。 。
It does cover some of the shortfalls of the RSScripter tool & it can also migrate content to a native mode installation, unlike the Reporting Services Migration Tool (but it will not migrate report history).
它确实弥补了RSScripter工具的一些不足，并且它还可以将内容迁移到纯模式安装，这与Reporting Services迁移工具不同 （但它不会迁移报告历史记录）。
There is some great documentation & details on the above Microsoft Docs page but it looks like the download link hasn’t (yet?) been updated from Codeplex, though the supported SQL Server versions do go right up to 2016 which is promising.
上面的Microsoft Docs页面上有一些很棒的文档和详细信息，但看起来下载链接尚未（是否？）已从Codeplex更新（尽管受支持SQL Server版本确实会持续到2016年），这很有希望。
The script is all written in VB.NET using the Reporting Services SOAP API. It’s not as easy to use or as flexible as the PowerShell module but it’s certainly a more complete and relevant option than the two I have mentioned thus far. No prior knowledge of VB.NET is required which is also a bonus.
该脚本全部使用Reporting Services SOAP API用VB.NET编写。 它不像PowerShell模块那样容易使用或灵活，但它肯定是一个比我到目前为止提到的两个更完整和相关的选项。 不需要VB.NET的先验知识，这也是一个好处。
不断回填 (Constant backfill)
This is a workaround I have personally adopted for a while but relates mainly to keeping various environments in line, and not a core migration method like the others. I wanted to mention it though as the migration of SSRS content is usually driven by the need to keep multiple non-production environments in line.
这是我个人采用的一种解决方法，但主要涉及使各种环境保持一致，而不是像其他方法那样采用核心迁移方法。 我想提一下它，因为SSRS内容的迁移通常是由保持多个非生产环境一致的需求驱动的。
This method became part of my BI agile release process about a year ago though it is not without its own drawbacks. Prior to its inception, several days of my time were swallowed up re-building DEV or QA environments when they became too far out of date or some piece of work had impacted the content on a Report Server. I’d have to copy down our production database, remove or update the Scale-out deployment contents (servers), re-point every data source we have, manually (over 30!) to its DEV or QA counterpart & update any stored credentials. Then we’d have to update folder security to remove all the production permissions. This would also bring the environment down for the day or more that it took to get everything in place.
大约一年前，这种方法成为我的BI敏捷发布过程的一部分，尽管它并非没有缺点。 在创建之初，我花了几天的时间重新构建DEV或QA环境，因为它们过时了或者某些工作已经影响了Report Server上的内容。 我必须复制我们的生产数据库，删除或更新横向扩展部署内容（服务器），重新指向我们拥有的每个数据源，手动（超过30个！）到其DEV或QA对等方并更新任何存储的凭据。 然后，我们必须更新文件夹安全性以删除所有生产权限。 这还将使环境瘫痪一天或更多，以至于一切都准备就绪。
While moving to an agile deployment method I began exploring better ways to keep our environments in line. If no releases are missed, backfilling at the time of each production release was the least time-consuming method of keeping all our DEV & QA environments up to production version & could actually be automated easily with PowerShell or as part of a CI/CD process.
在转向敏捷部署方法时，我开始探索使环境保持一致的更好方法。 如果没有遗漏任何发行版，则在每个生产发行版时进行回填是将所有DEV＆QA环境保持为生产版本的最省时的方法，并且实际上可以使用PowerShell轻松地进行自动化或作为CI / CD流程的一部分。
- Report kits are deployed to DEV & then QA 将报告套件部署到DEV，然后进行质量检查
- Once approved, a kit gets deployed to Production 批准后，工具包将部署到生产环境
- Kit is then automatically deployed to cold QA & DEV environments with the same production code. 然后，该套件将以相同的生产代码自动部署到冷QA和DEV环境中。
The main benefit of using this “backfill” method is that it removes the need, in most cases, to restore an entire database, recovering encryption keys, re-pointing data sources & altering security which can take more than a few hours to complete manually, depending on the size of your SSRS instance.
使用这种“回填”方法的主要好处是，在大多数情况下，它无需还原整个数据库，恢复加密密钥，重新指向数据源和更改安全性，而这些操作可能需要几个小时才能完成，具体取决于您的SSRS实例的大小。
dbatools PowerShell模块 (dbatools PowerShell module)
This is a bulk method approach but much more efficient than doing a database restore through Management Studio. It is literally a single command.
这是一种批量方法，但是比通过Management Studio进行数据库还原要有效得多。 它实际上是一个命令。
Copy-DbaDatabase -Source DBSOURCE\Instance -Destination DBTARGET\Instance -Database ReportServer -IncludeSupportDbs -WithReplace -BackupRestore -NetworkShare \\Share\SSRS_Migration
The -IncludeSupportDBs flag I use here is actually designed to include the ReportServer and ReportServerTempDB anyway but seeing as those are the only 2 I am migrating, I have called out the ReportServer DB and I’m using the flag to bring the TempDB over.
-IncludeSupportDBs标志 我在这里使用的实际上实际上是为了包括ReportServer和ReportServerTempDB而设计的，但是由于它们是我要迁移的仅有的两个，因此我已经调出ReportServer DB，并且正在使用该标志将TempDB移交给我。
You can then augment this script with the Reporting Services module to fix data source connection strings & make any further environment specific changes, which is actually my preferred migration method detailed in the conclusion below.
然后，可以使用Reporting Services模块扩展此脚本，以修复数据源连接字符串并进行任何其他特定于环境的更改，这实际上是我在以下结论中详细介绍的首选迁移方法。
Although dbatools doesn’t cater to Reporting Services specifically, it’s a very powerful module for interacting with databases. You can find a full command list on the dbatools site
尽管dbatools并不专门针对Reporting Services，但它是与数据库进行交互的功能非常强大的模块。 您可以在dbatools网站上找到完整的命令列表
Reporting Services PowerShell模块 (Reporting Services PowerShell module)
Similar in functionality and flexibility as the old RS Scripter, the official PowerShell module allows you to download & upload individual items, specific folders or a full instance of content. You can also utilize the new REST API for SQL Server 2016 installations. Here are a few code chunks for tasks you might perform during a migration.
与旧版RS Scripter的功能和灵活性类似，PowerShell官方模块允许您下载和上传单个项目，特定文件夹或完整的内容实例。 您还可以将新的REST API用于SQL Server 2016安装。 以下是一些代码块，用于您在迁移期间可能执行的任务。
迁移所有SSRS文件夹 (Migrate all SSRS folders)
This code chunk will migrate from a source environment, down to a local temporary folder then back up to the destination environment. Setting the $Recurse variable to $true will add that flag to the commands and include subfolder contents too. It’s a great way to quickly migrate content but be careful to note that this only migrates reports, shared data sources & datasets.
此代码块将从源环境迁移到本地临时文件夹，然后再备份到目标环境。 将$ Recurse变量设置为$ true会将该标志添加到命令中，并且也包含子文件夹的内容。 这是快速迁移内容的好方法，但请注意，这仅迁移报告，共享数据源和数据集。
$SourceUri = 'http://SOURCE_SSRS/reportserver/ReportService2010.asmx?wsdl'
$DestUri = 'http://DESTINATION_SSRS/reportserver/ReportService2010.asmx?wsdl'
try {
$SourceProxy = New-RsWebServiceProxy -ReportServerUri $SourceUri
$DestProxy = New-RsWebServiceProxy -ReportServerUri $DestUri
if($Recurse){
Out-RsFolderContent -Proxy $SourceProxy -RsFolder $RsFolder -Destination $tempFolder -Recurse
Write-RsFolderContent -Proxy $DestProxy -RsFolder $RsFolder -Path $tempFolder -Recurse -Overwrite
}
else {
Out-RsFolderContent -Proxy $SourceProxy -RsFolder $RsFolder -Destination $tempFolder
Write-RsFolderContent -Proxy $DestProxy -RsFolder $RsFolder -Path $tempFolder -Overwrite
}
}
catch {
throw $_.Exception.Message
}
恢复或备份加密密钥 (Restore or backup an Encryption Key)
Connect-RsReportServer -ComputerName 'SSRSComputerName' -ReportServerInstance 'MSSQLSERVER' -ReportServerUri 'http://ReportingURL/reportserver/ReportService2010.asmx?wsdl' -ReportServerVersion 12
Write-Verbose "Backup Encryption Key"
$encKey = Read-Host "Please provide a location to store the new encryption key (E.g. C:\Encrypt_Key.snk)"
$encKeyPass = Read-Host "Please provide the Password for the Encryption Key"
Write-Debug "Encryption Key location: $($encKey)"
Backup-RSEncryptionKey -Password $encKeyPass -KeyPath $encKey
Restore-RSEncryptionKey -Password $encKeyPass -KeyPath $encKey
I connect to the Report Server first, before restoring the key so that I don’t need to define the Report Server version, which is set in the Connect command. If you have dbatools however, you can get the version using the following command:
在还原密钥之前，我首先连接到Report Server，这样就无需定义在Connect命令中设置的Report Server版本。 但是，如果您拥有dbatools，则可以使用以下命令获取版本：
$SQLVersion = Get-DbaSqlInstanceProperty -SqlInstance $targetInstance | Where-Object{$_.Name -eq 'VersionMajor'}
从参考表更新数据源 (Update Data Sources from a reference table)
In this script excerpt, I utilize a process I have implemented to backup all shared data source information within SSRS to a database table. See the Appendix below for that script. I then prompt the user for all of the passwords so we aren’t storing them anywhere. You can consider using the hosts file to negate the need to update connection strings but we’ve opted not to use that method.
在此脚本摘录中，我利用已实现的过程将SSRS中的所有共享数据源信息备份到数据库表中。 有关该脚本，请参见下面的附录 。 然后，我提示用户输入所有密码，因此我们不会将其存储在任何地方。 您可以考虑使用hosts文件来消除更新连接字符串的需要，但是我们选择不使用该方法。
[CmdletBinding()]
param (
[Parameter(Mandatory=$true)]
[string]
$ReportServerUri,
[string]
$range,
#Database settings
[Parameter(Mandatory=$true)]
[string]
$DBserver,
[Parameter(Mandatory=$true)]
[string]
$Database,
[Parameter(Mandatory=$true)]
[string]
$schema,
[Parameter(Mandatory=$true)]
[string]
$table
)
begin {
$moduleName = 'ReportingServicesTools'
Try{
if (Get-Module -ListAvailable -Name $moduleName){
Import-Module -Name $moduleName -ErrorAction SilentlyContinue
}
else{
Install-Module -Name $moduleName -ErrorAction SilentlyContinue -Scope CurrentUser
}
}
Catch{
throw "$($moduleName) module is not installed and could not be added"
}
}
process {
try{
Write-Verbose "Connect to the Web service"
$proxy = New-RsWebServiceProxy -ReportServerUri $reportServerUri
Write-Verbose "Open DB connection for data source reference info"
$ConnSELECT = New-Object System.Data.SQLClient.SQLConnection
$ConnSELECT.ConnectionString = "server='$DBserver';database='$Database';trusted_connection=true;"
$ConnSELECT.Open()
Write-Verbose "Define Query to pull data source settings from source table"
$sqlSELECT = "SELECT
[Path]
,[Enabled]
,[ConnectionString]
,[UserName]
,[CredentialRetrieval]
FROM $database.$schema.$sTable
WHERE Range = '$range'
AND Enabled = 'True'"
$Command = New-Object System.Data.SQLClient.SQLCommand($sqlSELECT, $ConnSELECT)
$references = @{}
$references = $Command.ExecuteReader()
}
catch{
throw (New-Object System.Exception("Error retrieving data from $($database).$($schema).$($table)! $($_.Exception.Message)", $_.Exception))
}
Write-Verbose "Loop through each data source row to update target SSRS Data Source"
foreach($row in $references) {
try {
$datasourcePath = $row.Item("Path")
Write-Verbose "Retrieve Definition for data source $datasourcePath"
$DataSourceDefinition = Get-RsDataSource -Proxy $proxy -Path $datasourcePath
if($row.Item("CredentialRetrieval") -eq 'Integrated'){
Write-Verbose "Updating Connection String"
$DataSourceDefinition.ConnectString = $row.Item("ConnectionString")
Set-RsDataSource -Proxy $proxy -RsItem $datasourcePath -DataSourceDefinition $DataSourceDefinition
Write-Verbose "Connection String Updated!"
}
elseif($row.Item("CredentialRetrieval") -eq 'Store'){
Write-Verbose "Updating Connection String and Stored Credentials"
$DataSourceDefinition.ConnectString = $row.Item("ConnectionString")
$DataSourceDefinition.UserName = $row.Item("UserName")
Set-RsDataSource -Proxy $proxy -RsItem $datasourcePath -DataSourceDefinition $DataSourceDefinition
Write-Verbose "Connection String Updated!"
$password = Read-Host "Password for $($row.Item('UserName'))"
Set-RsDataSourcePassword -Proxy $proxy -Path $datasourcePath -Password $password
Write-Verbose "Stored Credentials Updated!"
}
}
catch {
Write-Error "Error $_"
}
}
$ConnSELECT.Close()
}
end {
}
You can also find a full command list on the module’s GitHub page
您还可以在模块的GitHub页面上找到完整的命令列表
结论 (Conclusion)
I find that every method has its drawbacks and even though the backfill process I use works great to keep non-production environments up to date, it won’t account for all of the content that never makes it to production or any removal or edits of reports, folders etc that can happen in development environments. This is where I utilize a hybrid of dbatools and the Reporting Services module
我发现每种方法都有其缺点，尽管我使用的回填过程非常适合使非生产环境保持最新状态，但它并不能解决从未用于生产或对它进行任何删除或编辑的所有内容。报告，文件夹等在开发环境中可能发生。 在这里，我利用dbatools和Reporting Services模块的混合体
-
- Copy or Restore a Database backup using dbatools 使用dbatools复制或还原数据库备份
- Set the SSRS Database (to implement Security, subscriptions etc) 设置SSRS数据库（以实现安全性，订阅等）
.\Scripts\Restore-RSDatabase.ps1 -reportServer 'http://ReportingURL/Reportserver' -targetInstance 'DBSERVER\INSTANCE' -targetDatabase 'ReportServer' -backupLocation '\\Share\Backup\ReportServerFolder' -ssrsServer 'SSRSSERVER'
- *[Remember to set up the proxy connection first] * [请记住要先设置代理连接]
Remove-RSSubscriptionBulk -RSfolder '/' -Recurse -proxy $Proxy - Update Data sources from a reference table (As above) 从参考表更新数据源（如上所述）
Set-RSFolderSecurity -Proxy $proxy -RsFolder '/' -Action Inherit –RecurseReset-RSScaleOut -targetInstance 'DBSERVER\INSTANCE' -targetDatabase 'ReportServer' -ssrsServer 'SSRSSERVER'- Backup the encryption key (As Above) 备份加密密钥（如上所述）
Although there are a few steps in this method, I can manage each stage individually and the full process is done in under an hour with no step being performed manually which is the aim of the game! Depending on your environment you could even string these together into a single script for faster execution
尽管此方法有几个步骤，但我可以单独管理每个阶段，整个过程在一个小时内完成，而无需手动执行任何步骤，这是游戏的目标！ 根据您的环境，您甚至可以将这些字符串组合到一个脚本中，以加快执行速度
Here are the functions and scripts for the full process described above. This can also be found on my GitHub page
这是上述完整过程的功能和脚本。 也可以在我的GitHub页面上找到
Restore-RSDatabase (Restore-RSDatabase)
<#
.EXAMPLE
Restore-RSDatabase.ps1 -reportServer 'http://ReportingURL/Reportserver' -targetInstance 'DBSERVER\DBINSTANCE' -targetDatabase 'ReportServer' `
-backupLocation '\\Networkshare\ReportServer' -ssrsServer 'SSRS01' -Verbose
#>
[CmdLetBinding()]
param
(
[string]
$reportServer,
[string]
$targetInstance,
[string]
$targetDatabase,
[string]
$backupLocation,
[string]
$ssrsServer
)
$InformationPreference = 'Continue'
#-----------------------------------------------------------------
Write-Information "Migrate the DB using dbaTools"
#-----------------------------------------------------------------
try {
Write-Verbose "Restoring Database from $($backupLocation)"
Restore-DbaDatabase -SqlServer $targetInstance -Path $backupLocation -WithReplace
Write-Verbose "Database restored to $($targetDatabase) on $($targetInstance)"
}
catch {
throw (New-Object System.Exception("Error restoring from backup! $($_.Exception.Message)", $_.Exception))
}
#-----------------------------------------------------------------
Write-Information "Set RS Database"
#-----------------------------------------------------------------
try {
Write-Verbose "Get SQL Version for $($targetInstance)"
$SQLVersion = Get-DbaSqlInstanceProperty -SqlInstance $targetInstance | Where-Object{$_.Name -eq 'VersionMajor'}
Write-Verbose "Connecting to $($reportServer) default instance"
Connect-RsReportServer -ComputerName $ssrsServer -ReportServerInstance 'MSSQLSERVER' -ReportServerUri $reportServer
Write-Verbose "Setting up RS Database $($targetDatabase) Version:$($SQLVersion.Value) on $($targetInstance)"
Set-RsDatabase -DatabaseServerName $targetInstance -Name $targetDatabase -IsExistingDatabase -DatabaseCredentialType ServiceAccount -ReportServerVersion $SQLVersion.Value
}
catch {
throw (New-Object System.Exception("Error setting RS Database! $($_.Exception.Message)", $_.Exception))
}
#-----------------------------------------------------------------
Write-Information "Restore the Encryption Key"
#-----------------------------------------------------------------
try {
Write-Verbose "Restoring Encryption Key from Source"
$encKey = Read-Host "Please provide local encryption key for Database Source (E.g. C:\Encrypt_Key.snk)"
$encKeyPass = Read-Host "Please provide the Password for the Encryption Key"
Write-Debug "Encryption Key location: $($encKey)"
Restore-RSEncryptionKey -Password $encKeyPass -KeyPath $encKey -ReportServerVersion $SQLVersion.Value
Write-Information "Please remember to update the Scaled out servers in SSRS"
}
catch{
throw (New-Object System.Exception("Error restoring Encryption Key! $($_.Exception.Message)", $_.Exception))
}
$InformationPreference = 'Silently Continue'
Set-RSFolderSecurity (Set-RSFolderSecurity)
<#
.SYNOPSIS
This will set the security on a specified folder. This can be adding or removing a user, or setting to inherit from parent
.DESCRIPTION
Sets security on a specified folder. Use of the Action variable will allow the addition or removal of a specified user or group. Using 'Inherit' will return the folder security
to that of the parent folder.
.PARAMETER RsFolder
Target folder. This should be preceded by a /. Eg. '/Sales Reports'. It is possible to set the Root folder using '/'
.PARAMETER Identity
This is the username of the user or group to be added or removed. This should include a domain prefix where relevant
.PARAMETER Role
The chosen Role for the addition of a new user. This should be one of the set roles within the target instance
.PARAMETER Action
This should be one of 3 options. ADD a provided user & role. REMOVE a provided user. Set the folder to Inherit from Parent
2
.PARAMETER Recurse
Flag to determine if all subfolders should be included or only the target folder
.PARAMETER proxy
Proxy object provided by using the New-RsWebServiceProxy command. Eg. New-RsWebServiceProxy -ReportServerUri 'http://ReportServerURL/ReportServer/ReportService2010.asmx?wsdl'
.EXAMPLE
Set-RSFolderSecurity -Proxy $proxy -RsFolder '/' -Action Add -Identity 'DOMAIN\User' -Role 'Content Manager'
This will grant DOMAIN\User Content Manager permissions on the root folder Only.
.NOTES
NOTE: The user executing this function will need the permissions within SSRS to perform these security changes
#>
function Set-RSFolderSecurity {
[CmdletBinding(SupportsShouldProcess = $true, ConfirmImpact = 'Medium')]
param (
[Alias('ItemPath', 'Path')]
[Parameter(Mandatory = $True)]
[string]
$RsFolder,
[Alias('GroupUserName')]
[string]
$Identity,
[Alias('RoleName')]
[string]
$Role,
[ValidateSet('Add','Remove','Inherit')]
[string]
$Action,
[switch]
$Recurse,
$proxy
)
begin {
If($Identity -eq $null -and (($Action -eq 'Add' -and $role -eq $null) -or ($Action -eq 'Remove'))){
throw (New-Object System.Exception("Missing Parameters!"))
}
}
process {
$folders = @()
try{
if($Recurse){
Write-Verbose "List out all sub-folders under the $($RsFolder) directory"
$RSFolders = Get-RsFolderContent -Proxy $proxy -Path $RSFolder -Recurse | Where-Object{$_.TypeName -eq 'Folder'}
$folders = $RSFolders.Path
}
Write-Verbose "Adding Folder Parameter to Array"
$folders += $RsFolder
foreach($folder in $folders){
If($Action -eq 'Add'){
Write-Verbose "Granting $($Identity) $($role) permissions on $($folder)"
if ($pscmdlet.ShouldProcess($folder, "Granting $($Identity) $($role) permissions on $($folder)")) {
Grant-RsCatalogItemRole -Proxy $proxy -Identity $Identity -RoleName $Role -Path $folder
}
}
Elseif($Action -eq 'Remove'){
Write-Verbose "Removing $($Identity) permissions from $($folder)"
if ($pscmdlet.ShouldProcess($folder, "Remove $($Identity) permissions from $($folder)")) {
Revoke-RsCatalogItemAccess -Proxy $proxy -Identity $Identity -Path $folder
}
}
ElseIf($Action -eq 'Inherit'){
$InheritParent = $true
Write-Verbose "Setting $($folder) to Inherit Parent Security"
$Proxy.GetPolicies($folder, [ref]$InheritParent)
if(-not $InheritParent -and $folder -ne '/') #Cant revert perms on Root folder
{
if ($pscmdlet.ShouldProcess($folder, "Set $($folder) to Inherit from Parent")) {
$Proxy.InheritParentSecurity($folder)
}
}
}
Else{
throw (New-Object System.Exception("No Valid Action provided! Use Add | Remove | Inherit"))
}
}
}
catch{
throw (New-Object System.Exception("Error Updating Permissions! $($_.Exception.Message)", $_.Exception))
}
}
end {
}
}
重置-RSScaleOut (Reset-RSScaleOut)
<#
.SYNOPSIS
Removes all but the specified servers from Reporting Services Scale Out deployment
.DESCRIPTION
Using dbatools Invoke-DbaSqlCmd command removes all servers except the specified servers from the dbo.keys table in the SSRS database.
This removes them from the Scale-Out deployment. This is intended to be used in Migration situations
.PARAMETER targetInstance
This is the target database instance. For default instances, you do not need to specify the instance. Eg. DBSERVER Eg. DBSERVER\INSTANCE2
.PARAMETER targetDatabase
This is the target Database for the Report Server. This defaults to ReportServer if no DB is provided
.PARAMETER ssrsServer
This is the machine name for the target servers. These can be passed as an array or a single machine
.EXAMPLE
Reset-RSScaleOut -targetInstance 'DBSERVER' -targetDatabase ReportServerClient1 -ssrsServer 'SSRS01'
This will remove all other machines from the Scale-Out, leaving only SSRS01
.NOTES
General notes
#>
function Reset-RSScaleOut {
[CmdletBinding(SupportsShouldProcess = $true, ConfirmImpact = 'Medium')]
param (
[string]
$targetInstance,
[string]
$targetDatabase = 'ReportServer',
[string[]]
$ssrsServer
)
begin {
$serverlist = $null
foreach($server in $ssrsServer){
$serverlist += "'$($server)',"
}
$serverlist = $serverlist.TrimEnd(",")
}
process {
try {
Write-Verbose "Delete all Scale Out Keys not attributed to servers: $($serverlist)"
$command = "DELETE FROM [$($targetDatabase)].[dbo].[Keys] WHERE MachineName NOT IN ($($serverlist))"
Write-Verbose "Command: $($command)"
if ($pscmdlet.ShouldProcess($command, "Delete Machines from Scale Out deployment (dbo.Keys table)")) {
Invoke-DbaSqlCmd -SqlInstance $targetInstance -Query $command
}
Invoke-DbaSqlCmd -SqlInstance $targetInstance -Query $command
}
catch {
throw (New-Object System.Exception("Error Cleaning Keys Table! $($_.Exception.Message)", $_.Exception))
}
}
end {
}
}
删除RS订阅 (Remove-RSSubscription)
<#
.SYNOPSIS
Removes all subscriptions from a provided folder
.DESCRIPTION
This removes all subscriptions in a provided folder with a Recurse flag to include all subfolders.
.PARAMETER RsFolder
Target folder. This should be preceded by a /. Eg. '/Sales Reports'. It is possible to set the Root folder using '/'
.PARAMETER Recurse
Flag to determine if all subfolders should be included or only the target folder
.PARAMETER Proxy
Proxy object provided by using the New-RsWebServiceProxy command. Eg. New-RsWebServiceProxy -ReportServerUri 'http://ReportServerURL/ReportServer/ReportService2010.asmx?wsdl'
.EXAMPLE
Remove-RSSubscriptionBulk -RSfolder '/' -Recurse -proxy $Proxy
This will remove all subscriptions in an entire instance
.EXAMPLE
Remove-RSSubscriptionBulk -RSfolder '/Sales Reports' -proxy $Proxy -Confirm
This will remove all subscriptions in the Sales Reports folder only. It will not affect subfolders. It will also prompt before each subscription deletion
.NOTES
General notes
#>
function Remove-RSSubscriptionBulk{
[CmdletBinding(SupportsShouldProcess = $true, ConfirmImpact = 'Medium')]
param (
[Alias('ItemPath', 'Path')]
[Parameter(Mandatory = $True)]
[string]
$RsFolder,
[switch]
$Recurse,
$Proxy
)
begin {
#$Proxy = New-RsWebServiceProxyHelper -BoundParameters $PSBoundParameters
}
process {
try {
if($Recurse){
Write-Verbose "Recurse flag set. Return all subscriptions in Folder:$($RsFolder) and sub-folders"
$subs = $Proxy.ListSubscriptions($RsFolder)
}
else{
Write-Verbose "Recurse flag not set. Return all subscriptions in Folder:$($RsFolder) only"
$subs = $Proxy.ListSubscriptions($RSFolder) | Where-Object {$_.Path -eq "$($RsFolder)/$($_.Report)"}
}
}
catch {
throw (New-Object System.Exception("Failed to retrieve items in '$RsFolder': $($_.Exception.Message)", $_.Exception))
}
try {
Write-Verbose "$($subs.Count) Subscriptions will be deleted."
foreach($sub in $subs){
if ($pscmdlet.ShouldProcess($sub.Path, "Delete Subscription with ID: $($sub.SubscriptionID)")) {
$Proxy.DeleteSubscription($sub.SubscriptionID)
}
Write-Verbose "Subscription Deleted: $($sub.SubscriptionID)"
}
}
catch {
throw (New-Object System.Exception("Failed to delete items in '$RsFolder': $($_.Exception.Message)", $_.Exception))
}
}
end {
}
}
参考资料 (References)
- The Reporting Services Scripter Reporting Services脚本编写器
- WebArchive of Original RSScripter 原始RSScripter的WebArchive
- Reporting Services Migration Tool method Reporting Services迁移工具方法
- RS.Exe Sample Script method RS.Exe示例脚本方法
- RS.Exe Sample Script CodePlex Download page RS.Exe示例脚本CodePlex下载页面
- Dbatools dbatools
- ReportingServicesTools GitHub ReportingServicesTools GitHub
- SSRS Admin GitHub SSRS管理员GitHub
附录 (Appendix)
SSRS Data Source Backup PowerShell Script SSRS数据源备份PowerShell脚本[CmdletBinding()]
param
(
[Parameter(Mandatory=$true)]
[string]
$ReportServerUri,
[Parameter(Mandatory=$true)]
[string]
$DataSourceFolder,
[string]
$range,
#Database settings
[Parameter(Mandatory=$true)]
[string]
$DBserver,
[Parameter(Mandatory=$true)]
[string]
$Database,
[Parameter(Mandatory=$true)]
[string]
$schema,
[Parameter(Mandatory=$true)]
[string]
$table
)
begin{
$moduleName = 'ReportingServicesTools'
Try{
if (Get-Module -ListAvailable -Name $moduleName){
Import-Module -Name $moduleName -ErrorAction SilentlyContinue
}
else{
Install-Module -Name $moduleName -ErrorAction SilentlyContinue -Scope CurrentUser
}
}
Catch
{
throw "$($moduleName) module is not installed and could not be added"
}
if(!$DataSourceFolder.StartsWith('/')){
Write-Verbose "Data Source Folder did not starts with /. Updating!"
$DataSourceFolder = "/$($DataSourceFolder)"
}
if($range -eq $null){
$range = 'NOT DEFINED'
}
}
process{
try{
$proxy = New-RsWebServiceProxy -ReportServerUri $reportServerUri
$DSContents = $Proxy.ListChildren($DataSourceFolder,$false) | Where-Object{$_.TypeName -eq 'DataSource'}
$SSRSDataSourceElements = @()
foreach($SSRSDataSourceElement in $DSContents)
{
$TempSSRSMeta = $Proxy.GetDataSourceContents($SSRSDataSourceElement.Path)
$SSRSDataSourceElements += New-Object PSObject -Property @{
'range' = $range
'UserName' = $TempSSRSMeta.UserName;
'Enabled' = $TempSSRSMeta.Enabled;
'WindowsCredentials' = $TempSSRSMeta.WindowsCredentials;
'CredentialRetrieval' = $TempSSRSMeta.CredentialRetrieval;
'Name' = $SSRSDataSourceElement.Name;
'Path' = $SSRSDataSourceElement.Path;
'Description' = $SSRSDataSourceElement.Description;
'ConnectionString' = $TempSSRSMeta.ConnectString;
'Extension' = $TempSSRSMeta.Extension;
'DateChecked' = Get-Date -Format s;
}
}
}
catch {
throw (New-Object System.Exception("Error adding data sources to Array! $($_.Exception.Message)", $_.Exception))
}
#-----------------------------------------------------------------------
# Connect to SQL table and insert the new contents
#-----------------------------------------------------------------------
try{
Write-Verbose "Open connection to Database server"
$ConnINSERT = New-Object System.Data.SQLClient.SQLConnection
$ConnINSERT.ConnectionString = "server='$DBserver'; Database='$Database';Trusted_Connection=true;"
$ConnINSERT.Open()
#-------------------------------------------------------------------------------
Write-Verbose "Create destination table if it does not exist"
$sqlCREATE = "IF NOT EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'$($schema).$($table)'))
CREATE TABLE $($schema).$($table)(
[Range] [NVARCHAR](50) NULL,
[Name] [NVARCHAR](50) NOT NULL,
[Path] [NVARCHAR](200) NOT NULL,
[Enabled] [NVARCHAR](5) NOT NULL,
[Description] [NVARCHAR](200) NULL,
[ConnectionString] [NVARCHAR](200) NULL,
[Extension] [NVARCHAR](50) NULL,
[UserName] [NVARCHAR](50) NOT NULL,
[Windows Credentials] [NVARCHAR](5) NOT NULL,
[CredentialRetrieval] [NVARCHAR](30) NOT NULL,
[DateChecked] [DATETIME2](7) NULL
)"
$Command = New-Object System.Data.SQLClient.SQLCommand($sqlCREATE, $ConnINSERT)
Write-Debug $Command.CommandText
$numROWS = $Command.ExecuteNonQuery()
#-------------------------------------------------------------------------------
}
catch{
throw (New-Object System.Exception("Error creating Table in $($Database)! $($_.Exception.Message)", $_.Exception))
}
try{
foreach($Field in $SSRSDataSourceElements)
{
#Escape characters in expected fields
if($($Field.Description)){$Field.Description = $Field.Description.replace("'","''")}
$Field.ConnectionString = $Field.ConnectionString.replace("'","''")
$sqlINSERT = "IF NOT EXISTS (SELECT 1 FROM $database.$schema.$table WHERE Name = '$($Field.Name)' AND Path = '$($Field.Path)' AND Range = '$($range)')
BEGIN
INSERT INTO $database.$schema.$table
([Range]
,[Name]
,[Path]
,[Enabled]
,[Description]
,[ConnectionString]
,[Extension]
,[UserName]
,[Windows Credentials]
,[CredentialRetrieval]
,[DateChecked])
VALUES
('$($range)'
,'$($Field.Name)'
,'$($Field.Path)'
,'$($Field.Enabled)'
,'$($Field.Description)'
,'$($Field.ConnectionString)'
,'$($Field.Extension)'
,'$($Field.UserName)'
,'$($Field.WindowsCredentials)'
,'$($Field.CredentialRetrieval)'
,'$($Field.DateChecked)')
END
ELSE IF EXISTS (SELECT 1 FROM $database.$schema.$table WHERE Name = '$($Field.Name)' AND Path = '$($Field.Path)' AND Range = '$($range)')
BEGIN
UPDATE $database.$schema.$table
SET
[Enabled] = '$($Field.Enabled)',
[Description] = '$($Field.Description)',
[ConnectionString] = '$($Field.ConnectionString)',
[Extension] = '$($Field.Extension)',
[UserName] = '$($Field.UserName)',
[Windows Credentials] = '$($Field.WindowsCredentials)',
[CredentialRetrieval] = '$($Field.CredentialRetrieval)',
[DateChecked] = '$($Field.DateChecked)'
WHERE Name = '$($Field.Name)' AND Path = '$($Field.Path)' AND Range = '$($range)'
END"
$Command = New-Object System.Data.SQLClient.SQLCommand($sqlINSERT, $ConnINSERT)
$numROWS = $Command.ExecuteNonQuery()
}
$Command.Dispose()
$ConnINSERT.Close()
$ConnINSERT.Dispose()
}
catch {
#Data Backup failure
throw (New-Object System.Exception("Error adding data to $($database).$($schema).$($table)! $($_.Exception.Message)", $_.Exception))
}
}
翻译自: https://www.sqlshack.com/migrating-ssrs-content-powershell/
扫一扫
240
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
