How to compare and update database scheme

Question

Hi, 

I have two databases (let's call them DB1 and DB2) on two different servers. the DB2 has an old structure. Some tables were changed:

some columns were added in some tables, some removed, some changed the datatype or allow null values.

also some procedures were changed/ added

I need to get the DB2 into the same structure as DB1: the same table names, columns, SP. I don't care about the data, the can be deleted if needed. Is there any way, how to do it? I don't have full administration rights.

Thank you

Answer 1

Best solution would be to use some 3rd party apps.. 

red-gate sql compare http://www.red-gate.com/products/sql-development/sql-compare/?utm_source=google&utm_medium=cpc&utm_content=brand_aware&utm_campaign=sqlcompare&gclid=CJ2isYjfqa8CFcgw3wodDi8TWQ

http://www.sqlaccessories.com/ 

VT

---

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

Answer 2

thx for answer. I will try to use that 3rd party soft, but it's a little bit problem, because I need to split the install package 50MB into 1MB parts and send it via email to the remote computer, because on the remote computer I do not have internet access and copy paste from local to remote doesn't works  :( 

Any other solution appropriated 

Answer 3

It is a bit challenging undertaking without admin rights.

Instead of emailing, how about ftp transfer?

Ftp

Transfers files to and from a computer running a File Transfer Protocol (FTP) server service such as Internet Information Services. Ftp can be used interactively or in batch mode by processing ASCII text files.

http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/ftp.mspx?mfr=true

You can also transfer files with the SSIS Import/Export Wizard:

http://www.sqlusa.com/bestpractices/ssis-wizard/

Simple table compare:

http://www.sqlusa.com/bestpractices2008/compare-tables/

Database metadata is in INFORMATION_SCHEMA views for comparison:

http://www.sqlusa.com/bestpractices2005/informationschema/

---

Kalman Toth SQL SERVER & BI TRAINING

Answer 4

I was succesfull when transfering the files via email. now I'm trying to synchro the two DB, but need to truncate some tables before, because when synchronizing, data are copied, which leads to timeouts.

Answer 5

So many tools available for this.

You can make use of Red gate tool.

http://www.red-gate.com/products/sql-development/sql-compare/