CODEWITHSUNDEEP
sqlsql-servercreate-table
user1287523
user1287523

Reputation: 957

Create SQL Script to Change Old Database to Current One

I'm creating the front-end for a project and I made a copy of the back-end database from the company's server and put it on my computer. I needed to make some changes (a few new tables and two new columns in an existing table) for security roles and other things so I duplicated the copied database and made my changes on the new one.

I want to deploy my project to the company's server now but we need to modify the original back-end database. I need to generate a SQL script that finds the changes between the old-database and my newer database, which can be run on the old database to create the new tables and columns. The script should retain the data from the old database and NOT add any junk/testing data I made in my new database.

By the way, I'm using SQL Server 2008 R2 and the old database on the server is on 2005. I've been looking around for utilities to use and found tablediff. However, it looks like it will copy the data and I can't see an argument on the information page to toggle this.

I'm sure it's simple but I'm not really sure how to do this. Any help would be appreciated. Thanks.

Upvotes: 3

Views: 861

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280625

By far the solution I trust most to handle schema comparisons is Red Gate's SQL Compare:

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

It has a companion called Data Compare which is designed specifically for data. You can grab the free trial to see if it does what you need in this case.

There are other options as well, for example SQL Server Data Tools has this functionality, though I haven't tested it to any degree that I could compare feature sets, performance, etc.

I've also blogged about why you want to use a tool and just pay for this functionality, rather than solve it programmatically yourself. The post also mentions a variety of alternatives if budget is a primary blocker:

http://bertrandaaron.wordpress.com/2012/04/20/re-blog-the-cost-of-reinventing-the-wheel/

Upvotes: 3

Related Questions