SQL数据库备份和远程恢复

最新推荐文章于 2024-08-15 01:26:58 发布

yangguo7790

最新推荐文章于 2024-08-15 01:26:58 发布

阅读量2.3k

点赞数

本文链接：https://blog.csdn.net/yangguo7790/article/details/2638138

版权

本文详细介绍了如何进行SQL数据库的备份操作，包括全备和增量备，并探讨了利用远程存储进行数据恢复的策略，确保在数据丢失情况下能够迅速恢复业务。同时，讨论了SQL Server的自动备份设置及日期相关的备份策略，为日常数据库维护提供了实用指南。

摘要由CSDN通过智能技术生成

数据库备份和远程恢复

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

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

备份和恢复的操作有计划任务定时执行，并分别在本地和远程服务器上建立的以数据库分别建立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

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

最低0.47元/天解锁文章

yangguo7790

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
SQL数据库备份和远程恢复

数据库备份和远程恢复数据库远程备份和恢复按照地域的不同分为两部分，本地的备份数据库和远程的恢复数据库。本地数据库定时进行备份，远程数据库定时进行数据库恢复。备份和恢复的操作有计划任务定时执行，并分别在本地和远程服务器上建立的以数据库分别建立backup_status和restore_status两个表用于记录操作的状态信息，分别建立backupdatabase和rest
复制链接

扫一扫