Re-run TIM after Live to Test Refresh?

David - there's new SQL stuff you have to do in a v11 copy from live to test. I think it is in the v10 to v11 copy documentation and I swear I saw it somewhere else... But this is what I've been using just after the securecheck step.

Hope this makes sense. If I can find the official doc I'll put a link later!

 

 

use impresario

GO

 

declare @schema_name varchar(100)

select@schema_name = 'SqlDependencySchema'

 

declare @objects table (

id int identity(1,1) not null,

name varchar(255) not null,

obj_id int not null,

xtype varchar(10) not null)

 

insert@objects

selectb.name, b.id, b.xtype

fromsys.schemas a

join sysobjects b on a.schema_id = b.uid

wherea.name = @schema_name

and b.name like 'SqlQueryNotification%'

 

declare@sql varchar(255)

declare@obj_name varchar(255)

declare@obj_id int

declare@obj_type varchar(10)

declare@svc_name varchar(255)

declare@ct int

select@ct = 1

 

while@ct <= (select MAX(id) from @objects)

  begin

select@obj_name = name,

@obj_id = obj_id,

@obj_type = xtype

from@objects

whereid = @ct

if @obj_type = 'SQ'

 begin

select@svc_name = name

fromsys.services

whereservice_queue_id = @obj_id

if coalesce(@svc_name,'') <> ''

 begin

select@sql = 'IF  EXISTS (SELECT * FROM sys.services WHERE name = ''' + @svc_name + ''')'

select@sql = @sql + ' BEGIN '

select@sql = @sql + 'DROP SERVICE [' + @svc_name + ']'

select@sql = @sql + ' END'

exec(@sql)

 end

if coalesce(@obj_name,'') <> ''

 begin

select@sql = 'DROP QUEUE [' + @schema_name + '].[' + @obj_name + ']'

exec(@sql)

 end

 end

if @obj_type = 'P'

 begin

if coalesce(@obj_name,'') <> ''

 begin

select@sql = 'DROP PROCEDURE [' + @schema_name + '].[' + @obj_name + ']'

exec(@sql)

 end

 end

select@ct = @ct + 1

  end

GO

 

-- Drop Services User

 

USE [impresario]

GO

/****** Object:  Schema [tessApi]    Script Date: 12/18/2011 21:38:09 ******/

IF  EXISTS (SELECT * FROM sys.schemas WHERE name = N'tessApi')

DROP SCHEMA [tessApi]

GO

/****** Object:  Schema [SqlDependencySchema]    Script Date: 12/18/2011 21:38:21 ******/

IF  EXISTS (SELECT * FROM sys.schemas WHERE name = N'SqlDependencySchema')

DROP SCHEMA [SqlDependencySchema]

GO

/****** Object:  User [tessApi]    Script Date: 12/18/2011 21:38:43 ******/

IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'tessApi')

DROP USER [tessApi]

GO

USE [impresario_cci]

GO

/****** Object:  Schema [tessApi]    Script Date: 12/18/2011 21:38:09 ******/

IF  EXISTS (SELECT * FROM sys.schemas WHERE name = N'tessApi')

DROP SCHEMA [tessApi]

GO

/****** Object:  Schema [SqlDependencySchema]    Script Date: 12/18/2011 21:38:21 ******/

IF  EXISTS (SELECT * FROM sys.schemas WHERE name = N'SqlDependencySchema')

DROP SCHEMA [SqlDependencySchema]

GO

/****** Object:  User [tessApi]    Script Date: 12/18/2011 21:38:43 ******/

IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'tessApi')

DROP USER [tessApi]

GO

--/****** Object:  Login [tessApi]    Script Date: 12/18/2011 21:40:07 ******/

--IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'tessApi')

--DROP LOGIN [tessApi]

--GO

 

-- Create Services User

 

USE IMPRESARIO

GO

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'tessApi')

  begin

      CREATE USER [tessApi] FOR LOGIN [tessApi] 

      WITH DEFAULT_SCHEMA = [SqlDependencySchema]

  end

GO

ALTER USER [tessApi] WITH DEFAULT_SCHEMA=[SqlDependencySchema]

GO

IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'SqlDependencySchema')

  begin

      DROP SCHEMA [SqlDependencySchema]

  end

GO

CREATE SCHEMA [SqlDependencySchema] AUTHORIZATION [tessApi]

GO

EXEC sp_addrolemember 'tessitura_sql_dependency_role', 'tessApi'

EXEC sp_addrolemember 'impusers', 'tessApi'

GO

USE impresario_cci

GO

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'tessApi')

  begin

      CREATE USER [tessApi] FOR LOGIN [tessApi] 

      WITH DEFAULT_SCHEMA = [SqlDependencySchema]

  end

GO

ALTER USER [tessApi] WITH DEFAULT_SCHEMA=[SqlDependencySchema]

GO

IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'SqlDependencySchema')

  begin

      DROP SCHEMA [SqlDependencySchema]

  end

GO

CREATE SCHEMA [SqlDependencySchema] AUTHORIZATION [tessApi]

GO

EXEC sp_addrolemember 'tessitura_sql_dependency_role', 'tessApi'

EXEC sp_addrolemember 'impusers', 'tessApi'

GO

 

-- Enable Service Broker

 

declare     @query nvarchar(255)

If NOT EXISTS (select * From sys.databases Where is_broker_enabled = 1 and name = 'impresario')

  Begin

      SET @query = N'ALTER DATABASE impresario SET ENABLE_BROKER with rollback immediate' 

      Exec sp_executesql @query

  End

GO

 

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

David - there's new SQL stuff you have to do in a v11 copy from live to test. I think it is in the v10 to v11 copy documentation and I swear I saw it somewhere else... But this is what I've been using just after the securecheck step.

Hope this makes sense. If I can find the official doc I'll put a link later!

 

 

use impresario

GO

 

declare @schema_name varchar(100)

select@schema_name = 'SqlDependencySchema'

 

declare @objects table (

id int identity(1,1) not null,

name varchar(255) not null,

obj_id int not null,

xtype varchar(10) not null)

 

insert@objects

selectb.name, b.id, b.xtype

fromsys.schemas a

join sysobjects b on a.schema_id = b.uid

wherea.name = @schema_name

and b.name like 'SqlQueryNotification%'

 

declare@sql varchar(255)

declare@obj_name varchar(255)

declare@obj_id int

declare@obj_type varchar(10)

declare@svc_name varchar(255)

declare@ct int

select@ct = 1

 

while@ct <= (select MAX(id) from @objects)

  begin

select@obj_name = name,

@obj_id = obj_id,

@obj_type = xtype

from@objects

whereid = @ct

if @obj_type = 'SQ'

 begin

select@svc_name = name

fromsys.services

whereservice_queue_id = @obj_id

if coalesce(@svc_name,'') <> ''

 begin

select@sql = 'IF  EXISTS (SELECT * FROM sys.services WHERE name = ''' + @svc_name + ''')'

select@sql = @sql + ' BEGIN '

select@sql = @sql + 'DROP SERVICE [' + @svc_name + ']'

select@sql = @sql + ' END'

exec(@sql)

 end

if coalesce(@obj_name,'') <> ''

 begin

select@sql = 'DROP QUEUE [' + @schema_name + '].[' + @obj_name + ']'

exec(@sql)

 end

 end

if @obj_type = 'P'

 begin

if coalesce(@obj_name,'') <> ''

 begin

select@sql = 'DROP PROCEDURE [' + @schema_name + '].[' + @obj_name + ']'

exec(@sql)

 end

 end

select@ct = @ct + 1

  end

GO

 

-- Drop Services User

 

USE [impresario]

GO

/****** Object:  Schema [tessApi]    Script Date: 12/18/2011 21:38:09 ******/

IF  EXISTS (SELECT * FROM sys.schemas WHERE name = N'tessApi')

DROP SCHEMA [tessApi]

GO

/****** Object:  Schema [SqlDependencySchema]    Script Date: 12/18/2011 21:38:21 ******/

IF  EXISTS (SELECT * FROM sys.schemas WHERE name = N'SqlDependencySchema')

DROP SCHEMA [SqlDependencySchema]

GO

/****** Object:  User [tessApi]    Script Date: 12/18/2011 21:38:43 ******/

IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'tessApi')

DROP USER [tessApi]

GO

USE [impresario_cci]

GO

/****** Object:  Schema [tessApi]    Script Date: 12/18/2011 21:38:09 ******/

IF  EXISTS (SELECT * FROM sys.schemas WHERE name = N'tessApi')

DROP SCHEMA [tessApi]

GO

/****** Object:  Schema [SqlDependencySchema]    Script Date: 12/18/2011 21:38:21 ******/

IF  EXISTS (SELECT * FROM sys.schemas WHERE name = N'SqlDependencySchema')

DROP SCHEMA [SqlDependencySchema]

GO

/****** Object:  User [tessApi]    Script Date: 12/18/2011 21:38:43 ******/

IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'tessApi')

DROP USER [tessApi]

GO

--/****** Object:  Login [tessApi]    Script Date: 12/18/2011 21:40:07 ******/

--IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'tessApi')

--DROP LOGIN [tessApi]

--GO

 

-- Create Services User

 

USE IMPRESARIO

GO

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'tessApi')

  begin

      CREATE USER [tessApi] FOR LOGIN [tessApi] 

      WITH DEFAULT_SCHEMA = [SqlDependencySchema]

  end

GO

ALTER USER [tessApi] WITH DEFAULT_SCHEMA=[SqlDependencySchema]

GO

IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'SqlDependencySchema')

  begin

      DROP SCHEMA [SqlDependencySchema]

  end

GO

CREATE SCHEMA [SqlDependencySchema] AUTHORIZATION [tessApi]

GO

EXEC sp_addrolemember 'tessitura_sql_dependency_role', 'tessApi'

EXEC sp_addrolemember 'impusers', 'tessApi'

GO

USE impresario_cci

GO

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'tessApi')

  begin

      CREATE USER [tessApi] FOR LOGIN [tessApi] 

      WITH DEFAULT_SCHEMA = [SqlDependencySchema]

  end

GO

ALTER USER [tessApi] WITH DEFAULT_SCHEMA=[SqlDependencySchema]

GO

IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'SqlDependencySchema')

  begin

      DROP SCHEMA [SqlDependencySchema]

  end

GO

CREATE SCHEMA [SqlDependencySchema] AUTHORIZATION [tessApi]

GO

EXEC sp_addrolemember 'tessitura_sql_dependency_role', 'tessApi'

EXEC sp_addrolemember 'impusers', 'tessApi'

GO

 

-- Enable Service Broker

 

declare     @query nvarchar(255)

If NOT EXISTS (select * From sys.databases Where is_broker_enabled = 1 and name = 'impresario')

  Begin

      SET @query = N'ALTER DATABASE impresario SET ENABLE_BROKER with rollback immediate' 

      Exec sp_executesql @query

  End

GO

 

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!