SQL Server 2005备份失败

我有一个完全恢复模式的数据库。该数据库的事务日志备份（通过维护计划安排）每晚都会失败。

这里是数据库的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, NOUNLOAD, NOREWIND GO

除非有完整的数据库备份作为其“基础”，否则无法进行日志备份。如果您刚刚切换到完全恢复模式，那么在您进行第一次数据库备份之前，数据库并不存在 – 它仍然处于伪SIMPLE模式。

另外，如果你做了一些事情来打破日志备份链，就像UndertheFold提到的那样，你需要用另一个完全备份重新build立日志备份链。

[编辑]您可以使用此查询find最后一次数据库备份的时间：

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

或者列出所有备份及其types（自从手动清除备份历史logging以来）：

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

D =数据库备份，L =日志备份，I =差异数据库备份。

“联机丛书”中有关“备份集”的更多信息

希望这可以帮助

在备份日志之前，您必须执行完整备份。 http://support.microsoft.com/kb/928317

还有一个错误来源：如果将日志传送文件放入其他文件夹，则日志传送可能会干扰日志备份过程。

从上次完整数据库备份开始的完整日志备份序列必须位于相同的位置（文件夹）。

根据您发布的日志，事务日志不能备份，因为实际的数据库本身没有备份。在尝试备份事务日志之前是否备份了主数据库？

我张贴在您的重复post中，但是您发布的日志中的错误表示数据库在事务日志之前未被备份。维护计划是否包含备份数据库本身的任务？

这可能与正在执行的额外步骤有关，这里没有提到

使用truncate_only备份日志
或者您已经从FULL或BULK-LOGGED恢复模式切换到SIMPLE