Parker Software Ltd Homepage
Forum Home Forum Home > WhosOn Live Chat > FAQ's > Advanced FAQ's
  New Posts New Posts RSS Feed - Individual Site Migration Using A Linked Database
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

Individual Site Migration Using A Linked Database

 Post Reply Post Reply
Author
Message
Daniel Horton View Drop Down
Admin Group
Admin Group
Avatar
Technical Support

Joined: 01 Jun 2009
Location: Stoke-on-Trent
Points: 346
Post Options Post Options   Thanks (0) Thanks(0)   Quote Daniel Horton Quote  Post ReplyReply Direct Link To This Post Topic: Individual Site Migration Using A Linked Database
    Posted: 27 Oct 2010 at 11:42am
Wishing to migrate individual site data from one WhosOn instance to another? 

The following stored procedure can be done if you have a good knowledge of Microsoft SQL Server. You will require their to be a link formed prior to this using SQL Server Management Studio's Linked Database functionality, then  if you know the SiteKey values for the sites you are attempting to migrate from one server to the other then the following stored procedure can be fired. 

***WARNING***
There are few things to look out for when doing this though, such as prior data being present in the database of the target server. Make sure that there is little to no data in the database before performing the data migration. There is a possibility that UIDs may already be used within the target database so if you receive any errors relating to that, then those records will need to be altered to allow this data to come through.
*************

Simply enter your new and old site keys and a date and time for the period you wish to migrate across from and replace all of the LinkedServer.[WhosOnV5].[dbo] declarations with the correct Linked Database information:

Declare @SiteKeyNew As Integer
Declare @SiteKeyOld As Integer 
Declare @LastDate As Date

SET @SiteKeyNew = !Enter new site key
SET @SiteKeyOld = !Enter old site key

SET @LastDate = !Enter your date here



INSERT INTO [WhosOnV5].[dbo].[DaySummary]
           ([SiteKey]
           ,[Dated]
           ,[Visits]
           ,[FullDayFlag]
           ,[LastTime]
           ,[NewVisitors]
           ,[AlertsFree]
           ,[AlertsPaid]
           ,[Spiders]
           ,[Exceptions]
           ,[Downloads]
           ,[Invites]
           ,[Chats]
           ,[ChatsMissed]
           ,[Prospects]
           ,[Customers]
           ,[Hacks]
           ,[PaidListingAbuse]
           ,[Peak]
           ,[PeakTime]
           ,[BytesServed]
           ,[BytesReceived]
           ,[TimeTaken]
           ,[PageErrors]
           ,[PageViews]
           ,[Cost]
           ,[Revenue])
     SELECT @SiteKeyNew
      ,[Dated]
      ,[Visits]
      ,[FullDayFlag]
      ,[LastTime]
      ,[NewVisitors]
      ,[AlertsFree]
      ,[AlertsPaid]
      ,[Spiders]
      ,[Exceptions]
      ,[Downloads]
      ,[Invites]
      ,[Chats]
      ,[ChatsMissed]
      ,[Prospects]
      ,[Customers]
      ,[Hacks]
      ,[PaidListingAbuse]
      ,[Peak]
      ,[PeakTime]
      ,[BytesServed]
      ,[BytesReceived]
      ,[TimeTaken]
      ,[PageErrors]
      ,[PageViews]
      ,[Cost]
      ,[Revenue]
  FROM LinkedServer.[WhosOnV5].[dbo].[DaySummary]
WHERE SiteKey = @SiteKeyOld AND Dated <= @LastDate




INSERT INTO [WhosOnV5].[dbo].[DaySummaryKeywords]
           ([SiteKey]
           ,[Dated]
           ,[KeywordID]
           ,[Visits]
           ,[NewVisitors]
           ,[Prospects]
           ,[Customers])
SELECT @SiteKeyNew
      ,[Dated]
      ,[KeywordID]
      ,[Visits]
      ,[NewVisitors]
      ,[Prospects]
      ,[Customers]
  FROM [LinkedServer].[WhosOnV5].[dbo].[DaySummaryKeywords]
WHERE SiteKey = @SiteKeyOld AND Dated <= @LastDate

INSERT INTO [WhosOnV5].[dbo].[DaySummaryAlerts]
           ([SiteKey]
           ,[Dated]
           ,[AlertNo]
           ,[Visits]
           ,[NewVisitors]
           ,[Prospects]
           ,[Customers]
           ,[PageViews]
           ,[Invites]
           ,[Chats]
           ,[Cost]
           ,[Revenue])
     SELECT @SiteKeyNew
      ,[Dated]
      ,[AlertNo]
      ,[Visits]
      ,[NewVisitors]
      ,[Prospects]
      ,[Customers]
      ,[PageViews]
      ,[Invites]
      ,[Chats]
      ,[Cost]
      ,[Revenue]
  FROM [LinkedServer].[WhosOnV5].[dbo].[DaySummaryAlerts]
WHERE SiteKey = @SiteKeyOld AND Dated <= @LastDate

INSERT INTO [WhosOnV5].[dbo].[DaySummaryPages]
           ([SiteKey]
           ,[Dated]
           ,[PageID]
           ,[Visits]
           ,[NewVisitors]
           ,[PageViews]
           ,[Chats]
           ,[Errors]
           ,[TimeSpent])
    SELECT @SiteKeyNew
      ,[Dated]
      ,[PageID]
      ,[Visits]
      ,[NewVisitors]
      ,[PageViews]
      ,[Chats]
      ,[Errors]
      ,[TimeSpent]
  FROM [LinkedServer].[WhosOnV5].[dbo].[DaySummaryPages]
WHERE SiteKey = @SiteKeyOld AND Dated <= @LastDate

INSERT INTO [WhosOnV5].[dbo].[DaySummaryReferrers]
           ([SiteKey]
           ,[Dated]
           ,[ReferrerID]
           ,[Visits]
           ,[NewVisitors]
           ,[Prospects]
           ,[Customers])
    SELECT @SiteKeyNew
      ,[Dated]
      ,[ReferrerID]
      ,[Visits]
      ,[NewVisitors]
      ,[Prospects]
      ,[Customers]
FROM [LinkedServer].[WhosOnV5].[dbo].[DaySummaryReferrers]
WHERE SiteKey = @SiteKeyOld AND Dated <= @LastDate

ALTER INDEX keywords_key1 on WhosOnV5.dbo.keywords
SET ( ignore_dup_key = ON );

INSERT INTO [WhosOnV5].[dbo].[Keywords]
           ([SiteKey]
           ,[SearchEngine]
           ,[Keywords])
     SELECT @SiteKeyNew
      ,[SearchEngine]
      ,[Keywords]
  FROM [LinkedServer].[WhosOnV5].[dbo].[Keywords]
WHERE SiteKey = @SiteKeyOld 



INSERT INTO [WhosOnV5].[dbo].[PageViews]
           ([VisitID]
           ,[PageID]
           ,[VisitorID]
           ,[SiteKey]
           ,[PageDate]
           ,[PageNumber]
           ,[HTTPStatus]
           ,[HTTPSubStatus])
     SELECT 
      [VisitID]
      ,[PageID]
      ,[VisitorID]
      ,@SiteKeyNew
      ,[PageDate]
      ,[PageNumber]
      ,[HTTPStatus]
      ,[HTTPSubStatus]
FROM [LinkedServer].[WhosOnV5].[dbo].[PageViews]
WHERE SiteKey = @SiteKeyOld AND PageDate <= @LastDate

ALTER INDEX keywords_key1 on WhosOnV5.dbo.keywords
SET ( ignore_dup_key = OFF );

ALTER INDEX PageHeader_key1 on WhosOnV5.dbo.PageHeader
SET ( ignore_dup_key = ON );

INSERT INTO [WhosOnV5].[dbo].[PageHeader]
           ([SiteKey]
           ,[Page]
           ,[TitleTag]
           ,[DescriptionTag]
           ,[Keywords]
           ,[ContentGroup]
           ,[LastModified]
           ,[LastChecked])
     SELECT @SiteKeyNew
      ,[Page]
      ,[TitleTag]
      ,[DescriptionTag]
      ,[Keywords]
      ,[ContentGroup]
      ,[LastModified]
      ,[LastChecked]
  FROM [LinkedServer].[WhosOnV5].[dbo].[PageHeader]
WHERE SiteKey = @SiteKeyOld 
ALTER INDEX PageHeader_key1 on WhosOnV5.dbo.PageHeader
SET ( ignore_dup_key = OFF );


ALTER INDEX referrers_key1 on WhosOnV5.dbo.referrers
SET ( ignore_dup_key = ON );

INSERT INTO [WhosOnV5].[dbo].[Referrers]
           ([SiteKey]
           ,[SearchEngine]
           ,[Referrer])
     SELECT 
      @SiteKeyNew
      ,[SearchEngine]
      ,[Referrer]
  FROM [LinkedServer].[WhosOnV5].[dbo].[Referrers]
WHERE SiteKey = @SiteKeyOld

ALTER INDEX referrers_key1 on WhosOnV5.dbo.referrers
SET ( ignore_dup_key = OFF );

