Log Shipping – NORECOVERY vs. STANDBY mode

This is another great feature that was introduced in sql server 2008. This is one of the enhanced features of ‘SELECT INTO ‘ statements. All you need to do is to make sure that the following conditions are met

1. ensure that the database recovery model is bulk-logged / simple
2. enable the Trace flag 610.
3. insert the data ordered by the clustered index

So what is exactly happening behind the scenes ? Initial inserts may be fully logged if the data pages they are filling already contain data, but however any new data pages added to the table will be minimally logged as long as the above 3 criteria are satisfied.

Here is an example

   1: DBCC TRACEON (610)

   2: INSERT INTO TargetTable

   3: SELECT * 

   4: FROM sourceTable

   5: ORDER BY <ClusteredIndex>

   6: DBCC TRACEOFF (610)