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?

7
  • 2
    Why do you think import/exüort is better than backup/restore?
    – gbn
    Aug 28, 2011 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, 2011 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, 2011 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, 2011 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 Aug 28, 2011 at 19:37

2 Answers 2

4

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

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .