October 16, 2003 at 1:22 pm
Hello,
I have a simple DTS package in which a dBase IV table populates a SQL Server 2000 table. The SQL Server 2000 table;
-The target table is not being replicated.
-The target table does not have any triggers.
-The target table has either 0 rows or no indexes.
I need to edit this DTS package so that the bulk insert is minimally logged. The package currently runs under I know that I can switch between recovery models in the DTS package for this, but how do I do it?
Where do I express 'TABLOCK' in the package? It looks to me like I should not run a Log backup after the bulk insert, but should backup the database. Is this correct?
What else should I consider when performing this bulk insert? I am revisiting the information in BOL about this.
Thanks for your help!
CSDunn
October 16, 2003 at 2:30 pm
You can switch between full and bulk-logged recovery models easily. It is not necessary to perform a full database backup after bulk copy operations complete under the Bulk-Logged Recovery model. Transaction log backups under this model capture both the log and the results of any bulk operations performed since the last backup, the Bulk-Logged Recovery model only allows the database to be recovered to the end of a transaction log backup when the log backup contains bulk changes. Point-in-time recovery is not supported.
Create Execute SQL Task in DTS package to run alter database yourdbanme set recovery BULK_LOGGED before you start load data and set it back with another Execute SQL Task to full recovery mode with alter database command.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply