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
image
BCP out Commands
image
BCP in Commands
 
image
Veirfy Errors : Generates Powershell Commands
image
 
 

/*
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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'

Post a Comment