"Replicate" sql structure between testing and staging on same database instance

Question

On my local development server I have a database set up which gets hooked into Entity-Framework and is then used for unit testing. Let's call it COMPANY\Database

On the same server (and same database instance) I then have another database - COMPANY\DatabaseStaging which has the exact same structure but persistent test data used for constructing a front end site around.

So, to hopefully clarify, the structure/flow is something like:

Create or modify a table:

COMPANY\Database.Product

Update entity framework, write some business logic, unit tests, etc against the table.

Manually mirror/replicate the changes:

COMPANY\DatabaseStaging.Product

Put in some slightly more persistent test data and do front end/ASP.NET stuff.

Now, this doesn't seem very elegant to be manually updating the COMPANY\DatabaseStaging database to make it reflect changes in COMPANY\Database. Is there a better way of doing it?

• Pondlife: This seems to be a question about automating deployment of SQL Server schema and code changes. There are many different ways to achieve that: use SSDT, use a commercial product, write your own deployment tools and scripts etc. If you can make your question more precise, you may get a better response.

Answer 1

There are many free tools - native and opensource available for comparing schema and data between 2 databases (on same or different servers):

1. Open DBDiff ==> This is on Codeplex and works great.
2. SQL Admin Studio ==> This is now a free tool.
3. Hidden Gem from SQL 2005 and up : tablediff.exe (you can find this in the COM directory of your SQL Server install folder) Compare schemas: Regular or Strict
4. Powershell - You have to write your own code or build up on existing ones
5. If you are using SQL Server 2012, SSDT is also an option.
6. I have not tried this one, but just to put it in place SqlDiffFramework

From my experience, its worth to invest in Redgate's Schema and data compare tools as they have commandline options and they integrate well with Powershell as well.

Answer 2

There are lots of great tools to do this. A great tool that gives a decent value for its price is xSQL Comparison Bundle which is developed by the company i work at. It contains xSQL Schema Compare which can be used to compare the schema's of two databases and then generate the scripts to synchronize the schemas. It also contains xSQL Data Compare which is used to compare the data in two databases.

Disclaimer: I'm affiliated to xSQL