Bit of a "Newbie" question. Compare and join databases.

Question

Bit of a "Newbie" question. Compare and join databases.

stefan 97267

SSC Rookie

Points: 35
More actions
February 7, 2014 at 7:51 am

#283524

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?

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

stefan 97267 SSC Rookie Points: 35 More actions · Answer 2

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?

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

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, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

v.collazos SSC Enthusiast Points: 131 More actions · Answer 5

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

v.collazos SSC Enthusiast Points: 131 More actions · Answer 6

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

stefan 97267 SSC Rookie Points: 35 More actions · Answer 7

Great answers, thank you all very much.

Will test and report back.

aaron.reese SSChampion Points: 13498 More actions · Answer 8

@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

Lempster SSCoach Points: 15651 More actions · Answer 9

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

aaron.reese SSChampion Points: 13498 More actions · Answer 10

They do, and its OK but like the RedGate packages, its a bit expensive on its own and I have not been able to get buy-in where I am to invest in PW's other offerings.

davros30 SSC Veteran Points: 273 More actions · Answer 11

SQLC is another good tool that will meet your needs. RedGate is also a very good tool but it is expensive.

SQLC is very affordable. You can download it here[/url]

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 12

davros30 (3/13/2016)
SQLC is another good tool that will meet your needs. RedGate is also a very good tool but it is expensive.
SQLC is very affordable. You can download it here[/url]

So how much is "very affordable"?

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

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lowell SSC Guru Points: 323504 More actions · Answer 13

Jeff Moden (3/13/2016)
davros30 (3/13/2016)
SQLC is another good tool that will meet your needs. RedGate is also a very good tool but it is expensive.
SQLC is very affordable. You can download it here[/url]
So how much is "very affordable"?

I haven't downloaded it or tested it yet, but $18 dollars is inexpensive in my book; I've spent that much on a decent dinner. I'll play with it and report what i think.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Wayne West SSC-Insane Points: 22586 More actions · Answer 14

Lowell (3/14/2016)

I haven't downloaded it or tested it yet, but $18 dollars is inexpensive in my book; I've spent that much on a decent dinner. ...

I've spent more than that on dinners that weren't that decent. :sick:

-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]