Jun 132012
 
Share

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 – 6.

1. Stop old SSRS

2. Detach ReportServer, ReportServerTempDB

3. Rename the MDF/LDF files; in this case ReportServer_MySite, ReportServer_MySiteTempDB

4. Copy/Attach these databases into new SQL server.

5. In my case, SSRS is Sharepoint-Integrated. In Central Admin; modify your Reporting Services integration as applicable.

6. Make sure new SSRS Configuration Manager is pointing to new database names.

7. Drop/Create script all Stored Procedures and the ExtendedCatalog Function and the ExtendedDatasource and Extended DataSet views.

Now for happy fun-time.

If it’s not enabled already; enable xp_cmdshell

EXECUTE SP_CONFIGURE ‘show advanced options’, 1
RECONFIGURE WITH OVERRIDE
GO

EXECUTE SP_CONFIGURE ‘xp_cmdshell’, ‘1’
RECONFIGURE WITH OVERRIDE
GO

EXECUTE SP_CONFIGURE ‘show advanced options’, 0
RECONFIGURE WITH OVERRIDE
GO

Execute the following Stored Procedure “FindandReplace”; this will find all references to your old ReportServerTempDB in the existing Stored Procedures in ReportServer.

USE [ReportServer_MySite]
GO

/****** Object: StoredProcedure [dbo].[FindAndReplace] Script Date: 06/12/2012 14:00:02 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[FindAndReplace]
(
@filepath varchar(500) =’c:temp’, — output filepath
@myfind varchar(500) = ‘ReportServerTempDB’, — keyword to be found
@myreplace varchar(500) = ‘ReportServer_MySiteTempDB’ — replacement word
)
as
set nocount on
declare @cmd varchar(8000), @myobject_id int, @myproc varchar(100), @checksum0 int, @checksum1 int

/*
Procedure to identify any stored procedures, in a given database, with a given keyword
BCPs an “alter” script to modify the proc with the replacement word
Also BCPs generates a “backup” script
Compatible with SQL2005 only
Louis Nguyen 2008
*/

— BCP requires permanent tables
create table temptable0 (definition nvarchar(max))
create table temptable1 (definition nvarchar(max))

— init filepath
if right(@filepath,1) <> ”
select @filepath = @filepath + ”

— cursor over procedures
declare mycur cursor fast_forward for
select b.object_id, a.name
from sys.objects a
join sys.sql_modules b
on a.type=’p’ and a.object_id = b.object_id
where a.name <> ‘FindAndReplace’
open mycur
fetch next from mycur into @myobject_id, @myproc

— begin cursor
while @@fetch_status = 0 begin

— write to permanent table(s)
delete temptable0
insert temptable0(definition)
select definition from sys.sql_modules
where object_id= @myobject_id

delete temptable1
insert temptable1(definition)
select replace(definition,@myfind,@myreplace) from sys.sql_modules
where object_id= @myobject_id

— compare checksums
select @checksum0=checksum(definition) from temptable0
select @checksum1=checksum(definition) from temptable1

if @checksum0<>@checksum1 begin

print ‘!! MATCH FOUND: ‘ + @myproc

— bcp out unadulterated contents
select @cmd=’bcp “select definition from ‘+db_name()+’..temptable0” queryout “‘
+@filepath
+replace(@@servername,”,’_’) +’_’ +db_name()
+’____’+@myproc + ‘.sql’
+’.backup”‘
+’ -S’+@@servername
+’ -T -c’
exec master.dbo.xp_cmdshell @cmd, no_output

— add use database & drop procedure to beginning of file
delete temptable1
insert temptable1
select ‘use [‘+db_name()+’]’
union all
select ‘go’
union all
select ‘drop procedure [‘+@myproc +’]’
union all
select ‘go’
union all
select replace(definition,@myfind,@myreplace) from temptable0

— bcp out modified contents
select @cmd=’bcp “select definition from ‘+db_name()+’..temptable1” queryout “‘
+@filepath
+replace(@@servername,”,’_’) +’_’ +db_name()
+’____’+@myproc + ‘.sql’
+'”‘
+’ -S’+@@servername
+’ -T -c’
exec master.dbo.xp_cmdshell @cmd, no_output

end
else begin
print ‘No match found: ‘ + @myproc
end

— end cursor
fetch next from mycur into @myobject_id, @myproc
end
close mycur
deallocate mycur

— cleanup
drop table temptable0
drop table temptable1

GO

Once the SP is created; Execute it.

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”

This will backup existing SPs and create new SPs scripts with the new value.

From C:temp call all the new scripts into SQL Management Studio and execute.

You will manually need to modify the Function and View mentioned above.