Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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
share|improve this question

6 Answers 6

up vote 4 down vote accepted

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

share|improve this answer
    
Is there something I can query to see the "Backup chain"? I have taken several full backups of this DB. –  andyhky May 19 '09 at 18:35
    
I edited my answer to include a couple of queries that might help you figure out what backups you have. –  Paul Randal May 19 '09 at 19:57

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?

share|improve this answer

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

share|improve this answer

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?

share|improve this answer
    
The transaction logs are backed up nightly Monday-Saturday. The Full backup takes place on Sundays. –  andyhky May 18 '09 at 21:14

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

share|improve this answer

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).

share|improve this answer
    
I'm not using log shipping and the full database backups are placed in the same folder. –  andyhky May 19 '09 at 13:15

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.