/*******************************************************************
this script will generate the bcp out commands for all data from the
users current connected database. The this script will only work if
both databases have the same ddl version, meaning same tables, same
columns same data definitions.
*******************************************************************/
SET NOCOUNT ON
GO
DECLARE @Path nvarchar(2000) = 'C:\Temp\' -- storage location for bcp dump (needs to have lots of space!)
, @Batchsize nvarchar(40) = '1000000' -- COMMIT EVERY n RECORDS
, @Xmlformat bit = 0 -- 1 for yes to xml format, 0 for not xml
, @SourceServerinstance nvarchar(200) = 'localhost' -- SQL Server \ Instance name
, @Security nvarchar(800) = ' -T ' -- options are -T (trusted), -Uloginid -Ploginpassword
, @GenerateDump bit = 0 -- 0 for storing data to disk, not 1 for loading from disk
, @FileGroup sysname = 'data'; -- Table filegroup that we are interested in
--> set output to text and execute the query, then copy the generated commands, validate and execute them
--------------------------------Do not edit below this line-----------------------------------------------------------------
DECLARE @filter TABLE(TABLE_NAME sysname)
INSERT INTO @filter (TABLE_NAME)
SELECT o.name
FROM sys.indexes as i
JOIN sys.objects as o on o.object_id = i.object_id
WHERE i.data_space_id = FILEGROUP_ID(@FileGroup)
AND i.type_desc ='CLUSTERED'
and o.name not like 'sys%'
order by 1
if(@GenerateDump=0)
begin
--BCP-OUT TABLES
SELECT 'bcp "' + QUOTENAME( TABLE_CATALOG ) + '.' + QUOTENAME( TABLE_SCHEMA )
+ '.' + QUOTENAME( TABLE_NAME ) + '" out "' + @path + '' + TABLE_NAME + '.dat" -q -b"'
+ @batchsize + '" -e"' + @path + 'Error_' + TABLE_NAME + '.err" -n -CRAW -o"' + @path + ''
+ TABLE_NAME + '.out" -S"' + @SourceServerinstance + '" ' + @security + ''
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME IN (SELECT TABLE_NAME FROM @filter)
if(@Xmlformat=0)
begin
print 'REM CREATE NON-XML FORMAT FILE '
SELECT 'bcp "' + QUOTENAME( TABLE_CATALOG ) + '.' + QUOTENAME( TABLE_SCHEMA ) + '.'+
QUOTENAME( TABLE_NAME ) + '" format nul -n -CRAW -f "' + @path + ''
+ TABLE_NAME + '.fmt" -S"' + @SourceServerinstance + '" ' + @security + ''
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME IN (SELECT TABLE_NAME FROM @filter)
end
else
begin
PRINT 'REM XML FORMAT FILE'
SELECT 'bcp "' +QUOTENAME( TABLE_CATALOG ) + '.' + QUOTENAME( TABLE_SCHEMA )
+ '.' + QUOTENAME( TABLE_NAME ) + '" format null -x -n -CRAW -f "'
+ @path + '' + TABLE_NAME + '.xml" -S"' + @SourceServerinstance + '" ' + @security + ''
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME IN (SELECT TABLE_NAME FROM @filter)
end
end
else
begin
print '--Make sure you backup your database first'
--GENERATE CONSTRAINT NO CHECK
PRINT '--NO CHECK CONSTRAINTS'
SELECT 'ALTER TABLE ' + QUOTENAME(TABLE_SCHEMA)+'.'+QUOTENAME( TABLE_NAME ) + ' NOCHECK CONSTRAINT ' + QUOTENAME( CONSTRAINT_NAME )
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME IN (SELECT TABLE_NAME FROM @filter)
PRINT '--DISABLE TRIGGERS'
SELECT 'ALTER TABLE ' + QUOTENAME(TABLE_SCHEMA)+'.'+QUOTENAME( TABLE_NAME ) + ' DISABLE TRIGGER ALL'
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME IN (SELECT TABLE_NAME FROM @filter)
--TRUNCATE TABLE
SELECT 'TRUNCATE TABLE ' +QUOTENAME(TABLE_SCHEMA)+'.'+QUOTENAME( TABLE_NAME ) + '
GO '
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME IN (SELECT TABLE_NAME FROM @filter)
--BULK INSERT
SELECT DISTINCT 'BULK INSERT ' + QUOTENAME(TABLE_CATALOG) + '.'
+ QUOTENAME( TABLE_SCHEMA ) + '.' + QUOTENAME( TABLE_NAME ) + '
FROM ''' + @path + '' + TABLE_NAME + '.Dat''
WITH (FORMATFILE = ''' + @path + '' + TABLE_NAME + '.FMT'',
BATCHSIZE = ' + @batchsize + ',
ERRORFILE = ''' + @path + 'BI_' + TABLE_NAME + '.ERR'',
TABLOCK);
GO '
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME IN (SELECT TABLE_NAME FROM @filter)
--GENERATE CONSTRAINT CHECK CONSTRAINT TO VERIFY DATA AFTER LOAD
PRINT '--CHECK CONSTRAINT'
SELECT 'ALTER TABLE ' + QUOTENAME(TABLE_SCHEMA)+'.'+QUOTENAME( TABLE_NAME ) + ' CHECK CONSTRAINT '
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME IN (SELECT TABLE_NAME FROM @filter)
SELECT 'ALTER TABLE ' + QUOTENAME(TABLE_SCHEMA)+'.'+ QUOTENAME( TABLE_NAME ) + ' ENABLE TRIGGER ALL'
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME IN (SELECT TABLE_NAME FROM @filter)
end