Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

6 Answers 6

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

share|improve this answer

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.

share|improve this answer

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

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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