SQL Server AWS Migration BCP

stored procedure to generate BCP scritps to migrate the SQL Server database.
Developed this stored procedure on my labs to simulate the Migrating databases to AWS RDS.

Execute the output of the below procedure in CMD window.

Output :

creates Folders to store the output data

creates Folders to log the errors

BCP out Commands

BCP in Commands

Veirfy Errors : Generates Powershell Commands

/* ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 1) Create this sp on source Example : Exec  AWS_BCP_MIGRATION 'C:\BCP_DATA' , 'my_source_server', 'my_database', 'my_dest_server', 'my_dest_database', 'my_sa_password', 0 ------------ -- next run , truncate these tables manually. dont want to disturb. if there is any tables already exists in your environment TRUNCATE TABLE MSDB..AWS_IMPORT TRUNCATE TABLE MSDB..AWS_IMPORT ------------ BCP Parameters S -- server T -- trusted connection U - username P - password n - native values , existing values E - keep identity ( this is very important in migration) w - unicode t - column delimiter / field terminator b - batch size e - error log file "TABLOCK" - for performance */ --------------------------------------------------------------------------------------------------------------------------------------------------------------------- ALTER proc AWS_BCP_MIGRATION ( @RootFolder varchar(max) ,@SourceServer varchar(max)  = NULL ,@SourceDB varchar(max) = NULL ,@DestServer varchar(max) ,@DestDB varchar(max) ,@DEST_SQL_SA_PASSWORD VARCHAR(256) ,@EXP_IMP_EACH_TABLE BIT = 0 ) AS IF @SourceServer IS NULL SET @SourceServer = @@SERVERNAME IF @SourceDB IS NULL SET @SourceDB = DB_NAME() SET NOCOUNT ON DECLARE @BCP_DATA_FOLDER VARCHAR(MAX) DECLARE @BCP_EXPORT_ERROR_FOLDER VARCHAR(MAX) DECLARE @BCP_IMPORT_ERROR_FOLDER VARCHAR(MAX) DECLARE @BCP_EXPORT_Logging_FOLDER VARCHAR(MAX) DECLARE @BCP_IMPORT_Logging_FOLDER VARCHAR(MAX) ---------------------------------------------------------------------------------------------------------------------------------------------------------- IF @SourceServer = @DestServer AND @SourceDB = @DestDB BEGIN RAISERROR ('Source and Dest Connections Cant be same',18,1) return END set @RootFolder  = @RootFolder  +'_'++REPLACE(DB_NAME(),' ','_') DECLARE @BCP_COL_DELIMITER VARCHAR(10) SET @BCP_COL_DELIMITER = '[@*#%^|$' ---------------------------------------------------------------------------------------------------------------------------------------------------------- set @BCP_DATA_FOLDER = @RootFolder  +'\BCP_DATA' SET @BCP_EXPORT_ERROR_FOLDER = @RootFolder   +'\LOGS\EXPORT_ERRORS' SET @BCP_IMPORT_ERROR_FOLDER = @RootFolder   +'\LOGS\IMPORT_ERRORS' SET @BCP_EXPORT_Logging_FOLDER = @RootFolder   +'\LOGS\EXPORT_LOGGING' SET @BCP_IMPORT_Logging_FOLDER = @RootFolder   +'\LOGS\IMPORT_LOGGING' SELECT 'MKDIR '+@BCP_DATA_FOLDER union all SELECT 'MKDIR '+@BCP_EXPORT_ERROR_FOLDER union all SELECT 'MKDIR '+@BCP_IMPORT_ERROR_FOLDER union all SELECT 'MKDIR '+@BCP_EXPORT_Logging_FOLDER union all SELECT 'MKDIR '+@BCP_IMPORT_Logging_FOLDER union all SELECT '' union all SELECT 'ECHO FOLDERS CREATED..' union all SELECT '' IF @EXP_IMP_EACH_TABLE  = 1     BEGIN             select IDENTITY(int,1,1) as iid, ' bcp "select * from '+@SourceDB +'.['+schema_name(schema_id) +'].['+ name  +'] " queryout '+@BCP_DATA_FOLDER+'\'+schema_name(schema_id)+'_'+REPLACE(NAME,' ','_')+'.txt -n -S '+@SourceServer+' -T -E -w -k -t'+@BCP_COL_DELIMITER+' -b 10000 -e'+@BCP_EXPORT_ERROR_FOLDER+'\'+schema_name(schema_id)+'_'+REPLACE(NAME,' ','_') +'.TXT >> '+@BCP_EXPORT_Logging_FOLDER+'\'+REPLACE(NAME,' ','_')+'.TXT'             +CHAR(13) + ' bcp '+@DestDB+'.['+schema_name(schema_id) +'].['+ name  +'] in '+@BCP_DATA_FOLDER+'\'+schema_name(schema_id)+'_'+REPLACE(NAME,' ','_')+'.txt -n -S '+@DestServer+' -Usa -P'+@DEST_SQL_SA_PASSWORD +'-h TABLOCK -E -w -k -t'+@BCP_COL_DELIMITER+' -b 10000 -e'+@BCP_IMPORT_ERROR_FOLDER+'\'+schema_name(schema_id)+'_'+REPLACE(NAME,' ','_') +'.TXT >> '+@BCP_IMPORT_Logging_FOLDER+'\'+REPLACE(NAME,' ','_')+'.TXT '             as [ECHO EXPORT BEGIN...]             INTO MSDB..AWS_EXPORT             from sys.tables              SELECT 'ECHO Export & Import Completed'             --- to verify the errors             PRINT 'Powershell.exe -noexit -command "get-childitem '+@RootFolder+'\LOGS\'+'*.TXT -recurse | select-string -pattern ''error''" >> '+@RootFolder+'\'+@DestDB+'_'+'BCP_status.txt'             PRINT 'Powershell.exe -noexit -command "get-childitem '+@RootFolder+'\LOGS\'+'*.TXT -recurse | select-string -pattern ''#@ Row''" >> '+@RootFolder+'\'+@DestDB+'_'+'BCP_status.txt'     RETURN     END select IDENTITY(int,1,1) as iid, ' bcp "select * from '+@SourceDB +'.['+schema_name(schema_id) +'].['+ name  +'] " queryout '+@BCP_DATA_FOLDER+'\'+schema_name(schema_id)+'_'+REPLACE(NAME,' ','_')+'.txt -n -S '+@SourceServer+' -T -E -w -k -t "'+@BCP_COL_DELIMITER+'" -b 10000 -e'+@BCP_EXPORT_ERROR_FOLDER+'\'+schema_name(schema_id)+'_'+REPLACE(NAME,' ','_') +'.TXT >> '+@BCP_EXPORT_Logging_FOLDER+'\'+REPLACE(NAME,' ','_')+'.TXT' as [ECHO EXPORT BEGIN...] INTO MSDB..AWS_EXPORT from sys.tables  SELECT [ECHO EXPORT BEGIN...] FROM  MSDB..AWS_EXPORT ORDER BY IID ASC SELECT 'ECHO EXPORT COMPLETE' ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- select ' bcp '+@DestDB+'.['+schema_name(schema_id) +'].['+ name  +'] in '+@BCP_DATA_FOLDER+'\'+schema_name(schema_id)+'_'+REPLACE(NAME,' ','_')+'.txt -n -S '+@DestServer+' -Usa -P'+@DEST_SQL_SA_PASSWORD +' -E -w -k -t "'+@BCP_COL_DELIMITER+'" -b 10000 -e'+@BCP_IMPORT_ERROR_FOLDER+'\'+schema_name(schema_id)+'_'+REPLACE(NAME,' ','_') +'.TXT >> '+@BCP_IMPORT_Logging_FOLDER+'\'+REPLACE(NAME,' ','_')+'.TXT "TABLOCK" ' as [ECHO IMPORT BEGIN..] ,IDENTITY(int,1,1) as iid into MSDB..AWS_IMPORT from sys.tables a SELECT [ECHO IMPORT BEGIN..] FROM MSDB..AWS_IMPORT ORDER BY IID ASC SELECT 'ECHO IMPORT COMPLETE' --get-childitem H:\BACKUP\AWS_BCP_DATA_RP_Regression\DATA\EXPORT_LOGGING\*.TXT -recurse | select-string -pattern "error" --- to verify the errors select  'Powershell.exe -noexit -command "get-childitem '+@RootFolder+'\LOGS\'+'*.TXT -recurse | select-string -pattern ''error''" >> '+@RootFolder+'\'+@DestDB+'_'+'BCP_status.txt' select 'Powershell.exe -noexit -command "get-childitem '+@RootFolder+'\LOGS\'+'*.TXT -recurse | select-string -pattern ''#@ Row''" >> '+@RootFolder+'\'+@DestDB+'_'+'BCP_status.txt'