Copying of all data between SQL Server databases and servers

Comments 0

Share to social media

This script will copy out all the tables from one version of a database in native BCP format, and  place them in a directory of your choice, but defaulting to your user area in a  directory called BCPFiles. They are placed in sub directories based on your server name and database name, just to keep things neat. It will then if you wish, copy it out to a target server.  On first glance, this routine may seem over-complicated but it is designed to be able to perform this task for a list of these pairs of source & target databases for multi-database applications if you need to do that.

If the script is used to copy this data from one database to another, it will need the target to be an empty version of the same database (same meaning with the same table schema) with no data in it.  If the two versions have a different table structure you’ll get an error. If you use this system and you change the table structure, or constraints, you would need to run your migration code on the source until it is the same as the target.

BCP must be installed to run this. This comes with SSMS so you probably have it already. Sometimes you need to create an alias for BCP but I think that problem has gone away

To get started, fill in the connection string for your source of data $datasource and $dataTarget. You also need to fill in an array of objects, each of which define your source and target databases, so you can do a whole list of databases. If you have to use credentials rather than integrated windows security, you add the userID but not the password. You will be asked the first  time you run the routine for your password and this is then stored as an encrypted string in a file in your user area protected by NTFS security. Be warned, though, that you shouldn’t allow anyone else to run this using  your PC, logged in with your credentials.

if you don’t want to do both the copying out and the copying in together in a single operation, you merely assign a $null to either the $DataSource or $Datatarget as appropriate, (if you are just copying out, you make the target $null and if just copying in make the source $null.

This script uses SMO in order to get the list of tables from the database and command-line BCP to do the copying of the data. I’ve published other ways of doing this over the years.