For a while now, my company has been working to migrate from a VAX system to SQL Server 7. But until this task is complete, it is necessary to DTS data on a daily basis from the VAX. Because of this, and the large volume of data moved on a daily basis, our SQL databases were created to allow Select Into/Bulk Copy.
Because of this, we consistently have problems with transaction log backups failing (non-logged trans). I thought a solution would be to execute full backups daily, and differentials throughout the day. It appeared to be working, as the differentials were completing in about 10 minutes (scheduled for every 30 minutes). However, after 2 successful differentials, the job ran for an hour before I cancelled it. A co-worker later came to me to help determine why one of his jobs was failing. His job was one that DTS'd data from the VAX to SQL. It appears that while the differential backup did not cause any performance issues with users (no calls to helpdesk), there is a conflict with DTS using bulk copy. My guess is that his job was causing my backup to run long, and my backup was causing his job to fail. Aside from turning off select into/bulk copy, I am looking for a suggestion on how to backup our databases throughout the day. Any help would be greatly appreciated.