Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2011-02-25
13
Medium Priority
?
461 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
13 Comments
 
LVL 15

Accepted Solution

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

Assisted Solution

by:Rajkumar Gs
Rajkumar Gs earned 498 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
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…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

715 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