Take the tour ×

Recently I started dealing with SQL Server, my previous experience was in MS-Access.

When I'm doing an import/export of a db, from the server to my computer or even in the server, all column with primary key loose the key. Identity is set to false and even bit is not set to the default.

How can I can I use an import/export job to make an exact copy of the db and its data? I don't want to have to perform a backup and restore every time I want the same db somewhere else, for another project, etc.

I have read about "edit mapping" and the checkbox but that did not helped with the identity specification... and what about the primary key of the tables and the rest of the things?

share|improve this question
Why do you think import/exüort is better than backup/restore? –  gbn Aug 28 '11 at 18:33
marc_s: i export from one sql server to another, duh! @gbn: i don't think so, it is just easier to export from one place to another without the need to backup, upload the backup file to the server and restore it –  Y.G.J Aug 28 '11 at 18:35
Again - that's not a method that will work most of the time. It will copy the data and the bare-bones structure - but none of the constraints, identity specs and so forth. That's the way it is. I would also recommend backup/restore, which WILL preserve your settings and everything. –  marc_s Aug 28 '11 at 18:40
@Y.G.J: you're new with SQL Server and know better then the folk who answer your questions? Interesting approach... –  gbn Aug 28 '11 at 19:24
@YGJ, meh, just use a SQL Data Compare or equivalent tool and it's a moot point. May as well get on with actual development –  Frankston Ralphington III Aug 28 '11 at 19:37
show 2 more comments

migrated from stackoverflow.com Aug 29 '11 at 7:35

This question came from our site for professional and enthusiast programmers.

2 Answers

up vote 1 down vote accepted

If you want (or need) to avoid backup/restore this is what I would recommend (these steps assuming you don't want to maintain the old schema NAME, just the structure) -

Download opendbdiff. Choose 'Compare' between source and (empty) destination. Choose sync. script tab and copy only the create table rows (without dbo.sysdiagrams tables etc.) paste into sql managment studio new query, delete all the schemas names appearing before the table names.

Now you have the full structure including primary keys, identity etc. Next step - use sql server import and export data like you did before (make sure you choose edit mappings and choose destination schema as dbo etc.). Also make sure you tick drop and recreate destination table.

share|improve this answer
add comment

If you use SQL Data Compare, your identity columns will be maintained.

This really is the best way to keep DB's synced up perfectly and it comes highly recommended by the Stackoverflow founders.

It is generally a losing battle to try to use the built-in Import / Export functionality in SSMS

share|improve this answer
add comment

Your Answer


