我有一个完全恢复模型的数据库.此数据库的事务日志备份(通过维护计划安排)每晚失败.
这是数据库的CREATE语句等：
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
以下是维护计划的错误消息：
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.
以下是维护计划中的相关代码：
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,NOREWIND GO
解决方法
除非有完整的数据库备份作为其“基础”,否则无法进行日志备份.如果您刚刚切换到FULL恢复模型,那么在您进行第一次数据库备份之前,数据库并不存在 – 它仍处于伪SIMPLE模式.
此外,如果您执行某些操作来破坏日志备份链,正如UndertheFold所提到的那样,您需要使用另一个完整备份重新建立日志备份链.
[编辑]
您可以使用此查询找到数据库的最后一次数据库备份的时间：
SELECT [backup_start_date],[backup_end_date] FROM msdb.dbo.backupset
WHERE [type] =’D’
AND [database_name] =’GateKeeper’
ORDER BY [backup_start_date] DESC;
或列出所有备份及其类型(自备份历史记录表被手动清除以来)：
SELECT [backup_start_date],[backup_end_date],[type] FROM msdb.dbo.backupset
WHERE [database_name] =’GateKeeper’
ORDER BY [backup_start_date] DESC;
有关’backupset’的联机丛书中的更多信息
希望这可以帮助
