Datapolis Process System
Migrating from SharePoint 2010 to 2013 (splitting database scenario)
If you cannot migrate your SharePoint 2010 farm to the 2013 all at once, following steps will show how to migrate your Datapolis Workbox 2010 workflows only from one selected SharePoint web application.
-- create temporary db
CREATE DATABASE <temporary_database_name>
GO
USE <temporary_database_name>
-- create tables
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[Associations](
[Id] [int] IDENTITY(1,1) NOT NULL,
[AssociationId] [uniqueidentifier] NULL,
[AppId] [uniqueidentifier] NULL,
[SiteId] [uniqueidentifier] NULL,
[WebId] [uniqueidentifier] NULL,
[ListId] [uniqueidentifier] NULL,
[AssociationType] [int] NULL,
[AssociationContent] [nvarchar](max) NULL,
[WBVersion] [nvarchar](20) NULL,
[AssociationReferenceId] [uniqueidentifier] NULL,
CONSTRAINT [PK_Associations] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
CREATE TABLE [dbo].[History](
[WorkflowInstanceId] [uniqueidentifier] NULL,
[TemplateId] [uniqueidentifier] NULL,
[ItemId] [int] NULL,
[UserId] [int] NULL,
[UserLogin] [nvarchar](100) NULL,
[Occured] [datetime2](7) NULL,
[EventType] [int] NULL,
[EventName] [nvarchar](100) NULL,
[EventTitle] [nvarchar](100) NULL,
[EventContent] [nvarchar](max) NULL,
CONSTRAINT [PK_History] PRIMARY KEY CLUSTERED
CREATE TABLE [dbo].[WBXml](
[BaseId] [uniqueidentifier] NULL,
[StateTitle] [nvarchar](100) NULL,
[ShowActions] [bit] NULL,
[WBXmlContent] [nvarchar](max) NULL,
CONSTRAINT [PK_WBXml] PRIMARY KEY CLUSTERED
CREATE TABLE [dbo].[SelfTimers](
[StartTime] [datetime2](7) NULL,
[ActionName] [nvarchar](100) NULL,
[StateInstanceId] [uniqueidentifier] NULL,
CONSTRAINT [PK_SelfTimers] PRIMARY KEY CLUSTERED
) ON [PRIMARY]
CREATE TABLE [dbo].[Tasks](
[TaskType] [int] NULL,
[CreatedBy] [nvarchar](100) NULL,
[Created] [datetime2](7) NULL,
[Started] [datetime2](7) NULL,
[Finished] [datetime2](7) NULL,
[TaskState] [int] NULL,
[Progress] [int] NULL,
[ItemIds] [nvarchar](max) NULL,
[WorkflowId] [uniqueidentifier] NULL,
[WorkflowIds] [nvarchar](max) NULL,
[TaskContent] [nvarchar](max) NULL,
[Result] [nvarchar](max) NULL,
CONSTRAINT [PK_Tasks] PRIMARY KEY CLUSTERED
--from
--SOURCE DB <source_DP_DB_2010_database_name>
--to
--TEMP DB <temporary_database_name>
-- remeber to replace database names in scrips !!!
SET STATISTICS TIME ON
DECLARE @TranName VARCHAR(50);
SET @TranName = 'CopyDPDBContentToTemp';
BEGIN TRANSACTION @TranName;
-- id of the web application that will be migrated
DECLARE @appId_SOURCE uniqueidentifier = 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'
SET IDENTITY_INSERT <temporary_database_name>.[dbo].[Associations] ON
INSERT INTO <temporary_database_name>.[dbo].[Associations]
([Id]
,[AssociationId]
,[AppId]
,[SiteId]
,[WebId]
,[ListId]
,[AssociationType]
,[AssociationContent]
,[WBVersion]
,[AssociationReferenceId])
SELECT [Id]
,[AssociationReferenceId] FROM <source_DP_DB_2010_database_name>.[dbo].[Associations] WHERE [AppId] = @appId_SOURCE
SET IDENTITY_INSERT <temporary_database_name>.[dbo].[Associations] OFF
SET IDENTITY_INSERT <temporary_database_name>.[dbo].[History] ON
INSERT INTO <temporary_database_name>.[dbo].[History]
,[WorkflowInstanceId]
,[TemplateId]
,[ItemId]
,[UserId]
,[UserLogin]
,[Occured]
,[EventType]
,[EventName]
,[EventTitle]
,[EventContent]
,[AssociationReferenceId] FROM <source_DP_DB_2010_database_name>.[dbo].[History] WHERE [AppId] = @appId_SOURCE
SET IDENTITY_INSERT <temporary_database_name>.[dbo].[History] OFF
SET IDENTITY_INSERT <temporary_database_name>.[dbo].[SelfTimers] ON
INSERT INTO <temporary_database_name>.[dbo].[SelfTimers]
,[StartTime]
,[ActionName]
,[StateInstanceId])
,[StateInstanceId] FROM <source_DP_DB_2010_database_name>.[dbo].[SelfTimers] WHERE [AppId] = @appId_SOURCE
SET IDENTITY_INSERT <temporary_database_name>.[dbo].[SelfTimers] OFF
SET IDENTITY_INSERT <temporary_database_name>.[dbo].[Tasks] ON
INSERT INTO <temporary_database_name>.[dbo].[Tasks]
,[TaskType]
,[CreatedBy]
,[Created]
,[Started]
,[Finished]
,[TaskState]
,[Progress]
,[ItemIds]
,[WorkflowId]
,[WorkflowIds]
,[TaskContent]
,[Result])
,[Result] FROM <source_DP_DB_2010_database_name>.[dbo].[Tasks] WHERE [AppId] = @appId_SOURCE
SET IDENTITY_INSERT <temporary_database_name>.[dbo].[Tasks] OFF
SET IDENTITY_INSERT <temporary_database_name>.[dbo].[WBXml] ON
INSERT INTO <temporary_database_name>.[dbo].[WBXml]
,[BaseId]
,[StateTitle]
,[ShowActions]
,[WBXmlContent]
,[WBVersion])
,[WBVersion] FROM <source_DP_DB_2010_database_name>.[dbo].[WBXml] WHERE [AppId] = @appId_SOURCE
SET IDENTITY_INSERT <temporary_database_name>.[dbo].[WBXml] OFF
COMMIT TRANSACTION @TranName
SET STATISTICS TIME OFF
--TEMPORARY DB <temporary_database_name>
--DESTINATION DB <destination_DP_DB_2013_database_name>
-- remeber to replace databasenames in scrips !!!
SET @TranName = 'MigrateContent';
DECLARE @appId_DEST uniqueidentifier = 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'
INSERT INTO <destination_DP_DB_2013_database_name>.[dbo].[Associations]
([AssociationId]
SELECT [AssociationId]
,@appId_DEST -- set new app id
,[AssociationReferenceId] FROM <temporary_database_name>.[dbo].[Associations] WHERE [AppId] = @appId_SOURCE
INSERT INTO <destination_DP_DB_2013_database_name>.[dbo].[History]
([WorkflowInstanceId]
SELECT [WorkflowInstanceId]
,[AssociationReferenceId] FROM <temporary_database_name>.[dbo].[History] WHERE [AppId] = @appId_SOURCE
DECLARE @now datetime2(7) = GETDATE()
INSERT INTO <destination_DP_DB_2013_database_name>.[dbo].[WBXml]
([AppId]
,[Status]
,[StatusDetails]
,[StatusLog]
,[DateOfLastUpdate])
SELECT @appId_DEST -- set new app id
,N'Active'
,N'Normal'
,N'Migration'
,@now FROM <temporary_database_name>.[dbo].[WBXml] WHERE [AppId] = @appId_SOURCE
INSERT INTO <destination_DP_DB_2013_database_name>.[dbo].[Tasks]
([TaskType]
,[Result]
,[StartDate]
,[Priority]
,[TriesLeft]
,[RetryInterval])
SELECT [TaskType]
,900
,3
,0 FROM <temporary_database_name>.[dbo].[Tasks] WHERE [AppId] = @appId_SOURCE
,[RetryInterval]
SELECT 5
,N'SHAREPOINT\SYSTEM'
,@now
,0
,N''
,N'<Result />'
,1000
,20
,[StateInstanceId] FROM <temporary_database_name>.[dbo].[SelfTimers] WHERE [AppId] = @appId_SOURCE
COMMIT TRANSACTION @TranName;