SQL Server 2005 备份失败

2024-5-26 • tag-icon

backup sql-server-2005 maintenance

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

答案1

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

另外，如果您采取某些措施破坏了日志备份链，正如 UndertheFold 所提到的，您将需要使用另一个完整备份重新建立日志备份链。

[编辑] 您可以使用以下查询找到数据库上次备份的时间：

从 msdb.dbo.backupset 中选择 [backup_start_date]、[backup_end_date]，其中 [type] = 'D' 且 [database_name] = 'GateKeeper' 按 [backup_start_date] DESC 排序；

或者列出所有备份及其类型（自手动清除备份历史表以来）：

从 msdb.dbo.backupset 中选择 [backup_start_date]、[backup_end_date]、[type]，其中 [database_name] = 'GateKeeper' 按 [backup_start_date] DESC 排序；

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

有关“backupset”的更多信息，请参阅联机丛书

希望这可以帮助

答案2

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

答案3

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

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

答案4

我重复发了您的帖子，但您发布的日志中的错误表明数据库未在事务日志之前备份。维护计划是否包括备份数据库本身的任务？

相关内容