Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Bit of a "Newbie" question. Compare and join databases. Expand / Collapse
Author
Message
Posted Friday, February 07, 2014 7:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 10, 2014 12:21 PM
Points: 3, Visits: 4
Hello all, greetings from a cold-winter Sweden.
I have a problem that i need help with, tried to seach the forum posts but got too many hits.

A customer has messed up while moving their databases.
After working for a week they found that data is missing in the database.
I have two backups, one from the old server and one from the new server today, they have been working in the new one for a week.

I need to compare these two databases and then update the new database with all data that is in the old one but not in the new database.
Join the data in the two databases so to say.
Both databases are from the same application so they use the same users, schema and so on.

Any ideas on how to do this in the best way?
Post #1539193
Posted Friday, February 07, 2014 8:19 AM This worked for the OP Answer marked as solution


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:33 AM
Points: 12,744, Visits: 31,073
stefan 97267 (2/7/2014)
Hello all, greetings from a cold-winter Sweden.
I have a problem that i need help with, tried to seach the forum posts but got too many hits.

A customer has messed up while moving their databases.
After working for a week they found that data is missing in the database.
I have two backups, one from the old server and one from the new server today, they have been working in the new one for a week.

I need to compare these two databases and then update the new database with all data that is in the old one but not in the new database.
Join the data in the two databases so to say.
Both databases are from the same application so they use the same users, schema and so on.

Any ideas on how to do this in the best way?


restore the old database on the server with a differnet name, ie PRODUCTIONCopy

then you can compare tables between the two databases pretty easily: the INTERSECT and EXCEPT operators can help a lot with that:
--items in copy that don't exist in production
SELECT SpecificColumns FROM PRODUCTIONCopy.dbo.Invoices
EXCEPT
SELECT SpecificColumns FROM PRODUCTION.dbo.Invoices

--items in production that don't exist in the copy
SELECT SpecificColumns FROM PRODUCTION.dbo.Invoices
EXCEPT
SELECT SpecificColumns FROM PRODUCTIONCopy.dbo.Invoices

then you can insert or update from the two as needed
SELECT * FROM PRODUCTIONCopy.dbo.Invoices


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1539217
Posted Sunday, February 09, 2014 11:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 10, 2014 12:21 PM
Points: 3, Visits: 4
Perfect, thank you.
But (there always is one)
The database has many tables,if i want to compare and insert on the schema level not table by table, is that possible?
Post #1539583
Posted Sunday, February 09, 2014 11:16 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:21 AM
Points: 20,462, Visits: 14,089
stefan 97267 (2/9/2014)
Perfect, thank you.
But (there always is one)
The database has many tables,if i want to compare and insert on the schema level not table by table, is that possible?


No, it would need to be done table by table.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1539585
Posted Sunday, February 09, 2014 7:01 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:25 AM
Points: 35,959, Visits: 30,253
Red-Gate has a product for comparing such data called "Data Compare". For things like this, it's worth the investment. Red-Gate also makes some other very useful tools. You can get many of the tools along with "Data Compare" or buy data compare separately. And, no... I'm not a Red-Gate employee. I just happen to like their tools.
http://www.red-gate.com/products/sql-development/sql-data-compare/


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1539611
Posted Monday, February 10, 2014 7:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 4:22 PM
Points: 7, Visits: 62

You can make a procedure obtain tables name for sys.objects like this

select a.id, a.name
from sysobjects a
where xtype = 'u'

Then build a cursor and compare each table, fill results in temp tables

Post #1539764
Posted Monday, February 10, 2014 8:12 AM This worked for the OP Answer marked as solution
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 4:22 PM
Points: 7, Visits: 62
If there are many tables use this script:


DECLARE @sql varchar(max),
@Database1 varchar(50),
@Database2 varchar(50),
@TableName varchar(50)

SET @Database1 = 'Production'
SET @Database2 = 'ProductionCopy'

DECLARE TableNameCursor CURSOR
FOR
select a.name
from sysobjects a
where xtype = 'u'
order by a.name;

OPEN TableNameCursor

FETCH NEXT
FROM TableNameCursor
INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN

print '---' + @TableName
SET @sql = 'INSERT INTO #Go'+@TableName+'
SELECT * FROM '+ @Database1 +'..'+ @TableName+'
EXCEPT
SELECT * FROM '+ @Database2 +'..'+ @TableName
EXEC(@sql)

SET @sql = 'INSERT INTO #Back'+@TableName+'
SELECT * FROM '+ @Database2 +'..'+ @TableName+'
EXCEPT
SELECT * FROM '+ @Database1 +'..'+ @TableName

EXEC(@sql)

FETCH NEXT
FROM TableNameCursor
INTO @TableName
END;

CLOSE TableNameCursor;
DEALLOCATE TableNameCursor;



Then only you must to SELECT the temp tables for each table

I hope you have served
Post #1539778
Posted Monday, February 10, 2014 12:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 10, 2014 12:21 PM
Points: 3, Visits: 4
Great answers, thank you all very much.
Will test and report back.
Post #1539905
Posted Tuesday, February 11, 2014 5:49 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, April 10, 2014 3:49 PM
Points: 366, Visits: 577
@jeff

+1 for Redgate. They are really helpful and the products are really useful. i especially like the source code control plug in for mgm studio and the SQL compare for generating migration scripts

If they would only add an SSIS / SSRS documenter solution it would be perfect.

Aaron
Post #1540155
Posted Tuesday, February 11, 2014 7:43 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 9:12 AM
Points: 1,887, Visits: 1,179
aaron.reese (2/11/2014)

If they would only add an SSIS / SSRS documenter solution it would be perfect.
Aaron

PragmaticWorks have a BIDocumenter product that may meet your needs. Disclaimer: I don't work for PragmaticWorks.

Regards
Lempster
Post #1540198
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse