Select the product you need help with

You must perform a full database backup before you back up the transaction log for a database in SQL Server 2005 and in SQL Server 2000

Article ID: 928317 - View products that this article applies to.

SUMMARY

Before you try to back up the transaction log for a database in Microsoft SQL Server 2005, you must perform a full database backup. Otherwise, you receive error messages that indicate that the BACKUP LOG statement stops abnormally. You must also perform a full database backup before you try to back up the transaction log for a database in Microsoft SQL Server 2000. However, if you do not perform a full database backup before you back up the transaction log of a database in SQL Server 2000, the BACKUP LOG statement does not stop.

Back to the top | Give Feedback

INTRODUCTION

This article describes that you must perform a full database backup before you back up the transaction log for a database of SQL Server.

Back to the top | Give Feedback

MORE INFORMATION

In SQL Server 2005, you must perform a full database backup before you back up the transaction log for a database. Otherwise, when you try to back up the transaction log for a database, you receive the following error message:

Msg 4214, Level 16, State 1, Line 1
BACKUP LOG cannot be performed because there is no current database backup.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.

Additionally, messages that resemble the following are logged in the SQL Server Errorlog file:

2006-10-10 11:16:05.63 spid52 Starting up database 'SampleDatabase'.
2006-10-10 11:16:05.92 spid52 Setting database option RECOVERY to FULL for database SampleDatabase.
2006-10-10 11:16:06.41 Backup Error: 3041, Severity: 16, State: 1.
2006-10-10 11:16:06.41 Backup BACKUP failed to complete the command BACKUP LOG SampleDatabase. Check the backup application log for detailed messages.

In SQL Server 2000, if you try to back up a transaction log before you back up the full database, you only receive a warning message that resembles the following:

The CREATE DATABASE process is allocating 0.63 MB on disk 'SampleDatabase'.
The CREATE DATABASE process is allocating 0.49 MB on disk ' SampleDatabase_log'.
There is no current database backup. This log backup cannot be used to roll forward a preceding database backup.
Processed 1 pages for database ' SampleDatabase ', file ' SampleDatabase_log' on file 1. BACKUP LOG successfully processed 1 pages in 0.145 seconds (0.007 MB/sec).

Additionally, a successful backup is reported in the SQL Server Errorlog file. The text in the log file resembles the following:

2006-10-10 11:11:04.31 spid51 Starting up database ' SampleDatabase '.
2006-10-10 11:11:04.80 backup Log backed up: Database: SampleDatabase, creation date(time): 2006/10/10(11:11:04), first LSN: 8:23:1, last LSN: 8:25:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\TestTlog.trn'}).

The warning message indicates that you cannot use this transaction log backup for any restore operations if you do not have a full database backup.

Back to the top | Give Feedback