Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2011-02-25
13
Medium Priority
?
464 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 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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 learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

885 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