Microsoft KB Archive/928317

Knowledge Base

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

Article Last Modified on 11/20/2007

---

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

---

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

Keywords: kbinfo kbtshoot kbexpertiseadvanced kbsql2005engine KB928317

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.