5

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?

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

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

  • 2
    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
  • 2
    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
  • 1
    @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
  • 1
    @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 – Brian Webster Aug 28 '11 at 19:37
3

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.

3

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

  • Their 14 day trial saved me, going to make my department buy us licenses! OT: Funny seeing George W Bush talking about losing battles ;) – Niclas Lindqvist Apr 1 '15 at 5:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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