{"id":86210,"date":"2020-01-31T19:18:07","date_gmt":"2020-01-31T19:18:07","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=86210"},"modified":"2020-02-05T11:14:25","modified_gmt":"2020-02-05T11:14:25","slug":"copying-of-all-data-between-sql-server-databases-and-servers","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/copying-of-all-data-between-sql-server-databases-and-servers\/","title":{"rendered":"Copying of all data between SQL Server databases and servers"},"content":{"rendered":"

This script will copy out all the tables from one version of a database in native BCP format, and\u00a0 place them in a directory of your choice, but defaulting to your user area in a\u00a0 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.\u00a0 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.<\/p>\n

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.\u00a0 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.<\/p>\n

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<\/p>\n

To get started, fill in the connection string for your source of data $datasource<\/strong> and $dataTarget<\/strong>. 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<\/strong> but not the password. You will be asked the first\u00a0 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\u00a0 your PC, logged in with your credentials.<\/p>\n

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<\/strong> or $Datatarget<\/strong> as appropriate, (if you are just copying out, you make the target $null and if just copying in make the source $null.<\/p>\n

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<\/a> over the years.<\/p>\n

$Filepath = \"$env:USERPROFILE\\BCPFiles\" # local directory to save build-scripts to\r\n$DataSource = @{ 'ConnectionString' = 'Server=MySourceServer;Persist Security Info=False' }; # server name and instance\r\n$DataTarget = @{ 'ConnectionString' = 'Server=MyTargetServer;User Id=sa;Persist Security Info=False' }; # server name and instance\r\n\r\n$FileSourceDirectory = 'MyServer'<#if you are reading files in only, the script\r\nneeds to know the subdirectory of your root directory to use to get the right files#>\r\n\r\nif ($DataSource -eq $null -and $FileSourceDirectory -eq $null)\r\n{ write-error 'the script needs to know the subdirectory of your root directory to use' break; }\r\n\r\n\r\n$Databases = @(@{ 'source' = 'MyDatabase'; 'target' = 'MyNewDatabase' })\r\n$slash = '+' #the string that you want to replace for the 'slash' in an instance name for files etc\r\n\r\n# set \"Option Explicit\" to catch subtle errors\r\nset-psdebug -strict\r\n$ErrorActionPreference = \"stop\" # you can opt to stagger on, bleeding, if an error occurs\r\n#load the sqlserver module\r\n$popVerbosity = $VerbosePreference\r\n$VerbosePreference = \"Silentlycontinue\"\r\n# the import process can be very noisy if you are in verbose mode\r\nImport-Module sqlserver -DisableNameChecking #load the SQLPS functionality\r\n$VerbosePreference = $popVerbosity\r\n\r\nif (!(Test-Path -path $Filepath -PathType Container))\r\n{ $null = New-Item -ItemType directory -Path $Filepath }\r\n\r\n\r\n@($DataSource, $DataTarget) | where { $_ -ne $null } | foreach {\r\n\t$csb = New-Object System.Data.Common.DbConnectionStringBuilder\r\n\t$csb.set_ConnectionString($_.ConnectionString)\r\n\tif ($csb.'user id' -ne '') #then it is using SQL Server Credentials\r\n\t{ <# Oh dear, we need to get the password, if we don't already know it #>\r\n\t\t$SqlEncryptedPasswordFile = `\r\n\t\t\"$env:USERPROFILE\\$($csb.'user id')-$($csb.server.Replace('\\', $slash)).xml\"\r\n\t\t# test to see if we know about the password in a secure string stored in the user area\r\n\t\tif (Test-Path -path $SqlEncryptedPasswordFile -PathType leaf)\r\n\t\t{\r\n\t\t\t#has already got this set for this login so fetch it\r\n\t\t\t$SqlCredentials = Import-CliXml $SqlEncryptedPasswordFile\r\n\t\t\t\r\n\t\t}\r\n\t\telse #then we have to ask the user for it (once only)\r\n\t\t{\r\n\t\t\t#hasn't got this set for this login\r\n\t\t\t$SqlCredentials = get-credential -Credential $csb.'user id'\r\n\t\t\t$SqlCredentials | Export-CliXml -Path $SqlEncryptedPasswordFile\r\n\t\t}\r\n\t\t$_.ServerConnection =\r\n\t\tnew-object `\r\n\t\t\t\t   \"Microsoft.SqlServer.Management.Common.ServerConnection\"`\r\n\t\t($csb.server, $SqlCredentials.UserName, $SqlCredentials.GetNetworkCredential().password)\r\n\t\t$csb.Add('password', $SqlCredentials.GetNetworkCredential().password)\r\n\t}\r\n\telse\r\n\t{\r\n\t\t$_.ServerConnection =\r\n\t\tnew-object `\r\n\t\t\t\t   \"Microsoft.SqlServer.Management.Common.ServerConnection\" `\r\n\t\t($csb.server)\r\n\t}\r\n\t$_.csb = $csb\r\n\ttry # now we make an SMO connection to the server, using the connection string\r\n\t{\r\n\t\t$_.srv = new-object (\"Microsoft.SqlServer.Management.Smo.Server\") $_.ServerConnection\r\n\t}\r\n\tcatch\r\n\t{\r\n\t\tWrite-error \"Could not connect to SQL Server instance $($DataSource.csb.server) $($error[0]). Script is aborted\"\r\n\t\texit -1\r\n\t}\r\n} <# all this work just to maintain passwords ! #>\r\nif ($DataSource -ne $null)\r\n{\r\n\t$DirectoryToSaveTo = $DataSource.csb.server.Replace('\\', $slash)\r\n\tif ($DataSource.srv.Version -eq $null) { Throw \"Can't find the instance $($DataSource.csb.server)\" }\r\n\tWrite-verbose \"writing data out to $directoryToSaveTo\"\r\n\t$DataSource.srv.Databases[$Databases.source].Tables | Select Name, Schema |\r\n\tforeach{\r\n    <# calculate where it should be saved #>\r\n\t\t$directory = \"$($FilePath)\\$($DirectoryToSaveTo)\\$($Databases.Source)\\Data\"\r\n    <# check that the directory exists #>\r\n\t\tif (-not (Test-Path -PathType Container $directory))\r\n\t\t{\r\n      <# we create the  directory if it doesn't already exist #>\r\n\t\t\t$null = New-Item -ItemType Directory -Force -Path $directory;\r\n\t\t}\r\n\t\t$filename = \"$($_.Schema)_$($_.Name)\" -replace '[\\\\\\\/\\:\\.]', '-'\r\n\t\tWrite-Verbose \"Writing out $($_.Schema).$($_.Name) t0 $($directory)\\$filename.bcp\"\r\n\t\tIf ($DataSource.csb.'user id' -eq '')<# OK. Easy, a trusted connection #>\r\n\t\t{\r\n\t\t\t#native format -n, Trusted connection -T\r\n\t\t\t$Progress = BCP \"$($_.Schema).$($_.Name)\"  out  \"$($directory)\\$filename.bcp\"   `\r\n\t\t\t\t\t\t\t-n -T \"-d$($Databases.source)\"  \"-S$($DataSource.csb.server)\"\r\n\t\t}\r\n\t\telse <# if not a trusted connection we need to provide a userid and password #>\r\n\t\t{\r\n\t\t\t\r\n\t\t\t$Progress = BCP \"$($_.Schema).$($_.Name)\"  out  \"$($directory)\\$($_.Schema)_$($_.Name).bcp\"  `\r\n\t\t\t\t\t\t\t-n \"-d$($Databases.source)\"  \"-S$($DataSource.csb.server)\"  `\r\n\t\t\t\t\t\t\t\"-U$($DataSource.csb.'user id')\" \"-P$($DataSource.csb.password)\"\r\n\t\t}\r\n\t\t\r\n\t\tif (-not ($?) -or $Progress -like '*Error*') # if there was an error\r\n\t\t{\r\n\t\t\tthrow (\"Error with data export of $($directory)\\$($_.Schema)_$($_.Name).bcp - $Progress\");\r\n\t\t}\r\n\t}\r\n}\r\nif ($DataTarget -ne $null)\r\n{\r\n\tif ($DataSource -ne $null) { $DirectoryToLoadFrom = $DataSource.csb.server.Replace('\\', $slash) }\r\n\telse { $DirectoryToLoadFrom = $FileSourceDirectory }\r\n\tif ($DataTarget.srv.Version -eq $null) { Throw \"Can't find the instance $($DataTarget.csb.server)\" }\r\n\tIf ($DataTarget.srv.Databases[$Databases.target] -eq $null)\r\n\t{ Throw \"Can't find the database $($Databases.target) on instance $($DataTarget.csb.server)\" }\r\n\tWrite-verbose \"Reading data in from $DirectoryToLoadFrom\"\r\n\t\r\n\t$DataTarget.srv.Databases[$Databases.target].Tables | Select Name, Schema |\r\n\tforeach {\r\n\t\t# calculate where it gotten from #\r\n\t\t$directory = \"$($FilePath)\\$($DirectoryToLoadFrom)\\$($Databases.Source)\\Data\"\r\n\t\t$filename = \"$($_.Schema)_$($_.Name)\" -replace '[\\\\\\\/\\:\\.]', '-'\r\n\t\t$progress = '';\r\n\t\tWrite-Verbose \"Reading in $($_.Schema).$($_.Name) from $($directory)\\$filename.bcp\"\r\n\t\tif ($DataTarget.csb.'user id' -ne '')\r\n\t\t{\r\n\t\t\t$Progress = BCP \"$($Databases.target).$($_.Schema).$($_.Name)\" in \"$($directory)\\$filename.bcp\" -q -N -E `\r\n\t\t\t\t\t\t\t\"-U$($DataTarget.csb.'user id')\"  \"-P$($DataTarget.csb.password)\" \"-S$($DataTarget.csb.server)\"\r\n\t\t}\r\n\t\telse\r\n\t\t{\r\n\t\t\t$Progress = BCP \"$($Databases.target).$($_.Schema).$($_.Name)\" in `\r\n\t\t\t\t\t\t\t\"$($directory)\\$filename.bcp\" -q -N -T -E `\r\n\t\t\t\t\t\t\t\"-S$($DataTarget.csb.server)\"\r\n\t\t}\r\n\t\tif (-not ($?) -or $Progress -like '*Error*') # if there was an error\r\n\t\t{\r\n\t\t\tthrow (\"Error with data import  of $($directory)\\$($_.Schema)_$($_.Name).bcp - $Progress \");\r\n\t\t}\r\n\t}\r\n\ttry # now we make an SMO connection to the server, using the connection string\r\n\t{\r\n\t\t$DataTarget.srv.ConnectionContext.ExecuteNonQuery(\" use [$($Databases.target)]   EXEC sp_msforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all'\")\r\n\t}\r\n\tcatch\r\n\t{\r\n\t\tWrite-error \"there was a constraint error!  Script is aborted\"\r\n\t\texit -1\r\n\t}\r\n}<\/pre>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
This script will copy out all the tables from one version of a database in native BCP format, and\u00a0 place them in a directory of your choice, but defaulting to your user area in a\u00a0 directory called BCPFiles. They are placed in sub directories based on your server name and database name, just to keep……<\/p>\n","protected":false},"author":154613,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2,1],"tags":[],"coauthors":[6813],"class_list":["post-86210","post","type-post","status-publish","format-standard","hentry","category-blogs","category-uncategorized"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/86210","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/154613"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=86210"}],"version-history":[{"count":8,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/86210\/revisions"}],"predecessor-version":[{"id":86256,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/86210\/revisions\/86256"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=86210"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=86210"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=86210"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=86210"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}