Sunday, February 21, 2016

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'

2 comments:

  1. Thank you for putting up a descriptive post on a very useful aspect of SQL. This actually helped me a lot to understand this topic.

    SSIS PostgreSql Write

    ReplyDelete
  2. Website hosting is a service where organizations and individuals are allowed to make websites accessible in the WWW (World Wide Web). The companies which provide such space are called web hosts. The servers can be leased or owned by such companies. The web hosts give data center space as well as internet connectivity for servers that are within their own data center. This is referred to as colocation. https://onohosting.com/

    ReplyDelete

Featured Post

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 t...

Contributors