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.

enter image description here


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.

Select Source Tables and Views

Transfer Settings

  • Strange that I didn't notice it! – Tony_Henrich Nov 8 '17 at 19:10

You can use Dynamic sql to set it for all tables described here. If the db is large, then I would suggest you to script out schema and create the shell / skeleton DB and then use bcp out and bulk insert data into it. You can automate this using powershell or sql agent job.

ALternatively, you can just kick off a copy_only backup and use copy-dbadatabase (part of dbatools - powershell based) to backup and restore the db from source to destination.

  • Your suggestions involve access to the file system which I mentioned I don't have access to. – Tony_Henrich Nov 4 '17 at 21:50
  • You can run dynamic sql and then import data – Kin Shah Nov 4 '17 at 22:17
  • Only one identity_insert can be set in a session. – Tony_Henrich Nov 5 '17 at 1:02
  • How about creating linked server and doing insert ? Else you have to script out somehow and run the sql. – Kin Shah Nov 5 '17 at 1:19

I think you can do what you want by creating the import package using OLE DB source/destination components with the default identity insert. Save it to a package file instead of executing immediately. Open the package source in a text editor and globally change the FastLoadKeepIdentity property to true all destination tables. Then manually changes it to false for those tables without identity columns.

Below is an xml fragment of the package source that needs to be changed.

          description="Indicates whether the values supplied for identity columns will be copied to the destination. If false, values for identity columns will be auto-generated at the destination. Applies only if fast load is turned on."

Save the modified package and then run it using the dtexec.exe command-line utility or the dtexecui.exe graphical tool. That will allow you to easily repeat the copy without going through the import/export wizard again.

  • Thanks. I went by with my answer method. I will try yours soon and see how it goes. – Tony_Henrich Nov 5 '17 at 1:06

I used Toad to create the insert statements. It's a commercial product and has a free trial.

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.