SQL Server 2005 Backup Failing

Question

I have a database in full recovery model. This database's transaction log backups (scheduled via a Maintenance Plan) fail nightly.

Here is the database's CREATE statement, etc:

USE [master]
GO
/****** Object:  Database [Gatekeeper]    Script Date: 05/18/2009 15:31:26 ******/
CREATE DATABASE [Gatekeeper] ON  PRIMARY 
( NAME = N'Gatekeeper_dat', FILENAME = N'F:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Gatekeeper.mdf' , SIZE = 20480KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'Gatekeeper_log', FILENAME = N'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Gatekeeper.ldf' , SIZE = 10240KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
 COLLATE SQL_Latin1_General_CP1_CI_AS
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'Gatekeeper', @new_cmptlevel=90
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [Gatekeeper].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [Gatekeeper] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [Gatekeeper] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [Gatekeeper] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [Gatekeeper] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [Gatekeeper] SET ARITHABORT OFF 
GO
ALTER DATABASE [Gatekeeper] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [Gatekeeper] SET AUTO_CREATE_STATISTICS ON 
GO
ALTER DATABASE [Gatekeeper] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [Gatekeeper] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [Gatekeeper] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [Gatekeeper] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [Gatekeeper] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [Gatekeeper] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [Gatekeeper] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [Gatekeeper] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [Gatekeeper] SET  DISABLE_BROKER 
GO
ALTER DATABASE [Gatekeeper] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [Gatekeeper] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [Gatekeeper] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [Gatekeeper] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [Gatekeeper] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [Gatekeeper] SET  READ_WRITE 
GO
ALTER DATABASE [Gatekeeper] SET RECOVERY FULL 
GO
ALTER DATABASE [Gatekeeper] SET  MULTI_USER 
GO
ALTER DATABASE [Gatekeeper] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [Gatekeeper] SET DB_CHAINING OFF 

Here is the error message from the maintenace plan:

Executing the query "BACKUP LOG [Gatekeeper] TO  DISK = N'C:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Backup\\Gatekeeper\\Gatekeeper_backup_200905180100.trn' WITH NOFORMAT, NOINIT,  NAME = N'Gatekeeper_backup_20090518010003', SKIP, REWIND, NOUNLOAD,  STATS = 10
" failed with the following error: "BACKUP LOG cannot be performed because there is no current database backup.
BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Here's the relevant code from the maintenance plan:

EXECUTE master.dbo.xp_create_subdir N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Gatekeeper'
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'Gatekeeper' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'Gatekeeper' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''Gatekeeper'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Gatekeeper\Gatekeeper_backup_200905190812.trn' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND
GO

Answer 1

You can't take log backups unless there is a full database backup as their 'base'. If you've just switched into the FULL recovery model, the database isn't really there until you take the first database backup - it remains in a pseudo-SIMPLE mode.

Also, if you do something to break the log backup chain, as UndertheFold mentioned, you'll need to re-establish the log backup chain with another full backup.

[Edit] You can find the time that the last database backup was taken of your database using this query:

SELECT [backup_start_date], [backup_end_date] FROM msdb.dbo.backupset WHERE [type] = 'D' AND [database_name] = 'GateKeeper' ORDER BY [backup_start_date] DESC;

or to list all backups and their types (taken since the backup history tables were manually cleared out):

SELECT [backup_start_date], [backup_end_date], [type] FROM msdb.dbo.backupset WHERE [database_name] = 'GateKeeper' ORDER BY [backup_start_date] DESC;

D = database backup, L = log backup, I = differential database backup.

More info in the Books Online for 'backupset'

Hope this helps

Answer 2

You must perform a full backup before backing up the log. http://support.microsoft.com/kb/928317

Answer 3

There is one more source of errors: Log shipping can interfere with log backup process if you put files for log shipping into another folder.

Full sequence of log backups starting from the last full database backup must be in the same place (folder).

Answer 4

According to the log you posted, the transaction logs cannot be backed up because the actual database itself is not backed up. Is the main database being backed up before you try to backup the transaction logs?

Answer 5

I posted in your duplicate post, but the error in the log you posted indicates that the database is not being backed up before the transaction logs are. Does the maintenance plan include a task to backup the database itself?

Answer 6

This could be related to extra steps being performed that are not mentioned here like

• backing up the Log with truncate_only

• or you have switched from FULL or BULK-LOGGED recovery mode to SIMPLE