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 FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAGatekeeper.mdf',SIZE = 20480KB,MAXSIZE = UNLIMITED,FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'Gatekeeper_log',FILENAME = N'E:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAGatekeeper.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 FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupGatekeeperGatekeeper_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 FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupGatekeeper'
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 FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupGatekeeperGatekeeper_backup_200905190812.trn' WITH  FILE = @backupSetId,NOREWIND
GO

解决方法

此外,如果您执行某些操作来破坏日志备份链,正如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;

D =数据库备份,L =日志备份,I =差异数据库备份.

有关’backupset’的联机丛书中的更多信息

希望这可以帮助

（编辑：揭阳站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!