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?
Best Answer
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.