SQL数据库备份和远程恢复

来源：互联网发布：java 写文本文件编辑：程序博客网时间：2024/06/03 22:51

数据库备份和远程恢复

数据库远程备份和恢复按照地域的不同分为两部分，本地的备份数据库和远程的恢复数据库。

本地数据库定时进行备份，远程数据库定时进行数据库恢复。

备份和恢复的操作有计划任务定时执行，并分别在本地和远程服务器上建立的以数据库分别建立backup_status和restore_status两个表用于记录操作的状态信息，分别建立backupdatabase和restoredatabase两个存储过程用于执行备份和恢复的操作：、

backup_status和restore_status表的结构相同有以下字段：

字段名

类型

备注

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

backup log Membercalls to backupmembercallslog with format

Backupmembercallslog为存储设备，将在备份的计划任务中定义

Restoredatabase：

CREATE PROCEDURE ZZ

restore log MemberCalls from restoremembercallslog with standby = 'D:/andytest/MemberCalls_LOG.LDF'

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

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

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

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

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

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

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的条数应与最早出错和当前时间的正点时刻差相同.