gistfile1.txt

-- change all instances of 'dev_localhost_localdomain' to whatever the database name is on production.
-- run against 'restore2010' and then change 'restore2010' to 'restore2009' and run again.

insert into dev_localhost_localdomain.Survey 
select * from restore2010.Survey 
where restore2010.Survey.revisionDate not in ( select revisionDate from dev_localhost_localdomain.Survey );

-- too slow...
-- insert into dev_localhost_localdomain.assetData
-- select assetId, revisionDate
-- from restore2010.assetData
-- where restore2010.assetData.revisionDate not in ( select revisionDate from dev_localhost_localdomain.assetData )
-- and restore2010.assetData.assetId in ( select assetId from dev_localhost_localdomain.Survey_response );

insert into dev_localhost_localdomain.wobject
  ( displayTitle, description, assetId,  styleTemplateId, 
    printableStyleTemplateId, revisionDate,           mobileStyleTemplateId )
select distinctrow
  restore2010.wobject.displayTitle, 
  restore2010.wobject.description, 
  restore2010.wobject.assetId,  
  restore2010.wobject.styleTemplateId, 
  restore2010.wobject.printableStyleTemplateId, 
  restore2010.wobject.revisionDate,           
  restore2010.wobject.mobileStyleTemplateId 
from restore2010.wobject
join dev_localhost_localdomain.Survey_response using (assetId, revisionDate)
left join dev_localhost_localdomain.wobject using (assetId, revisionDate)
where dev_localhost_localdomain.wobject.assetId is null;

insert into dev_localhost_localdomain.assetData
  ( assetId, revisionDate, revisedBy, tagId, status, title, menuTitle, url, ownerUserId, groupIdView,
    groupIdEdit, synopsis, newWindow, isHidden, isPackage, isPrototype, encryptPage, assetSize,
    extraHeadTags, skipNotification, isExportable, inheritUrlFromParent, lastModified, extraHeadTagsPacked,
    usePackedHeadTags )
select distinctrow
    restore2010.assetData.assetId,
    restore2010.assetData.revisionDate,
    restore2010.assetData.revisedBy,
    restore2010.assetData.tagId,
    restore2010.assetData.status,
    restore2010.assetData.title,
    restore2010.assetData.menuTitle,
    restore2010.assetData.url,
    restore2010.assetData.ownerUserId,
    restore2010.assetData.groupIdView,
    restore2010.assetData.groupIdEdit,
    restore2010.assetData.synopsis,
    restore2010.assetData.newWindow,
    restore2010.assetData.isHidden,
    restore2010.assetData.isPackage,
    restore2010.assetData.isPrototype,
    restore2010.assetData.encryptPage,
    restore2010.assetData.assetSize,
    restore2010.assetData.extraHeadTags,
    restore2010.assetData.skipNotification,
    restore2010.assetData.isExportable,
    restore2010.assetData.inheritUrlFromParent,
    restore2010.assetData.lastModified,
    restore2010.assetData.extraHeadTagsPacked,
    restore2010.assetData.usePackedHeadTags 
from restore2010.assetData
join dev_localhost_localdomain.Survey_response using (assetId, revisionDate)
left join dev_localhost_localdomain.assetData using (assetId, revisionDate)
where dev_localhost_localdomain.assetData.assetId is null;

test.sql

select from_unixtime(max(Survey_response.revisionDate)) 
from Survey_response left join Survey using (assetId, revisionDate) where Survey.revisionDate is null; 

select from_unixtime(min(Survey_response.revisionDate))
from Survey_response left join Survey using (assetId, revisionDate) where Survey.revisionDate is null;

select from_unixtime(max(Survey_response.revisionDate))
from Survey_response left join assetData using (assetId, revisionDate) where assetData.revisionDate is null;
 
select from_unixtime(min(Survey_response.revisionDate))
from Survey_response left join assetData using (assetId, revisionDate) where assetData.revisionDate is null;
 
select from_unixtime(max(Survey_response.revisionDate))
from Survey_response left join wobject using (assetId, revisionDate) where wobject.revisionDate is null;
 
select from_unixtime(min(Survey_response.revisionDate))
from Survey_response left join wobject using (assetId, revisionDate) where wobject.revisionDate is null;

testusers.sql

-- some restored users whose histories should work correctly
select username, email, userId
from dev_localhost_localdomain.Survey_response
join dev_localhost_localdomain.userProfileData using (userId)
join restore2010.Survey using (assetId, revisionDate)
order by rand()
limit 10;


-- some still broken users
select username, email, userId
from dev_localhost_localdomain.userProfileData 
join dev_localhost_localdomain.Survey_response using (userId)
left join dev_localhost_localdomain.Survey using (assetId, revisionDate)
where dev_localhost_localdomain.Survey.assetId is null
order by rand()
limit 10;