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.
