Log Shipping – NORECOVERY vs. STANDBY mode

This will be useful while migrating the data from one server to another

 

SET NOCOUNT ON
GO
SELECT @@SERVERNAME
DECLARE @path nvarchar(2000), @batchsize nvarchar(40), 
        @format nvarchar(40), @serverinstance nvarchar(200), 
        @security nvarchar(800)
 
SET @path = 'C:\Temp\';
SET @batchsize = '100000' -- COMMIT EVERY n RECORDS
SET @serverinstance = @@SERVERNAME --SQL Server \ Instance name
SET @security = ' -T ' -- -T (trusted), -Uloginid -Ploginpassword
 
--GENERATE CONSTRAINT NO CHECK
PRINT '--NO CHECK CONSTRAINTS'
SELECT 'ALTER TABLE ' + QUOTENAME( TABLE_NAME ) + ' NOCHECK CONSTRAINT ' 
+  QUOTENAME( CONSTRAINT_NAME )
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
 
--DISABLE TRIGGERS
PRINT '--DISABLE TRIGGERS'
SELECT 'ALTER TABLE ' + QUOTENAME( TABLE_NAME ) + ' DISABLE TRIGGER ALL'
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
 
--BCP-OUT TABLES
PRINT '--BCP OUT TABLES '
SELECT  'bcp "' + QUOTENAME( TABLE_CATALOG ) + '.' + QUOTENAME( TABLE_SCHEMA ) 
+ '.' + QUOTENAME( TABLE_NAME ) + '" out "' + @path + '' + TABLE_NAME + '.dat" -q -b"' 
+ @batchsize + '" -e"' + @path + '' + TABLE_NAME + '.err" -n -CRAW -o"' + @path + '' 
+ TABLE_NAME + '.out"  -S"' + @serverinstance + '" ' + @security + ''
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
 
-- CREATE NON-XML FORMAT FILE
PRINT '--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"' + @serverinstance + '" ' + @security + ''
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
 
-- CREATE XML FORMAT FILE
PRINT '--XML FORMAT FILE'
SELECT  'bcp "' +QUOTENAME( TABLE_CATALOG ) + '.' + QUOTENAME( TABLE_SCHEMA ) 
+ '.' + QUOTENAME( TABLE_NAME ) + '" format nul -x -n -CRAW -f "' 
+ @path + '' + TABLE_NAME + '.xml"  -S"' + @serverinstance + '" ' + @security + ''
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
 
--TRUNCATE TABLE
PRINT '--TRUNCATE TABLE'
SELECT 'TRUNCATE TABLE ' +QUOTENAME( TABLE_NAME ) + ' 
GO '
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
 
--BULK INSERT
PRINT '--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' 
 
--OPENROWSET
PRINT '--OPENROWSET'
SELECT DISTINCT 'INSERT INTO ' + QUOTENAME(TABLE_CATALOG) + '.' 
+ QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + ' 
    SELECT *
      FROM  OPENROWSET(BULK  ''' + @path + '' + TABLE_NAME + '.Dat'',
      FORMATFILE=''' + @path + '' + TABLE_NAME + '.Xml''
      ) as t1 ;
GO ' 
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
 
--GENERATE CONSTRAINT CHECK CONSTRAINT TO VERIFY DATA AFTER LOAD
PRINT '--CHECK CONSTRAINT'
SELECT 'ALTER TABLE ' + QUOTENAME( TABLE_NAME ) + ' CHECK CONSTRAINT ' 
+  QUOTENAME( CONSTRAINT_NAME ) 
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
 
--ENABLE TRIGGERS
PRINT '--ENABLE TRIGGERS'
SELECT 'ALTER TABLE ' + QUOTENAME( TABLE_NAME ) + ' ENABLE TRIGGER ALL'
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'