|
|
| http://www.sqlsky.com/ |
|
|
|
一、为什么要备份数据库?
在现实IT世界里，我们使用的服务器硬件可能因为使用时间过长，而发生故障；
Windows系列服务器有可能蓝屏或者感染病毒；SQL Server数据库也可能因为误操作或Bug而停止运行。
如何有效备份SQL Server数据库，避免故障真正发生时长时间的宕机，是每个系统治理员必须面对的任务。
二、简单实现Standby Sql Server 数据库的原理
我这里介绍一种不需要多大硬件投入（只需一台专用或兼用备份服务器）的Standby SQL Server的简单配置和使用方法。
数据库完全备份和日志备份文件通过Msdos下xcopy命令从工作环境复制到备份环境（比在SQL Server里设置日志转移方法要简单得多），备份环境再根据xcopy过来的备份文件设定作业（执行一些存储过程）来完成自动恢复操作。
假如意外发生时，这样的备份体系当然还需要人为地来干预和恢复（如改变备份机器的IP地址和主机名或更改应用程序的连接数据库参数等），会丢失一些数据也在所难免。
下面是我的测试环境Standby SQL Server备份体系图：
三、备份和恢复案例介绍
首先我们要了解系统所能承受的最长宕机时间是多少（假如是1小时），能承受的数据丢失最多是多少（假如是30分钟），用它来定下备份和恢复的目标：
工作环境下的某一个SQL Server数据库（假如是db_test）必须设置成完全故障还原模式；
然后在数据库维护计划里设定天天凌晨四点做一次完全数据库备份(天天从0:00开始，每20分钟做一次数据库日志文件的备份，直到23:59分)；
备份目录下只保留最近一天内的完全备份和日志备份文件；并把此目录共享。
备份环境下的服务器在[控制面板]->[任务计划]里添加一个天天0:05分开始，每20分钟执行一次的xcopy局域网上备份目录下最新文件的任务，直到23:59分。
xcopy \192.168.0.１db_test_backup F:ackup_datadb_test /c /y /d /s
备份服务器上SQL Server根据复制过来的备份文件，也天天0:10分开始，每20分钟执行一次由旧到新，逐一恢复数据库的作业（调用我改写的过程sp_RestoreDir实现）；
另外还有一个删除备份服务器两天前备份文件的作业（调用我写的过程p_delete_db_test_backup实现），避免备份硬盘扇区被装满。
过程sp_RestoreDir的源代码：
if exists (
select *
from dbo.sysobjects
where id = object_id(N'[dbo].[sp_RestoreDir]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_RestoreDir]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/***************************************************************************************/
-- 目 的: 根据某个目录下某个数据库的备份文件(*.trn或*.bak都可以)还原数据库.
-- 配合xcopy命令，可以在另一台备份机器上实现standby SQL Server数据库.
--
-- 输入参数: @restoreFromDir - 存放*.trn或*.bak数据库备份文件的目录
-- @restoreToDataDir - 数据库数据文件将要还原的目录
-- @restoreToLogDir - 数据库日志文件将要还原的目录, 假如为空,
-- 日志文件和数据文件目录相同
--
-- Written By: Chris Gallelli -- 8/22/2003
-- Modified By: Bruce Canaday -- 11/04/2003
-- http://www.sqlservercentral.com/scripts/contributions/962.asp
-- Modified By: maggiefengyu@tom.com --- 02/23/2005
--
-- 调用举例: exec sp_RestoreDir 'F:ackup_datadb_test', 'E:sqlserver_datadb_test'
/***************************************************************************************/
CREATE proc sp_RestoreDir
@restoreFromDir varchar(255),
@restoreToDataDir varchar(255)= null,
@restoreToLogDir varchar(255) = null
as
--If a directory for the Log file is not supplied then use the data directory
If @restoreToLogDir is null
set @restoreToLogDir = @restoreToDataDir
set nocount on
declare @filename varchar(40),
@cmd varchar(500),
@DataName varchar (255),
@LogName varchar (255),
@LogicalName varchar(255),
@PhysicalName varchar(255),
@Type varchar(20),
@FileGroupName varchar(255),
@Size varchar(20),
@MaxSize varchar(20),
@restoreToDir varchar(255),
@DBName varchar(255),
@PhysicalFileName varchar(255),
@i_exist int
create table #dirList (id [int] IDENTITY (1, 1) NOT NULL , filename varchar(100))
create table #filelist (LogicalName varchar(255), PhysicalName varchar(255),
Type varchar(20), FileGroupName varchar(255), Size varchar(20), MaxSize varchar(20) )
--Get the list of database backups that are in the restoreFromDir directory order by date desc
select @cmd = 'dir /b /o-d /o-g "' +@restoreFromDir+ '"'
set @i_exist=0
insert into #dirList(filename) exec master..xp_cmdshell @cmd
-- 找到备份目录下次新的2个文件名, 不处理最新的, 避免最新备份好的物理文件没有完全复制成功
select filename from #dirList where id>1 and id<8 order by id desc
begin
declare BakFile_csr cursor for
select filename from #dirList where id>1 and id<8 order by id desc
end
open BakFile_csr
fetch BakFile_csr into @filename
while @@fetch_status = 0
begin
-- 判定恢复日志表restore_log存在否，不存在则创建表
if not exists (
select *
from dbo.sysobjects
where id = object_id('restore_log')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
create table restore_log (filename varchar(128),dt datetime default getdate())
end
-- 判定此备份文件有无在恢复日志表里记录过?
select @i_exist=count(0) from restore_log where filename=@filename
if @i_exist=0
begin
-- 根据备份目录下的物理文件名, 找到对应的逻辑名等参数
select @cmd = "RESTORE FILELISTONLY FROM disk = '"
+ @restoreFromDir + "" + @filename + "'"
insert #filelist exec ( @cmd )
-- select * from #filelist
if right(@filename,3)='TRN'
begin
select @dbName = left(@filename,datalength(@filename)
- patindex('%_golt_%',reverse(@filename))-5)
select @cmd = "RESTORE Log " + @dbName +
" FROM DISK = '" + @restoreFromDir + "" + @filename +
"' WITH STANDBY='"+@restoreToDataDir+"UNDO_"+@filename+".DAT ',"
print ''
print '--RESTORING Log ' + @dbName
end
if right(@filename,3)='BAK'
begin
select @dbName = left(@filename,datalength(@filename)
- patindex('%_bd_%',reverse(@filename))-3)
select @cmd = "RESTORE DATABASE " + @dbName +
" FROM DISK = '" + @restoreFromDir + "" + @filename +
"' WITH NORECOVERY ,"
print ''
print '--RESTORING DATABASE ' + @dbName
end
-- 找到数据库逻辑和物理文件名称之间的对应关系
declare DataFileCursor cursor for
select LogicalName, PhysicalName, Type, FileGroupName, Size, MaxSize
from #filelist
open DataFileCursor
fetch DataFileCursor into @LogicalName, @PhysicalName, @Type,
@FileGroupName, @Size, @MaxSize
while @@fetch_status = 0
begin
-- RESTORE with MOVE option
select @PhysicalFileName = reverse(substring(
reverse(rtrim(@PhysicalName)),1,patindex('%\%',
reverse(rtrim(@PhysicalName)))-1 ))
select @restoreToDir = @restoreToDataDir
select @cmd = @cmd +
" MOVE '" + @LogicalName + "' TO '" +
@restoreToDir + "" + @PhysicalFileName + "', "
fetch DataFileCursor into @LogicalName, @PhysicalName,
@Type, @FileGroupName, @Size, @MaxSize
end -- DataFileCursor loop
close DataFileCursor
deallocate DataFileCursor
select @cmd = @cmd + ' REPLACE'
print @cmd
print ''
select @cmd
EXEC (@cmd)
IF @@ERROR=0
BEGIN
-- 假如恢复成功,记恢复操作日志
delete from restore_log where dt<getdate()-2
insert into restore_log (filename) values (@filename)
END
truncate table #filelist
end -- @i_exist=0
fetch BakFile_csr into @filename
end -- BakFile_csr loop
close BakFile_csr
deallocate BakFile_csr
drop table #dirList
return
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO