本文详细介绍了如何进行SQL数据库的备份操作，包括全备和增量备，并探讨了利用远程存储进行数据恢复的策略，确保在数据丢失情况下能够迅速恢复业务。同时，讨论了SQL Server的自动备份设置及日期相关的备份策略，为日常数据库维护提供了实用指南。
数据库备份和远程恢复
数据库远程备份和恢复按照地域的不同分为两部分，本地的备份数据库和远程的恢复数据库。
本地数据库定时进行备份，远程数据库定时进行数据库恢复。
备份和恢复的操作有计划任务定时执行，并分别在本地和远程服务器上建立的以数据库分别建立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'andyy@aurorasoft.com.cn',
@priority = N'NORMAL',
@subject = N'Hello SQL Server SMTP Mail',
@message = N'Backup log of MemberCalls for this hour was success!',
@type = N'text/plain',
@server = N'192.168.0.19'
-----------------------------------------------------------------
end
else
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())
and today_status = 'FAILD'
and yesterday_status = 'SUCCESS'
and Ip = @IP
end
exec sp_dropdevice backupmembercallslog
end
end
else
BEGIN
SELECT @IP = COUNT(*) + 1
FROM backup_status AS b
where b.today_status = 'SUCCESS'
and b.Date_day = datepart(day,@dt)-1
SELECT @IP2 = COUNT(*)+1
FROM backup_status AS c
where c.today_status = 'FAILD'
---and yesterday_status = 'FAILD'
and c.Date_day = datepart(day,@dt)
SELECT @IIPP = @IP + @IP2 - 1
select @today_status = today_status
from backup_status
where Ip = @IP2
update backup_status
set today_status = 'FAILD',yesterday_status = @today_status,Date_day=datepart(day,getdate()),Date_year=datepart(year,getdate()),Date_month=datepart(month,getdate())
where Ip = @IP2
and amond_date<=datepart(hour,@dt)
----while @IP2>0
----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)-1),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())-1
and yesterday_status = 'SUCCESS'
and today_status = 'FAILD'
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'andyy@aurorasoft.com.cn',
@priority = N'NORMAL',
@subject = N'Hello SQL Server SMTP Mail',
@message = N'Backup log of MemberCalls for this hour was success!',
@type = N'text/plain',
@server = N'192.168.0.19'
-----------------------------------------------------------------
end
else
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 yesterday_status = 'SUCCESS'
and today_status = 'FAILD'
and Ip = @IP
end
exec sp_dropdevice backupmembercallslog
----end
END
END
else
begin
SELECT @IP = COUNT(*) + 1
FROM backup_status AS c
where c.yesterday_status = 'SUCCESS'
and c.Date_day = datepart(day,@dt)
SELECT @IP2 = COUNT(*)
FROM backup_status AS d
where d.yesterday_status = 'FAILD'
and d.Date_day = datepart(day,@dt)
select @today_status = today_status
from backup_status
where Ip = @IP2 + @IP
SELECT @IIPP = @IP
update backup_status
set today_status = 'FAILD',yesterday_status = @today_status,Date_day=datepart(day,getdate()),Date_year=datepart(year,getdate()),Date_month=datepart(month,getdate())
where Ip = @IP2 + @IP
and amond_date<=datepart(hour,@dt)
---SELECT @amond_date=amond_date
---FROM backup_status AS d
---where d.today_status = 'SUCCESS'
---and d.yesterday_status = 'SUCCESS'
---and d.Date_day = datepart(day,@dt)
---if @amond_date >= convert(varchar(2),datepart(hour,@dt))
---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)-1),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 yesterday_status = 'SUCCESS',Date_day=datepart(day,getdate()),Date_year=datepart(year,getdate()),Date_month=datepart(month,getdate())
where Date_day = datepart(day,getdate())
and Ip = @IP
and yesterday_status = 'FAILD'
-----------------------------------------------------------------
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'andyy@aurorasoft.com.cn',
@priority = N'NORMAL',
@subject = N'Hello SQL Server SMTP Mail',
@message = N'Backup log of MemberCalls for this hour was success!',
@type = N'text/plain',
@server = N'192.168.0.19'
-----------------------------------------------------------------
end
else
begin
update backup_status
set yesterday_status = 'FAILD',Date_day=datepart(day,getdate()),Date_year=datepart(year,getdate()),Date_month=datepart(month,getdate())
where Date_day = datepart(day,getdate())
and Ip = @IP
and yesterday_status = 'FAILD'
end
exec sp_dropdevice backupmembercallslog
---end
end
select @cont=count(*)
from restore_status
where (yesterday_status = 'FAILD'
or today_status = 'FAILD')
and datepart(minute,getdate())<='1'
and datepart(minute,getdate())=amond_date
if @cont = 1
begin
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'andyy@aurorasoft.com.cn',
@priority = N'NORMAL',
@subject = N'Hello SQL Server SMTP Mail',
@message = N'Backup log of MemberCalls for this hour was faild!',
@type = N'text/plain',
@server = N'192.168.0.19'
end
远程服务器上恢复的计划任务为：
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 restore_status AS b
where b.yesterday_status = 'FAILD'
and b.Date_day = datepart(day,@dt)
if @IP = 0
begin
SELECT @IP = COUNT(*)
FROM restore_status AS a
where a.today_status = 'FAILD'
and a.Date_day = datepart(day,@dt)-1
if @IP = 0
begin
SELECT @IP = COUNT(*)
FROM restore_status AS c
where c.today_status = 'FAILD'
and c.Date_day = datepart(day,@dt)
if @IP = 0
begin
SELECT @IP = COUNT(*) + 1
FROM restore_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 restore_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','restoremembercallslog',@execstring
--EXEC sp_dboption msdb, single, true
EXEC ZZ
if @@ERROR <> '0' AND @@ERROR <> '3009'
begin
update restore_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 restore_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_dboption msdb, single, false
exec sp_dropdevice restoremembercallslog
end
end
else
begin
SELECT @IP = COUNT(*) + 1
FROM restore_status AS D
where D.today_status = 'SUCCESS'
and D.yesterday_status = 'SUCCESS'
and D.Date_day = datepart(day,@dt)
SELECT @IP2 = COUNT(*)
FROM restore_status AS E
where E.today_status = 'FAILD'
and E.yesterday_status = 'SUCCESS'
and E.Date_day = datepart(day,@dt)
SELECT @amond_date=amond_date
FROM restore_status AS d
where d.today_status = 'SUCCESS'
and d.yesterday_status = 'SUCCESS'
and d.Ip=@IP+@IP2
SELECT @IIPP = @IP
if @amond_date <= datepart(hour,@dt)
begin
update restore_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','restoremembercallslog',@execstring
EXEC ZZ
if @@ERROR <> '0' AND @@ERROR <> '3009'
begin
update restore_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())
and today_status = 'FAILD'
and yesterday_status = 'SUCCESS'
and Ip = @IP
end
else
begin
update restore_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'andyy@aurorasoft.com.cn',
@priority = N'NORMAL',
@subject = N'Hello SQL Server SMTP Mail',
@message = N'Restore log of MemberCalls was success!',
@type = N'text/plain',
@server = N'192.168.0.19'
-----------------------------------------------------------------
end
exec sp_dropdevice restoremembercallslog
end
end
else
BEGIN
SELECT @IP = COUNT(*) + 1
FROM restore_status AS b
where b.today_status = 'SUCCESS'
and b.Date_day = datepart(day,@dt)-1
SELECT @IP2 = COUNT(*)+1
FROM restore_status AS c
where c.today_status = 'FAILD'
---and yesterday_status = 'FAILD'
and c.Date_day = datepart(day,@dt)
select @today_status = today_status
from restore_status
where Ip = @IP2
SELECT @IIPP = @IP + @IP2 - 1
update restore_status
set today_status = 'FAILD',yesterday_status = @today_status,Date_day=datepart(day,getdate()),Date_year=datepart(year,getdate()),Date_month=datepart(month,getdate())
where Ip = @IP2
and amond_date<=datepart(hour,@dt)
----while @IP2>0
----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)-1),2)
select @file_name = @dtString + '_' + @IIPP + '_log.bak'
select @execstring = 'd:/andytest/MemberCalls' + @file_name
exec sp_addumpdevice 'disk','restoremembercallslog',@execstring
EXEC ZZ
if @@ERROR <> '0' AND @@ERROR <> '3009'
begin
update restore_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 yesterday_status = 'SUCCESS'
and today_status = 'FAILD'
and Ip = @IP + @IP2 - 1
end
else
begin
update restore_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 yesterday_status = 'SUCCESS'
and today_status = 'FAILD'
and Ip = @IP + @IP2 - 1
-----------------------------------------------------------------
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'andyy@aurorasoft.com.cn',
@priority = N'NORMAL',
@subject = N'Hello SQL Server SMTP Mail',
@message = N'Restore log of MemberCalls was success!',
@type = N'text/plain',
@server = N'192.168.0.19'
-----------------------------------------------------------------
end
exec sp_dropdevice restoremembercallslog
----end
END
END
else
begin
SELECT @IP = COUNT(*) + 1
FROM restore_status AS c
where c.yesterday_status = 'SUCCESS'
and c.Date_day = datepart(day,@dt)
SELECT @IP2 = COUNT(*)
FROM restore_status AS d
where d.yesterday_status = 'FAILD'
and d.Date_day = datepart(day,@dt)
select @today_status = today_status
from restore_status
where Ip = @IP2 + @IP
SELECT @IIPP = @IP
update restore_status
set today_status = 'FAILD',yesterday_status = @today_status,Date_day=datepart(day,getdate()),Date_year=datepart(year,getdate()),Date_month=datepart(month,getdate())
where Ip = @IP2 + @IP
and amond_date<=datepart(hour,@dt)
---SELECT @amond_date=amond_date
---FROM restore_status AS d
---where d.today_status = 'SUCCESS'
---and d.yesterday_status = 'SUCCESS'
---and d.Date_day = datepart(day,@dt)
---if @amond_date >= convert(varchar(2),datepart(hour,@dt))
---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)-1),2)
select @file_name = @dtString + '_' + @IIPP + '_log.bak'
select @execstring = 'd:/andytest/MemberCalls' + @file_name
exec sp_addumpdevice 'disk','restoremembercallslog',@execstring
EXEC ZZ
if @@ERROR <> '0' AND @@ERROR <> '3009'
begin
update restore_status
set yesterday_status = 'FAILD',Date_day=datepart(day,getdate()),Date_year=datepart(year,getdate()),Date_month=datepart(month,getdate())
where Date_day = datepart(day,getdate())
and Ip = @IP
and yesterday_status = 'FAILD'
end
else
begin
update restore_status
set yesterday_status = 'SUCCESS',Date_day=datepart(day,getdate()),Date_year=datepart(year,getdate()),Date_month=datepart(month,getdate())
where Date_day = datepart(day,getdate())
and Ip = @IP
and yesterday_status = 'FAILD'
-----------------------------------------------------------------
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'andyy@aurorasoft.com.cn',
@priority = N'NORMAL',
@subject = N'Hello SQL Server SMTP Mail',
@message = N'Restore log of MemberCalls was success!',
@type = N'text/plain',
@server = N'192.168.0.19'
-----------------------------------------------------------------
end
exec sp_dropdevice restoremembercallslog
---end
end
select @cont=count(*)
from restore_status
where (yesterday_status = 'FAILD'
or today_status = 'FAILD')
and datepart(minute,getdate())<='1'
and datepart(minute,getdate())=amond_date
if @cont = 1
begin
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'andyy@aurorasoft.com.cn',
@priority = N'NORMAL',
@subject = N'Hello SQL Server SMTP Mail',
@message = N'Restore log of MemberCalls for this hour was faild!',
@type = N'text/plain',
@server = N'192.168.0.19'
end
计划任务的添加:
点击start->settings->control panel->双击打开Scheduled Tasks,点击右键选择new-> Scheduled Tasks,修改计划任务的名称后,双击打开Scheduled Tasks的属性,在task 的run中填写:
|
计划任务
|
代码内容
|
|
恢复
|
isql /U sa /P "" /S 192.168.0.54 /d msdbb /d msdbb /i "d:/webpage/restoremembercalls.qry"
|
|
备份
|
isql /U sa /P oguk52677 /S 192.168.0.25 /d andytest /i "d:/webpage/backupmembercalls.qry"
|
/U 登陆服务器的用户名
/P 登陆服务器的密码
/S 登陆的服务器地址
/d 新建的数据库
/I 执行的计划任务路径
在Schedule属性中设定任务开始,结束,持续的时间,以及每次执行的时间间隔.
任务添加成功后,计划任务会按照预先的设定定时执行.
并可点击start->settings->control panel-> Administrative Tools-> 双击Event Viewer-> 点击Application Log
查看计划任务运行的日志.一旦此日志满,可点击action下的Clear all events来删除日志,也可点击properties设定保存日志文件的总量和自动清理时间
注意:计划任务中设定的时间间隔仅做为计划任务执行的时钟脉冲,与备份和恢复的日志的命名无关.
数据库的备份恢复模式
|
时间
|
本地
|
远程
| |||
|
完全备份
|
日志备份
|
完全备份
|
完全恢复
|
日志恢复
| |
|
周日
|
执行一次
|
每x分钟一次
|
执行一次
(与本地服务器同一时刻)
|
执行一次
|
每小时一次
|
|
周一
|
|
每小时一次
|
|
|
每小时一次
|
|
周二
|
|
每小时一次
|
|
|
每小时一次
|
|
周三
|
|
每小时一次
|
|
|
每小时一次
|
|
周四
|
|
每小时一次
|
|
|
每小时一次
|
|
周五
|
|
每小时一次
|
|
|
每小时一次
|
|
周六
|
|
每小时一次
|
|
|
每小时一次
|
数据库备份和恢复的状态监视
首先将表的内容设制为以下状态 18 为当前的前一日日期
Ip Date_year Date_month Date_day yesterday_status today_status amond_date
1 2007 4 18 SUCCESS SUCCESS 0
2 2007 4 18 SUCCESS SUCCESS 1
3 2007 4 18 SUCCESS SUCCESS 2
4 2007 4 18 SUCCESS SUCCESS 3
5 2007 4 18 SUCCESS SUCCESS 4
6 2007 4 18 SUCCESS SUCCESS 5
7 2007 4 18 SUCCESS SUCCESS 6
8 2007 4 18 SUCCESS SUCCESS 7
9 2007 4 18 SUCCESS SUCCESS 8
10 2007 4 18 SUCCESS SUCCESS 9
11 2007 4 18 SUCCESS SUCCESS 10
12 2007 4 18 SUCCESS SUCCESS 11
13 2007 4 18 SUCCESS SUCCESS 12
14 2007 4 18 SUCCESS SUCCESS 13
15 2007 4 18 SUCCESS SUCCESS 14
16 2007 4 18 SUCCESS SUCCESS 15
17 2007 4 18 SUCCESS SUCCESS 16
18 2007 4 18 SUCCESS SUCCESS 17
19 2007 4 18 SUCCESS SUCCESS 18
20 2007 4 18 SUCCESS SUCCESS 19
21 2007 4 18 SUCCESS SUCCESS 20
22 2007 4 18 SUCCESS SUCCESS 21
23 2007 4 18 SUCCESS SUCCESS 22
24 2007 4 18 SUCCESS SUCCESS 23
一旦百备份的日志按时传送到并正确恢复则状态显示为:
1 2007 4
19 SUCCESS SUCCESS 0
2 2007 4
19 SUCCESS SUCCESS 1
3 2007 4 18 SUCCESS SUCCESS 2
4 2007 4 18 SUCCESS SUCCESS 3
5 2007 4 18 SUCCESS SUCCESS 4
6 2007 4 18 SUCCESS SUCCESS 5
7 2007 4 18 SUCCESS SUCCESS 6
8 2007 4 18 SUCCESS SUCCESS 7
.
……………………………………………….
否则,备份不成功或者传送不成功,则状态显示
1 2007 4
19 SUCCESS FAILD 0
2 2007 4
19 SUCCESS FAILD 1
3 2007 4 18 SUCCESS SUCCESS 2
4 2007 4 18 SUCCESS SUCCESS 3
……………………………………………………………………………….
如果操作已到第二天,及如下状态:
1 2007 4
18 SUCCESS FAILD 0
2 2007 4
18 SUCCESS FAILD 1
3 2007 4
18 SUCCESS FAILD 2
……………………………………………………………………………………
……………………………………………………………………………………
22 2007 4
18 SUCCESS FAILD 21
23 2007 4
18 SUCCESS FAILD 22
24 2007 4
18 SUCCESS FAILD 23
如果 IP为1的备份还为成功恢复,将会出现如下状态:
1 2007 4
19 FAILD FAILD 0
2 2007 4 18 SUCCESS FAILD 1
3 2007 4 18 SUCCESS FAILD 2
……………………………………………………………………………………
……………………………………………………………………………………
22 2007 4 18 SUCCESS FAILD 21
23 2007 4 18 SUCCESS FAILD 22
24 2007 4 18 SUCCESS FAILD 23
而如果 IP为1的备份成功恢复,将会出现如下状态:
1 2007 4
19 SUCCESS FAILD 0
2 2007 4 18 SUCCESS FAILD 1
3 2007 4 18 SUCCESS FAILD 2
……………………………………………………………………………………
……………………………………………………………………………………
22 2007 4 18 SUCCESS FAILD 21
23 2007 4 18 SUCCESS FAILD 22
24 2007 4 18 SUCCESS FAILD 23
接下去如果 IP为2的备份成功恢复,将会出现如下状态:
1 2007 4
19 SUCCESS FAILD 0
2 2007 4
19 SUCCESS FAILD 1
3 2007 4 18 SUCCESS FAILD 2
……………………………………………………………………………………
……………………………………………………………………………………
否则如果 IP为2的备份未成功恢复,将会出现如下状态:
1 2007 4
19 SUCCESS FAILD 0
2 2007 4
19 FAILD FAILD 1
3 2007 4 18 SUCCESS FAILD 2
……………………………………………………………………………………
……………………………………………………………………………………
也就是说在前一天的操作未成功前,操作是不会进行到当前一天的,而是优先执行前一天最早未恢复成功的备份,及计划任务会停止在操作出错的位置,以防止错误的恢复顺序
每到计划任务规定的执行时间,计划任务便会首先检查是否有前一天的操作执行失败,如果有则会每间隔事先设定的间隔时间(如一分钟)检查一次最早执行失败的备份是否已有并执行.如果没有则会检查当前一天的本时刻之前的操作有无失败,如有则会每间隔事先设定的间隔时间(如一分钟)检查一次最早执行失败的备份是否已有并执行.
与以上同时进行的是,计划任务将同时检查与amond_date中内容相同时刻的备份,如过此次操作之前所有操作都成功则直接执行恢复或备份操作并按照执行的结果改变当前日期的状态,否则直接将当前日期的状态改变为”FAILD”
数据库备份恢复失败的消息的发送:
数据库备份恢复失败的消息的发送可通过EMAIL 进行:
Email组件的安装:
将xpsmtp80.dll的文件放置于C:/Program Files/Microsoft SQL Server/80/Tools/Binn下
并执行:
exec sp_addextendedproc 'xp_smtp_sendmail', 'xpsmtp80.dll'
grant execute on xp_smtp_sendmail to public
发送email的代码为
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'andyy@aurorasoft.com.cn',
@priority = N'NORMAL',
@subject = N'Hello SQL Server SMTP Mail',
@message = N'Restore log of MemberCalls for this hour was faild!',
@type = N'text/plain',
@server = N'192.168.0.19'
@FROM 为发件人地址
@FROM_NAME 为发件人名称
@TO 为收件人地址
@subject 为信件名称
@message 内容
@server 信箱服务器
EMAIL的发送时间为每次操作失败后,而一旦操作成功则会发送内容为操作成功的EMAIL,如果是第一次操作就执行成功则不会发送任何EMAIL
数据库在恢复的过程中将为只读形式,而MSDB恢复时则要将数据库设制为单用户模式.可利用语句:
EXEC sp_dboption msdb, single, ture
EXEC sp_dboption msdb, single, false
并保持代理服务器为关闭状态
恢复过程中因为计划任务具有自动纠正操作错误的功能,所以只要保证备份文件正确传送即可.
根据第一次收到信息的EMAIL可判断与EMAIL发送同一正点时刻的备份文件传送有误,后可根据收到的操作成功的EMAIL的条数判断最早出错到当前的操作之间是否有其他的备份文件有误.因为如果无其它备份文件出错,则收到操作成功的EMAIL的条数应与最早出错和当前时间的正点时刻差相同.
一旦需要使用远程服务器的数据库,则可将收到的最后一次备份,执行一次非只读模式的恢复即可.
扫一扫
396
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
