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

除非有完整的數據庫備份作為其“基礎”，否則您不能進行日誌備份。如果您剛剛切換到 FULL 恢復模式，那麼在您進行第一次數據庫備份之前，數據庫並不真正存在 - 它仍處於偽 SIMPLE 模式。

此外，如果您採取措施破壞日誌備份鏈，正如 UndertheFold 所提到的，您需要使用另一個完整備份重新建立日誌備份鏈。

$$ Edit $$ 您可以使用以下查詢找到上次對數據庫進行數據庫備份的時間： 選擇

$$ backup_start_date $$,$$ backup_end_date $$從 msdb.dbo.backupset 在哪裡$$ type $$= ‘D’ 和$$ database_name $$= ‘GateKeeper’ ORDER BY$$ backup_start_date $$降序； 或列出所有備份及其類型（由於手動清除了備份歷史表）：

選擇

$$ backup_start_date $$,$$ backup_end_date $$,$$ type $$從 msdb.dbo.backupset 在哪裡$$ database_name $$= ‘GateKeeper’ ORDER BY$$ backup_start_date $$降序； D = 數據庫備份，L = 日誌備份，I = 差異數據庫備份。

線上圖書中有關“備份”的更多資訊

希望這可以幫助

引用自：https://serverfault.com/questions/9644