SQL Server 2005备份失败

前端之家收集整理的这篇文章主要介绍了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,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;

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

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

希望这可以帮助

原文链接:https://www.f2er.com/mssql/80089.html

猜你在找的MsSQL相关文章