Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?


locked by HopelessN00b Dec 5 '14 at 5:35

This question exists because it has historical significance, but it is not considered a good, on-topic question for this site, so please do not use it as evidence that you can ask similar questions here. This question and its answers are frozen and cannot be changed. More info: help center.

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).


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.


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)


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:

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.


Have you tried Microsoft's Database Publishing Wizard?
Disclaimer: I only used it on SQL 2005...don't know if it works with SQL 2008.


Hey, why don't you just try the "Import and Export Wizard" included with SQL Server 2008 (Start -> Programs -> Microsoft SQL Server 2008 -> Import and Export Wizard).

When selecting the tables make sure to Edit the Mappings between origin and destination, and to check the "Enable Identity Insert" option. This will allow you to keep your identity foreign keys untouched.


Not the answer you're looking for? Browse other questions tagged or ask your own question.