I am trying to copy a database from a remote server. I don't have access to its backups or file system. My destination database on my dev machine needs to get all the data plus the same tables structures including primary keys with identity settings and the rest of the constraints.
The Import/Export facility has the option to set the identity insert as shown below but I have to do this manually per table. I have over 300 tables to copy and most of them have identity primary keys. This is going to be very painful process doing the copy multiple times.
Is there a way to set this identity insert setting all at once for all the tables? Or some other reliable way of copying databases in such a setup?
I am using SSMS 7.3 against SQL Server 2014.
Best Answer
You can do all tables at once in the Import / Export wizard. On the Select Source Tables and Views Page you can select the tick box in the source bar, once selected you can select Edit Mappings and you'll see Enable Identity Insert at the bottom.