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

select b.name, b.id, b.xtype

from sys.schemas a

join sysobjects b on a.schema_id = b.uid

where a.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

where id = @ct

if @obj_type = 'SQ'

 begin

select @svc_name = name

from sys.services

where service_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

David,

The Moving a Database documentation has been updated for v11 and includes all the steps you need to take for a Live to Test copy.  

If you are still having an issue please feel free to open a support ticket.

Best,

Anna

Hi Anna,

A question about the services user... I noticed in the Moving a Database document you posted, a lot of code is included to rebuild that user from scratch. However, the Resolving Orphan Users section on p.18 mentions the same sp_change_users_login procedure I mentioned in my previous post for resolving orphaned users.

We actually use the sp_change_users_login approach for our services user as well, and haven't experienced any problems (at least not yet). Do you know why it is advised to drop/recreate the services user from scratch rather than using sp_change_users_login to reassociate the login with the database user?

Thanks,
David 

Anna,

Is there a document for V11 Live to V12 Test?

Thanks

John

John,

After moving your live v11 database to a test v12 environment you will need to run the v12 TIM Upgrade and Migration processes.  There is a note in the v12 Upgrade Instructions on refreshing a v12 TEST instance with a copy of a v11 LIVE instance.  I pasted it below: