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.
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 How to obtain the latest SQL Server 2000 service pack
After you install the latest service pack for Microsoft SQL Server 2000, you must re-create the problematic DTS packages.
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 How to obtain the latest SQL Server 7.0 service pack
After you install the latest service pack for Microsoft SQL Server 7.0, you must re-create the problematic DTS packages.