Solved

[MS SQL 2008] Merging the difference of two SQL database (tables, procedures, etc) ???

Posted on 2011-02-25
13
452 Views
Last Modified: 2012-05-11
I have two databases similar to each other, Base A and Base B.

Base A is clean, no data inside.
Base B however, is highly populated, but has more procedures, triggers and views than Base A.

What I need to do is to import Base B structural differences into Base A, the thing is, I can't migrate the data inside Base B, only the structural content (tables, new columns inside existing tables, procedures, triggers, etc).

Again, I just need to get the structural differences from Base B and insert them on Base A.

How can I do this?

Thanks in advance.
0
Comment
Question by:John86a
13 Comments
 
LVL 15

Accepted Solution

by:
derekkromm earned 334 total points
ID: 34985795
0
 
LVL 23

Assisted Solution

by:Rajkumar Gs
Rajkumar Gs earned 166 total points
ID: 34985842
There are many tools to compare sql server database and sychronize the change.
Top most tools that I know
1. Apex SQL Comparison Studio
2. RedGate SQL Compare

It is very easy - You can see what object are in TableA and not in TableB, viceversa and what are the objects that have difference. Then select the objects that you want to sync and click on 'Sync' button or similar button and take care to select proper destination database. If you select wrongly, it will cause lose of your objects

Raj
0
 
LVL 10

Expert Comment

by:Asim Nazir
ID: 34986210
hi,

no need to use third party tools. Simply use Visual Studio compare schema option.
You can use VS2oo8 or 2o1o

Asim
0
 

Author Comment

by:John86a
ID: 34988692
Red Gate SQL Compare almost worked, until it returned an error while in the merging process.
Apex SQL Comparison Studio return an error as well, but at the very beginning of the merge.

Visual Studio? Care to explain?
0
 
LVL 15

Expert Comment

by:derekkromm
ID: 34988860
What errors did you get in SQL Compare? If both utilities are reporting errors, there's probably an underlying cause
0
 

Author Comment

by:John86a
ID: 34989832
This is the error for Red Gate:
The following error message was returned from the SQL Server:

[515] Cannot insert the value NULL into column 'fund_tec', table 'Expbase.dbo.CTFPlanoGer'; column does not allow nulls. UPDATE fails.

The following SQL command caused the error:

ALTER TABLE [dbo].[XPCGeneralPl] ALTER COLUMN [interg_ctb_titulo] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
ALTER TABLE [dbo].[XPCGeneralPl] ALTER COLUMN [interg_ctb_pgto] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
ALTER TABLE [dbo].[XPCGeneralPl] ALTER COLUMN [fluxo] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
ALTER TABLE [dbo].[XPCGeneralPl] ALTER COLUMN [tp_atividade] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
ALTER TABLE [dbo].[XPCGeneralPl] ALTER COLUMN [despesa_antec] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

This is the error for Apex:
 Apex Error
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 15

Expert Comment

by:derekkromm
ID: 34989836
So judging from the RedGate errors, it looks like you're attempting to change a column definition from NULL to NOT NULL, but there are pre-existing NULLS already present in the table.

Can you verify by doing this:

select * from Expbase.dbo.CTFPlanoGer where fund_tec IS NULL

If there are NULLs, you'll need to update them to a non-NULL value, or you won't be able to sync that column definition.
0
 

Author Comment

by:John86a
ID: 34989861

(38 row(s) affected)

That command returned a bunch of values. Column fund_tec was all 'NULL'.
0
 

Author Comment

by:John86a
ID: 34989864
I thought it would be an automated process where I wouldn't have to fill data in. I just wanted to sync the structure.. is it possible at all?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34989907
>>I just wanted to sync the structure.. is it possible at all?<<
SQL Compare cannot guess what value to add to those columns that do not allow NULLs, nor would it be responsible for them to do it.
0
 

Author Comment

by:John86a
ID: 34989940
Oh, I get it now.. how would I make them all allow null?
0
 
LVL 15

Assisted Solution

by:derekkromm
derekkromm earned 334 total points
ID: 34989967
There should be an option to just generate the script that would sync the databases.

From there, you'd have to change any new columns that are NOT NULL to NULL before executing and any existing columns that change to NOT NULL back to NULL
0
 

Author Closing Comment

by:John86a
ID: 34993847
Thanks.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now