BCP multiple files in/out using Azure Active Directory Interactive

user_11069202 0
Viewed 160

I am trying to export/import tables using bcp, I am able to do it one by one as the documentation says using Azure Active Directory Interactive (I require Multifactor authentication to access the DB), by this method, it prompts an external window to set my credentials, so I should do this action for all the "IN" and "OUT" for all the tables (more than 20 and can increase in the future). Is there any way to avoid re-enter my credentials and execute export and import in one shot?

Example of BCP script:

bcp bcptest out "c:\last\data1.dat" -c -t -S aadserver.database.windows.net -d testdb -G -U alice@aadtest.onmicrosoft.com

Note: I know that script can be created by query, but again, my issue is to re-enter credentials for each file.

select 'bcp dbo.' + st.name + ' out c:\' + st.name + '.dat -c -t -S ' + 'aadserver.database.windows.net -d '  +    DB_NAME()   + ' -G -U alice@aadtest.onmicrosoft.com' from sys.tables st
 
select 'bcp dbo.' + st.name + ' in c:\' + st.name + '.dat -c -t -S ' + 'aadserver.database.windows.net -d '  +   'newdb'    + ' -G -U v-alice@aadtest.onmicrosoft.com' from sys.tables st
azure-active-directory sqlbulkcopy bcp sql-server
1 Answers

Unfortunately, I was not able to do it via Azure Active Directory Interactive, I had to ask for the user and password credentials, which allows me full access, so I created the next Power Shell script:

#Source DB params
$dbServerSource = "serversource.database.windows.net"
$dbDatabaseSource = "dbsource"
$dbUserSource = "Data Base User"
$dbPasswordSource = "'password  - do not remove the quotes'"
#Target DB params [example params]
$dbServerTarget = "newServer.database.windows.net"
$dbDatabaseTarget = "NewDB"
$dbUserTarget = "NewUser"
$dbPasswordTarget = "'password - do not remove the quotes'"


#SQL Connection - connection to SQL server
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $dbServerSource; Database = $dbDatabaseSource; User ID = $dbUserSource; Password = $dbPasswordSource;"
$SqlConnection.Open()

#SQL Command - set up the SQL call
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "select  st.name  from sys.tables st where st.name not in('sysdiagrams');"
$SqlCmd.Connection = $SqlConnection

#SQL Adapter - get the results using the SQL Command
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
#Close SQL Connection
$sqlConnection.Close();


foreach ($Row in $DataSet.Tables[0].Rows) { 
   $table = "$($Row[0])" 
   write-Output "--------------Crteate templates " + $table + "---------------"
   $psCommand = 'bcp dbo.' + $table + ' format nul -c -x -f ' + $table + '.xml  -S ' + $dbServerSource  + " -d " + $dbDatabaseSource + " -U " + $dbUserSource +  " -P " + $dbPasswordSource
   Invoke-Expression $psCommand
   write-Output "--------------Extract Data " + $table + "---------------"
   $psCommand = 'bcp dbo.' + $table + ' out ' + $table + '.dat -E -f ' + $table + '.xml -e ' + $table + 'out_logout.log -S ' + $dbServerSource  + " -d " + $dbDatabaseSource + " -U " + $dbUserSource +  " -P " + $dbPasswordSource
   Invoke-Expression $psCommand
   write-Output "--------------Import Data " + $table + "---------------"
   $psCommand = 'bcp dbo.' + $table + ' in ' + $table + '.dat -E  -f ' + $table + '.xml -e ' + $table + 'in_logout.log -S '  + $dbServerTarget  + " -d " + $dbDatabaseTarget + " -U " + $dbUserTarget +  " -P " + $dbPasswordTarget
   Invoke-Expression $psCommand
}

This script does the next steps:

  • Connect to the source DB
  • Get the tables (you can exclude or include tables in the select)
  • Execute bcp commands for each table:
    • Get source templates
    • Get source data
    • Migrate source data to target
user_110692020
Related