Had a task to move the default SQL Reporting Services ReportServer and ReportServerTempDB databases to a new SQL cluster which required a rename to avoid any potential conflicts.<\/p>\n
These instance of the databases are being used in Sharepoint-Integrated Mode. You could do Step 7 below before you do Steps 4 – 6.<\/p>\n
1. Stop old SSRS<\/p>\n
2. Detach ReportServer, ReportServerTempDB<\/p>\n
3. Rename the MDF\/LDF files; in this case ReportServer_MySite, ReportServer_MySiteTempDB<\/p>\n
4. Copy\/Attach these databases into new SQL server.<\/p>\n
5. In my case, SSRS is Sharepoint-Integrated. In Central Admin; modify your Reporting Services integration as applicable.<\/p>\n
6. Make sure new SSRS Configuration Manager is pointing to new database names.<\/p>\n
7. Drop\/Create script all Stored Procedures and the ExtendedCatalog Function and the ExtendedDatasource and Extended DataSet views.<\/p>\n
Now for happy fun-time.<\/p>\n
If it’s not enabled already; enable xp_cmdshell<\/p>\n
EXECUTE SP_CONFIGURE ‘show advanced options’, 1
\nRECONFIGURE WITH OVERRIDE
\nGO<\/p>\n
EXECUTE SP_CONFIGURE ‘xp_cmdshell’, ‘1’
\nRECONFIGURE WITH OVERRIDE
\nGO<\/p>\n
EXECUTE SP_CONFIGURE ‘show advanced options’, 0
\nRECONFIGURE WITH OVERRIDE
\nGO<\/p>\n
Execute the following Stored Procedure “FindandReplace”; this will find all references to your old ReportServerTempDB in the existing Stored Procedures in ReportServer.<\/p>\n
USE [ReportServer_MySite]
\nGO<\/p>\n
\/****** Object: StoredProcedure [dbo].[FindAndReplace] Script Date: 06\/12\/2012 14:00:02 ******\/
\nSET ANSI_NULLS ON
\nGO<\/p>\n
SET QUOTED_IDENTIFIER ON
\nGO
\nCREATE procedure [dbo].[FindAndReplace]
\n(
\n@filepath varchar(500) =’c:temp’, — output filepath
\n@myfind varchar(500) = ‘ReportServerTempDB’, — keyword to be found
\n@myreplace varchar(500) = ‘ReportServer_MySiteTempDB’ — replacement word
\n)
\nas
\nset nocount on
\ndeclare @cmd varchar(8000), @myobject_id int, @myproc varchar(100), @checksum0 int, @checksum1 int<\/p>\n
\/*
\nProcedure to identify any stored procedures, in a given database, with a given keyword
\nBCPs an “alter” script to modify the proc with the replacement word
\nAlso BCPs generates a “backup” script
\nCompatible with SQL2005 only
\nLouis Nguyen 2008
\n*\/<\/p>\n
— BCP requires permanent tables
\ncreate table temptable0 (definition nvarchar(max))
\ncreate table temptable1 (definition nvarchar(max))<\/p>\n
— init filepath
\nif right(@filepath,1) <> ”
\nselect @filepath = @filepath + ”<\/p>\n
— cursor over procedures
\ndeclare mycur cursor fast_forward for
\nselect b.object_id, a.name
\nfrom sys.objects a
\njoin sys.sql_modules b
\non a.type=’p’ and a.object_id = b.object_id
\nwhere a.name <> ‘FindAndReplace’
\nopen mycur
\nfetch next from mycur into @myobject_id, @myproc<\/p>\n
— begin cursor
\nwhile @@fetch_status = 0 begin<\/p>\n
— write to permanent table(s)
\ndelete temptable0
\ninsert temptable0(definition)
\nselect definition from sys.sql_modules
\nwhere object_id= @myobject_id<\/p>\n
delete temptable1
\ninsert temptable1(definition)
\nselect replace(definition,@myfind,@myreplace) from sys.sql_modules
\nwhere object_id= @myobject_id<\/p>\n
— compare checksums
\nselect @checksum0=checksum(definition) from temptable0
\nselect @checksum1=checksum(definition) from temptable1<\/p>\n
if @checksum0<>@checksum1 begin<\/p>\n
print ‘!! MATCH FOUND: ‘ + @myproc<\/p>\n
— bcp out unadulterated contents
\nselect @cmd=’bcp “select definition from ‘+db_name()+’..temptable0” queryout “‘
\n+@filepath
\n+replace(@@servername,”,’_’) +’_’ +db_name()
\n+’____’+@myproc + ‘.sql’
\n+’.backup”‘
\n+’ -S’+@@servername
\n+’ -T -c’
\nexec master.dbo.xp_cmdshell @cmd, no_output<\/p>\n
— add use database & drop procedure to beginning of file
\ndelete temptable1
\ninsert temptable1
\nselect ‘use [‘+db_name()+’]’
\nunion all
\nselect ‘go’
\nunion all
\nselect ‘drop procedure [‘+@myproc +’]’
\nunion all
\nselect ‘go’
\nunion all
\nselect replace(definition,@myfind,@myreplace) from temptable0<\/p>\n
— bcp out modified contents
\nselect @cmd=’bcp “select definition from ‘+db_name()+’..temptable1” queryout “‘
\n+@filepath
\n+replace(@@servername,”,’_’) +’_’ +db_name()
\n+’____’+@myproc + ‘.sql’
\n+'”‘
\n+’ -S’+@@servername
\n+’ -T -c’
\nexec master.dbo.xp_cmdshell @cmd, no_output<\/p>\n
end
\nelse begin
\nprint ‘No match found: ‘ + @myproc
\nend<\/p>\n
— end cursor
\nfetch next from mycur into @myobject_id, @myproc
\nend
\nclose mycur
\ndeallocate mycur<\/p>\n
— cleanup
\ndrop table temptable0
\ndrop table temptable1<\/p>\n
GO<\/p>\n
Once the SP is created; Execute it.<\/p>\n
You will be prompted for a temporary path, eg. C:temp and your string to look for: “ReportServerTempDB” and the value you want to change it to: “ReportServer_MySiteTempDB”<\/p>\n
This will backup existing SPs and create new SPs scripts with the new value.<\/p>\n
From C:temp call all the new scripts into SQL Management Studio and execute.<\/p>\n
You will manually need to modify the Function and View mentioned above.<\/p>\n","protected":false},"excerpt":{"rendered":"
Had a task to move the default SQL Reporting Services ReportServer and ReportServerTempDB databases to a new SQL cluster which required a rename to avoid any potential conflicts. These instance of the databases are being used in Sharepoint-Integrated Mode. You could do Step 7 below before you do Steps 4 […]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"footnotes":""},"categories":[2,11],"tags":[105,106,111],"class_list":["post-163","post","type-post","status-publish","format-standard","hentry","category-computer","category-software","tag-sql-2008-r2","tag-sql-reporting-services","tag-ssrs"],"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/www.markwheeler.com\/index.php?rest_route=\/wp\/v2\/posts\/163","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.markwheeler.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.markwheeler.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.markwheeler.com\/index.php?rest_route=\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/www.markwheeler.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=163"}],"version-history":[{"count":0,"href":"https:\/\/www.markwheeler.com\/index.php?rest_route=\/wp\/v2\/posts\/163\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.markwheeler.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=163"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.markwheeler.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=163"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.markwheeler.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=163"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}