CODEWITHSUNDEEP
sqlsql-servert-sqlssrs-2008sql-server-2012
Alex Gordon
Alex Gordon

Reputation: 60892

how to rebuild / reinstall ssrs (reportserver, reportservertempdb) databases?

Our server crashed. We got it back up and running however, the mentioned databases have been corrupted.

Is there a programmatic / automatic way of rebuilding or reinstalling the SSRS databases?

If not:

  1. Since we can still select * from the databases, how do I grab the permissions and roles on different reports?
  2. How do we retrieve subscription information?

Upvotes: 3

Views: 5806

Answers (1)

Sam
Sam

Reputation: 7678

1.

You can automate the initial install of SQL by using a .ini file with the setup.exe. After that point, you should be backing up your databases. But it seems like perhaps you haven't.

If you still want to rebuild the databases, you can drop and then use the SSRS config tool to recreate them.

If you'd like to automate that, you'll need to script a db drop, create, role creation (RSExecRole) and then run the Catalog.sql and CatalogTempDB.sql scripts located here, however I don't think you should be doing this, just backups - way easier!

X:\Program Files\Microsoft SQL Server\MSRS10.X\Reporting Services\ReportServer

2.

I'd use RSScripter to move objects which needs the web service to be running. It seems like you only have access to the database, which makes your job much more difficult. You'll need to figure out what tables hold subscription data and move that data over to your new database. You'll also need to translate the old report ID to the new report ID, since they will have changed in the new install - this can be accomplished by matching on the report name, if they are unique. If possible I'd just try to rebuild them by hand.

Here is a query I use to look at subscriptions, maybe it will help...

USE ReportServer
go


WITH
[Sub_Parameters] AS
(
SELECT 
[SubscriptionID],
[Parameters] = CONVERT(XML,a.[Parameters])
FROM [Subscriptions] a
),
[MySubscriptions] AS
(
SELECT DISTINCT
[SubscriptionID],
[ParameterName] = QUOTENAME(p.value('(Name)[1]', 'nvarchar(max)')),
[ParameterValue] = p.value('(Value)[1]', 'nvarchar(max)')
FROM 
[Sub_Parameters] a
CROSS APPLY [Parameters].nodes('/ParameterValues/ParameterValue') t(p)
),
[SubscriptionsAnalysis] AS
(
SELECT
a.[SubscriptionID],
a.[ParameterName],
[ParameterValue] = 
(SELECT
STUFF(( 
SELECT [ParameterValue] + ', ' as [text()]
FROM [MySubscriptions]
WHERE 
[SubscriptionID] = a.[SubscriptionID]
AND [ParameterName] = a.[ParameterName]
FOR XML PATH('')
),1, 0, '')
+'')
FROM [MySubscriptions] a
GROUP BY a.[SubscriptionID],a.[ParameterName]
)
SELECT
a.[SubscriptionID],
c.[UserName] AS Owner, 
b.Name,
b.Path,
a.[Locale], 
a.[InactiveFlags], 
d.[UserName] AS Modified_by, 
a.[ModifiedDate], 
a.[Description], 
a.[LastStatus], 
a.[EventType], 
a.[LastRunTime], 
a.[DeliveryExtension],
a.[Version],
e.[ParameterName],
LEFT(e.[ParameterValue],LEN(e.[ParameterValue])-1) as [ParameterValue],
SUBSTRING(b.PATH,2,LEN(b.PATH)-(CHARINDEX('/',REVERSE(b.PATH))+1)) AS ProjectName
FROM 
[Subscriptions] a 
INNER JOIN [Catalog] AS b
ON a.[Report_OID] = b.[ItemID]
LEFT OUTER JOIN [Users] AS c
ON a.[OwnerID] = c.[UserID]
LEFT OUTER JOIN [Users] AS d
ON a.MODIFIEDBYID = d.Userid
LEFT OUTER JOIN [SubscriptionsAnalysis] AS e 
ON a.SubscriptionID = e.SubscriptionID

ORDER BY projectname

Upvotes: 1

Related Questions