I wish to copy my Sql Server database to create a new one or to replicate it with some data and with the same identity columns and primary keys intact.
When I create the new database and import from the old database, I do click on the menu item 'Tasks>Import data .., and after selecting all the tables, I click the checkbox under 'Edit mapping' which says 'Enable identity insert', but it does not import those fields/columns which has those attributes to the new database.
Is there a robust way to select which table's' data I can replicate and how do I handle primary keys and identity fields.
I know how to TRUNCATE tables once it has been replicated to remove old data and reset the identity fields, but it does not allow me to change the relevant columns to having an identity field without recreating the tables, if it has been copied without the identity column.
What error do you get? When you enable identity insert, are the columns mapped properly or is destination = Ignore? The way you are copying the data should work. You can always make a backup of the entire DB and then restore it to a new destination. I assume this is for making a copy of prod to lower environments
I want to make a template copy with data that does not change from Db to Db in certain tables and others with no data.
I mark it for Enable entity insert, but the new database do not have that as an identity column.
So, are you having the import data wizard create the object for you? It will only create the table it needs to load data. It won't take into account Identity or PKs. you should have a script that creates your objects, then just use SSIS to load the data you need.
The first step would be to create a database with all of the objects - and no data, then you can populate the data for those tables that have static data.
You want to verify the clone so it can be used as a production database...once you have that you can then use the import/export wizard to populate the tables as needed.