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 translations Article translations
Article ID: 928317 - View products that this article applies to.
Expand all | Collapse all

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.

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.

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.

REFERENCES

For more information, visit the following Microsoft Developer Network (MSDN) Web sites:
Backing up and restoring databases in SQL Server
http://msdn2.microsoft.com/en-us/library/ms187048.aspx

How to: Create a full database backup (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms191304.aspx

Properties

Article ID: 928317 - Last Review: November 20, 2007 - Revision: 1.5
APPLIES TO
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Workgroup Edition
  • Microsoft SQL Server 2005 Standard X64 Edition
  • Microsoft SQL Server 2005 Standard Edition for Itanium-based Systems
  • Microsoft SQL Server 2005 Enterprise X64 Edition
  • Microsoft SQL Server 2005 Enterprise Edition for Itanium-based Systems
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2000 Developer Edition
  • Microsoft SQL Server 2000 Enterprise Edition
  • Microsoft SQL Server 2000 Personal Edition
  • Microsoft SQL Server 2000, Workgroup Edition
Keywords: 
kbtshoot kbexpertiseadvanced kbsql2005engine kbinfo KB928317

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com