How best to script selected tables and data on SQL Server 2008?

Question

The SQL Server Management Studio has the ability generate scripts. The problem is that it breaks on identity columns. I can't find a link to this bug at the moment but Microsoft basically declared it as a "feature".

This is particularly important for:

• Seedning a development or new test environment; and
• Replicating data from production back to test or development.

Is there an easy solution to this that actually works?

Solution 1

Scriptio worked well with SQL Server 2005 and is from SQL Server MVP Bill Graziano. It looks like you'll have to do a touch up and recompile for SQL Server 2008, though.

If you're looking for 3rd party tools, Red Gate's SQL Compare is great (full disclosure - I'm a Friend of Red Gate).

Solution 2

These two Red Gate tools are very good for comparing differences between databases and scripting the structural, or data, differences.

Red Gate Sql Compare - for structure comparison and replication

Red Gate Sql Data Compare - for data comparison and replication

They sell a SQL bundle which includes both products I think.

We've used them extensively to setup development databases and copy the changes back and so on. You could also use the Data Compare one to sync data between databases.

Solution 3

I haven't fully reviewed this tool, but take a look at it. It's free when I downloaded it (it still is, i believe)

http://www.ssmstoolspack.com/Features.aspx

Solution 4

There's two parts to this question.

First, how do you copy/move data with identity fields?

If you're going to be doing this regularly between two or more servers, you need to set up their identity seeds to be different. For example, if you have two servers sharing a table with a small amount of records, you might set one up with an identity seed of 1, and the other with an identity seed of 1,000,000. One server will start its identity field at 1 and go up, and the other at the higher number. Of course, you still have to keep an eye on this to make sure you don't end up with overlapping records.

Then, when you want to copy data from one server to another, you prefix your inserts with the SET IDENTITY_INSERT command as referenced here:

http://msdn.microsoft.com/en-us/library/ms188059.aspx

Then you can temporarily disable the identity field so you can pump data from one server to another.

Second, how do you copy/move data in general?

There's a bunch of ways to do this:

• SQL Server replication - can automatically sync data between multiple servers. It's built into the product, and it's flexible, but it's a pain in the rear to set up and manage. It's not for syncing dev/test environments like you're looking for.
• Scripting with SQL Server Management Studio - works, but it lacks flexibility, and it's a manual pain in the rear too.
• Data/schema comparisons with 3rd party products - tools like Toad for SQL Server will compare schemas and data between two servers and bring 'em in sync. (Disclaimer: I work for Quest, the makers of Toad.)

If you're moving data between production & dev/test, then restore the production data onto your dev/test server as another database name, and then do your database syncs there. It'll be faster, it won't impact your production box, and if you do something wrong (like sync data the wrong way) it won't wreck production.

View more solutions