Monday, December 21, 2009

transaction log backup issue

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.

1 comment:

  1. I have heard about another way of sql server 2000 corrupt. Besides, you can visit my blogs at: http://daspeac.livejournal.com/ or http://daspeac.blogspot.com/ where I’m trying to share my experience with regard to data corruption issues.

    ReplyDelete