SQL 7.0 SP1 on NT 4 SP6a Cluster.
We have a DB with the option "select into/bulk copy" set to true.
When non-logged operations happens, the backup of the transaction log fail, so we do a differential backup instead and everything is ok.
My questions are:
UPGRADING to SQL 2K,
1 Do we have to choose the "Bulk-Logged" recovery model?
and if so,
2 Do we still need to run differential backup because backup of t-log will fail as on SQL 7.0?
Can somebody help me with this?
In BULK_LOGGED recovery mode certain bulk operations are only minimally logged increasing performance and decreasing log size. Performing builk operations do not require you to process backup, since they are actually logged thoughout BCM page. For every datafile there is one BCM page where one bit correspond to extent modification by minimally logged operation. When you perform tran log backup, sql scans BCM and backups these extents. As a result you are getting larger tranlog backups then in FULL mode.