FIX: DTS Copy Objects Task (DMO) breaks transaction log backup chain by switching recovery mode to Simple during transfer

View products that this article applies to.

This article was previously published under Q308267

BUG #: 355050 (SHILOH_BUGS)
BUG #: 102033 (SQLBUG_70)

SYMPTOMS

The DTS Copy SQL Server Objects task (which is the Copy objects and data between SQL Server databases option in the DTS wizard) is a wrapper around the Distributed Management Objects (DMO) Transfer Object. The task creates a script of the selected objects, uses the bulk copy program (BCP) to move the data out of the source database, creates the objects on the destination database, and then uses BCP to move the data in.

In SQL Server 2000, prior to moving the data into the destination database, the recovery mode is changed to simple. This allows the BCP operation to be minimally logged to improve performance and prevents the transaction log from filling up. After BCP is completed, the recovery mode is changed back to the previous setting. In SQL Server 7.0, this is equivalent to setting truncate log on checkpoint on and then setting it off after the BCP is completed.

This sequence breaks the transaction log backup chain. If you back up the log using Query Analyzer after the recovery model has been switched, you receive error message 4214 and the backup will still complete successfully:

There is no current database backup. This log backup cannot be used to roll forward a preceding database backup. Processed X pages for database 'dbname', file 'dbname_log' on file 2. BACKUP LOG successfully processed X pages in X.XXX seconds (X.XXX MB/sec).

However, a database maintenance plan, log shipping, or a scheduled job that executes a transaction log backup succeeds without posting the above error. Closer examination of the errorlog shows there is a gap between the last log sequence number (LSN) of the backup just prior to the task executing and the first LSN in the next backup as shown in the following example:

2001-01-11 20:21:20.48 backup
Log backed up: Database: TEST01, creation date(time): 2001/01/11(16:32:20), first LSN: 6:123:1, last LSN: 6:125:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'TestBack01'}).

2001-01-11 20:21:47.76 backup
Log backed up: Database: TEST01, creation date(time): 2001/01/11(16:32:20), first LSN: 6:142:1, last LSN: 6:144:1, number of dump devices: 1, device information: (FILE=2, TYPE=DISK: {'TestBack01'}).

When you attempt to restore the transaction log taken immediately after the task was executed, you receive error message 4305:

The log in this backup set begins at LSN %.*ls, which is too late to apply to the database. An earlier log backup that includes LSN %.*ls can be restored.

Attempting to use the Copy SQL Server Objects task to just script the objects from the source to the destination database without moving the data also results in a break of the transaction log backup chain. The scripting task performs a number of dump tran with no_log commands as it is creating the objects on the destination database. This is recorded in the errorlog as follows:

2001-01-11 20:50:31.32 backup Database log truncated: Database: TEST01.

Back to the top

RESOLUTION

SQL Server 2000

To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 (http://support.microsoft.com/kb/290211/ ) How to obtain the latest SQL Server 2000 service pack

Note After you install the latest service pack for Microsoft SQL Server 2000, you must re-create the problematic DTS packages.

Back to the top

SQL Server 7.0

To resolve this problem, obtain the latest service pack for Microsoft SQL Server 7.0. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

301511 (http://support.microsoft.com/kb/301511/ ) How to obtain the latest SQL Server 7.0 service pack

Note After you install the latest service pack for Microsoft SQL Server 7.0, you must re-create the problematic DTS packages.

Back to the top

WORKAROUND

You can work around this problem in the following ways:

Take a log backup just prior to executing a package that uses the Copy SQL Server Objects task. After executing the package, perform a full database backup before resuming transaction log backups.

-or-
Create a DTS package that executes a create script of the desired objects and then use the transform data task to move the data between the databases.

-or-
Use the copy database wizard to copy the database to the desired location. Drop any unwanted objects after the copy is complete.

-or-
Restore the database and transaction logs to a new database in the desired location. Drop any unwanted objects after the copy is complete.

Back to the top

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

SQL Server 2000
This problem was first corrected in Microsoft SQL Server 2000 Service Pack 3.

Back to the top