INSERT INTO [WhosOnV5].[dbo].[UserCallBacks]
           ([SiteKey]
           ,[CallUID]
           ,[VisitID]
           ,[VKey]
           ,[RequestTime]
           ,[RequestedUser]
           ,[RequestedDept]
           ,[TakenByUser]
           ,[TakenByDept]
           ,[Dated]
           ,[StartTime]
           ,[FinishTime]
           ,[TelephoneNo]
           ,[TelephoneType]
           ,[VisitorName]
           ,[VisitorCompany]
           ,[VisitorCountry]
           ,[VisitorEmail]
           ,[VisitorTimeZone]
           ,[VisitorIP]
           ,[VisitorUserName]
           ,[VisitorVisitNumber]
           ,[CallClosed]
           ,[Notes])
     SELECT 
      @SiteKeyNew
      ,[CallUID]
      ,[VisitID]
      ,[VKey]
      ,[RequestTime]
      ,[RequestedUser]
      ,[RequestedDept]
      ,[TakenByUser]
      ,[TakenByDept]
      ,[Dated]
      ,[StartTime]
      ,[FinishTime]
      ,[TelephoneNo]
      ,[TelephoneType]
      ,[VisitorName]
      ,[VisitorCompany]
      ,[VisitorCountry]
      ,[VisitorEmail]
      ,[VisitorTimeZone]
      ,[VisitorIP]
      ,[VisitorUserName]
      ,[VisitorVisitNumber]
      ,[CallClosed]
      ,[Notes]
 FROM [LinkedServer].[WhosOnV5].[dbo].[UserCallBacks]
WHERE SiteKey = @SiteKeyOld AND Dated <= @LastDate

INSERT INTO [WhosOnV5].[dbo].[UserChats]
           ([SiteKey]
           ,[ChatUID]
           ,[VisitID]
           ,[VKey]
           ,[TakenByUser]
           ,[TakenByDept]
           ,[Dated]
           ,[StartTime]
           ,[FinishTime]
           ,[VisitorName]
           ,[VisitorIP]
           ,[VisitorUserName]
           ,[VisitorVisitNumber]
           ,[VisitorPageID]
           ,[WaitedForSecs]
           ,[ChattedForSecs]
           ,[VisitorClosed]
           ,[Invited]
           ,[Rating]
           ,[MessageEmail]
           ,[MessageText])
     SELECT 
      @SiteKeyNew
      ,[ChatUID]
      ,[VisitID]
      ,[VKey]
      ,[TakenByUser]
      ,[TakenByDept]
      ,[Dated]
      ,[StartTime]
      ,[FinishTime]
      ,[VisitorName]
      ,[VisitorIP]
      ,[VisitorUserName]
      ,[VisitorVisitNumber]
      ,[VisitorPageID]
      ,[WaitedForSecs]
      ,[ChattedForSecs]
      ,[VisitorClosed]
      ,[Invited]
      ,[Rating]
      ,[MessageEmail]
      ,[MessageText]
  FROM [LinkedServer].[WhosOnV5].[dbo].[UserChats]
WHERE SiteKey = @SiteKeyOld AND Dated <= @LastDate

INSERT INTO [WhosOnV5].[dbo].[UserChatVars]
           ([SiteKey]
           ,[ChatUID]
           ,[VarKey]
           ,[PreOrPost]
           ,[VarData])
     SELECT @SiteKeyNew
      ,[ChatUID]
      ,[VarKey]
      ,[PreOrPost]
      ,[VarData]
 FROM [LinkedServer].[WhosOnV5].[dbo].[UserChatVars]
WHERE SiteKey = @SiteKeyOld

INSERT INTO [WhosOnV5].[dbo].[UserTranscript]
           ([SiteKey]
           ,[ChatUID]
           ,[LineNumber]
           ,[LineTime]
           ,[OperatorLine]
           ,[LineText])
     SELECT 
      @SiteKeyNew
      ,[ChatUID]
      ,[LineNumber]
      ,[LineTime]
      ,[OperatorLine]
      ,[LineText]
 FROM [LinkedServer].[WhosOnV5].[dbo].[UserTranscript]
WHERE SiteKey = @SiteKeyOld

INSERT INTO [WhosOnV5].[dbo].[Visit]
           ([VKey]
           ,[SiteKey]
           ,[Alert]
           ,[AlertNo]
           ,[VisitNo]
           ,[VisitDate]
           ,[PagesSeen]
           ,[SecondsOnSite]
           ,[Invited]
           ,[Chatted]
           ,[Prospect]
           ,[Customer]
           ,[HackRaised]
           ,[PaidListingAbuse]
           ,[Spider]
           ,[Cost]
           ,[Revenue]
           ,[FirstPageID]
           ,[LastPageID]
           ,[OperatingSystemID]
           ,[BrowserID]
           ,[ReferrerID]
           ,[KeywordsID]
           ,[ReferrerQuery])
     SELECT 
      [VKey]
      ,@SiteKeyNew
      ,[Alert]
      ,[AlertNo]
      ,[VisitNo]
      ,[VisitDate]
      ,[PagesSeen]
      ,[SecondsOnSite]
      ,[Invited]
      ,[Chatted]
      ,[Prospect]
      ,[Customer]
      ,[HackRaised]
      ,[PaidListingAbuse]
      ,[Spider]
      ,[Cost]
      ,[Revenue]
      ,[FirstPageID]
      ,[LastPageID]
      ,[OperatingSystemID]
      ,[BrowserID]
      ,[ReferrerID]
      ,[KeywordsID]
      ,[ReferrerQuery]
   FROM [LinkedServer].[WhosOnV5].[dbo].[Visit]
WHERE SiteKey = @SiteKeyOld AND VisitDate <= @LastDate

INSERT INTO [WhosOnV5].[dbo].[VisitFormVars]
           ([VisitID]
           ,[SiteKey]
           ,[VarForm]
           ,[VarName]
           ,[VarData]
           ,[VarTime])
     SELECT 
      [VisitID]
      ,@SiteKeyNew
      ,[VarForm]
      ,[VarName]
      ,[VarData]
      ,[VarTime]
  FROM [LinkedServer].[WhosOnV5].[dbo].[VisitFormVars]
WHERE SiteKey = @SiteKeyOld

INSERT INTO [WhosOnV5].[dbo].[VisitorContact]
           ([VKey]
           ,[SiteKey]
           ,[IP]
           ,[Name]
           ,[Email]
           ,[Company]
           ,[Telephone]
           ,[Fax]
           ,[Street]
           ,[City]
           ,[ZIP]
           ,[Country]
           ,[Web]
           ,[Organization]
           ,[CRMID]
           ,[DateAdded]
           ,[Notes])
     SELECT 
      [VKey]
      ,@SiteKeyNew
      ,[IP]
      ,[Name]
      ,[Email]
      ,[Company]
      ,[Telephone]
      ,[Fax]
      ,[Street]
      ,[City]
      ,[ZIP]
      ,[Country]
      ,[Web]
      ,[Organization]
      ,[CRMID]
      ,[DateAdded]
      ,[Notes]
 FROM [LinkedServer].[WhosOnV5].[dbo].[VisitorContact]
WHERE SiteKey = @SiteKeyOld AND DateAdded <= @LastDate

INSERT INTO [WhosOnV5].[dbo].[Visitors]
           ([VKey]
           ,[SiteKey]
           ,[IP]
           ,[Visits]
           ,[FirstVisitID]
           ,[FirstDate]
           ,[LastDate]
           ,[Spider]
           ,[PaidListingAbuse]
           ,[HackCount]
           ,[ProspectVisitID]
           ,[CustomerVisitID]
           ,[TotalCost]
           ,[TotalRevenue]
           ,[Country]
           ,[Region])
     SELECT 
      [VKey]
      ,@SiteKeyNew
      ,[IP]
      ,[Visits]
      ,[FirstVisitID]
      ,[FirstDate]
      ,[LastDate]
      ,[Spider]
      ,[PaidListingAbuse]
      ,[HackCount]
      ,[ProspectVisitID]
      ,[CustomerVisitID]
      ,[TotalCost]
      ,[TotalRevenue]
      ,[Country]
      ,[Region]
  FROM [LinkedServer].[WhosOnV5].[dbo].[Visitors]
WHERE SiteKey = @SiteKeyOld AND LastDate <= @LastDate



Edited by James Horton - 22 Nov 2010 at 10:38am
Best Regards,

Daniel James Horton
Technical Manager
Parker Software
Back to Top
Liam View Drop Down
Admin Group
Admin Group
Avatar

Joined: 29 Jun 2011
Location: Stoke-on-Trent
Points: 256
Post Options Post Options   Thanks (0) Thanks(0)   Quote Liam Quote  Post ReplyReply Direct Link To This Post Posted: 29 Aug 2016 at 3:40am
Duplicates may occur on some of the below tables, depending on how long the two database have been separated for.
This is most likely to occur on the Visit and Visitors table.

You can work around this by running the AddCompleteVisitRecord stored procedure, filling on all the required fields with information from the Visit table.

some notable fields:

TempSessionID = 0
InsertPagesIntoDatabase = 0
(SELECT OperatingSystem FROM OperatingSystems WHERE OperatingSystemID = V.[OperatingSystemID])
(SELECT Browser FROM Browsers WHERE BrowserID = V.BrowserID)
(SELECT SearchEngine FROM Referrers WHERE ReferrerID = V.ReferrerID)
(SELECT Referrer FROM Referrers WHERE ReferrerID = V.ReferrerID)
(SELECT Keywords FROM Keywords WHERE KeywordID = V.KeywordsID AND SiteKey = V.SiteKey)
Country = ''
Region = ''


Edited by Liam - 29 Aug 2016 at 4:46am
Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 11.05
Copyright ©2001-2016 Web Wiz Ltd.

This page was generated in 0.094 seconds.