数据库备份和远程恢复
数据库远程备份和恢复按照地域的不同分为两部分,本地的备份数据库和远程的恢复数据库。
本地数据库定时进行备份,远程数据库定时进行数据库恢复。
备份和恢复的操作有计划任务定时执行,并分别在本地和远程服务器上建立的以数据库分别建立backup_status和restore_status两个表用于记录操作的状态信息,分别建立backupdatabase和restoredatabase两个存储过程用于执行备份和恢复的操作:、
backup_status和restore_status表的结构相同有以下字段:
字段名
|
类型
|
备注
|
Ip
|
int
|
自增,唯一标示一天的24条记录
|
Date_year
|
varchar
|
记录 ‘年’
|
Date_month
|
varchar
|
记录 ‘月份’
|
Date_day,
|
varchar
|
记录 ‘日’
|
yesterday_status
|
varchar
|
记录昨天同一时刻发生的状态
|
today_status
|
varchar
|
记录今天发生的状态
|
amond_date
|
char
|
0-23标示一天24小时
|
backupdatabase和restoredatabase两个存储过程的内容为
backupdatabase:
CREATE PROCEDURE backupdatabase
AS
backup log Membercalls to backupmembercallslog with format
GO
Backupmembercallslog为存储设备,将在备份的计划任务中定义
Restoredatabase:
CREATE PROCEDURE ZZ
as
restore log MemberCalls from restoremembercallslog with standby = 'D:/andytest/MemberCalls_LOG.LDF'
GO
restoremembercallslog为存储设备,将在恢复的计划任务中定义
本地机器上的备份计划任务内容:
declare @amond_date INT
declare @rc int
declare @today_status varchar(15)
declare @IIPP varchar(15)
declare @IP INT
declare @IP2 INT
declare @cont INT
Declare @dt DateTime
Declare @file_name varchar(50)
declare @execString varchar(255)
declare @dtString varchar(15)
select @dt = getdate()
SELECT @IP = COUNT(*)
FROM backup_status AS b
where b.yesterday_status = 'FAILD'
and b.Date_day = datepart(day,@dt)
if @IP = 0
begin
SELECT @IP = COUNT(*)
FROM backup_status AS a
where a.today_status = 'FAILD'
and a.Date_day = datepart(day,@dt)-1
if @IP = 0
begin
SELECT @IP = COUNT(*)
FROM backup_status AS c
where c.today_status = 'FAILD'
and c.Date_day = datepart(day,@dt)
if @IP = 0
begin
SELECT @IP = COUNT(*) + 1
FROM backup_status AS d
where d.today_status = 'SUCCESS'
and d.yesterday_status = 'SUCCESS'
and d.Date_day = datepart(day,@dt)
SELECT @amond_date=amond_date
FROM backup_status AS d
where d.today_status = 'SUCCESS'
and d.yesterday_status = 'SUCCESS'
and d.Ip = @IP
SELECT @IIPP = @IP
if datepart(hour,@dt) >= @amond_date
begin
select @dtString = right('0000' + convert(varchar(4),datepart(year,@dt)),4)
+ '_'
+ right('00' + convert(varchar(2), datepart(month,@dt)),2)
+ '_'
+ right('00' + convert(varchar(2),datepart(day,@dt)),2)
select @file_name = @dtString + '_' + @IIPP + '_log.bak'
select @execstring = 'd:/andytest/MemberCalls' + @file_name
exec sp_addumpdevice 'disk','backupmembercallslog',@execstring
EXEC backupdatabase
if @@ERROR <> '0'
begin
update backup_status
set today_status = 'FAILD',Date_day=datepart(day,getdate()),Date_year=datepart(year,getdate()),Date_month=datepart(month,getdate())
where Date_day = datepart(day,getdate())-1
and today_status = 'SUCCESS'
and yesterday_status = 'SUCCESS'
and Ip = @IP
end
else
begin
update backup_status
set today_status = 'SUCCESS',Date_day=datepart(day,getdate()),Date_year=datepart(year,getdate()),Date_month=datepart(month,getdate())
where Date_day = datepart(day,getdate())-1
and today_status = 'SUCCESS'
and yesterday_status = 'SUCCESS'
and Ip = @IP
end
exec sp_dropdevice backupmembercallslog
end
end
else
begin
SELECT @IP = COUNT(*) + 1
FROM backup_status AS D
where D.today_status = 'SUCCESS'
and D.yesterday_status = 'SUCCESS'
and D.Date_day = datepart(day,@dt)
SELECT @IP2 = COUNT(*)
FROM backup_status AS E
where E.today_status = 'FAILD'
and E.yesterday_status = 'SUCCESS'
and E.Date_day = datepart(day,@dt)
SELECT @IIPP = @IP
SELECT @amond_date=amond_date
FROM backup_status AS d
where d.today_status = 'SUCCESS'
and d.yesterday_status = 'SUCCESS'
and d.Ip=@IP+@IP2
if @amond_date <= datepart(hour,@dt)
begin
update backup_status
set today_status = 'FAILD',Date_day=datepart(day,getdate()),Date_year=datepart(year,getdate()),Date_month=datepart(month,getdate())
where Date_day = datepart(day,getdate())-1
and Ip=@IP+@IP2
end
select @dtString = right('0000' + convert(varchar(4),datepart(year,@dt)),4)
+ '_'
+ right('00' + convert(varchar(2), datepart(month,@dt)),2)
+ '_'
+ right('00' + convert(varchar(2),datepart(day,@dt)),2)
select @file_name = @dtString + '_' + @IIPP + '_log.bak'
select @execstring = 'd:/andytest/MemberCalls' + @file_name
exec sp_addumpdevice 'disk','backupmembercallslog',@execstring
EXEC backupdatabase
if @@ERROR = '0'
begin
update backup_status
set today_status = 'SUCCESS',Date_day=datepart(day,getdate()),Date_year=datepart(year,getdate()),Date_month=datepart(month,getdate())
where Date_day = datepart(day,getdate())
and today_status = 'FAILD'
and yesterday_status = 'SUCCESS'
and Ip = @IP
-----------------------------------------------------------------
exec @rc = master.dbo.xp_smtp_sendmail
@FROM = N'andyy@aurorasoft.com.cn',
@FROM_NAME = N'John',
@TO = N'andyy@aurorasoft.com.cn',
---@CC = N