求助大虾，编程实现SQL数据库备份、恢复以及备份管理，不够分可再加！！！

[布学无数]

同上！！！

[eddiezhuo]

看着头痛

[txlicenhe]

/************ 备份 恢复 *******
backup database sys to disk='c:\目录\a.bak' with init


--还原
use master
RESTORE DATABASE TestDB FROM DISK = 'c:\目录\a.bak'

[布学无数]

备份管理？

[leimin]

create PROCEDURE GY_DBBak
@bakequip int, -- 备份设备：磁盘&磁带
@bakpath varchar(50), -- 带全路径的备份文件名
@baktype int, -- 完全备份&增量备份
@baklog int, -- ‘0’备份日志
@bakdb int, -- ‘0’备份数据库
@kind varchar(7), --备份还是恢复

@retmsg varchar(20) output --返回信息
AS


DECLARE @DevName_data varchar(50)
DECLARE @DevName_log varchar(50)
declare @db_path varchar(100)
declare @log_path varchar(100)

DECLARE @RC INT

SELECT @db_path = @bakpath + '.dat'
SELECT @log_path = @bakpath + 'log.dat'
SELECT @RC=0

DBCC CHECKDB(Northwind)
/***********************************************************
** CREATE BACKUP AND RESTORE DEVICES
************************************************************/
IF @RC=0
BEGIN

EXEC sp_addumpdevice 'disk', @DevName_data,@db_path

exec sp_addumpdevice 'disk', @DevName_log,@log_path
select @rc=@@error
IF @RC<>0
begin
EXEC SP_DropDevice @Devname_data
exec sp_dropdevice @devname_log
SELECT @RC=-1000
return @rc
end
END

IF @kind='backup'
BEGIN
IF @bakequip=0
BEGIN
IF @baktype=0
BEGIN
IF @bakdb=0
BEGIN
BACKUP DATABASE Northwind TO DISK=@Devname_data
WITH INIT
END
IF @baklog=0
BEGIN
BACKUP LOG Northwind WITH NO_LOG
BACKUP LOG Northwind TO DISK=@DevName_log
WITH INIT,NO_TRUNCATE
END
END
ELSE BEGIN
IF @bakdb=0
BEGIN
BACKUP DATABASE Northwind TO DISK=@DevName_data
WITH NOINIT
END
IF @baklog=0
BEGIN
BACKUP LOG Northwind WITH NO_LOG
BACKUP LOG Northwind TO DISK=@DevName_log
WITH NOINIT,NO_TRUNCATE
END
END
END
SELECT @retmsg='数据库备份成功！'
END

IF @kind='restore'
BEGIN
RESTORE DATABASE Northwind FROM DISK= @DevName_data WITH REPLACE
SELECT @retmsg='恢复数据库成功！'
END

RETURN 0

[txlicenhe]

备份管理，你是指用job调度吗？
EXEC sp_add_job @job_name = '作业名字'

EXEC sp_add_jobstep @job_name = '作业名字',
@step_name = '步骤名子',
@subsystem = 'TSQL',
@command = 'EXEC 库名..过程名', -- 此处改成如上备份语句即可。
@retry_attempts = 5, --重试次数
@retry_interval = 5 --重试间隔

EXEC sp_add_jobschedule @job_name = '作业名字',
@name = '作业调度名字',
@freq_type = 4, -- 每天
@freq_interval = 26, --间隔
@active_start_time = 10000 --开始时间

[zjcxc]

备份管理,你是用程序吧?

[zjcxc]

如果是在SQL中实现,可以考虑下面的job

--创建备份当前数据库的存储过程
create proc p_backupdb
as
declare @sql varchar(8000)
set @sql='backup database '+db_name()+' to disk=''c:\'+db_name()
+convert(varchar,getdate(),112)+'.bak'''
exec(@sql)
go

--创建作业
exec sp_add_job @job_name='数据备份'

--创建作业步骤
declare @sql varchar(8000) --数据备份的命令
declare @dbname varchar(250)

select @sql='exec p_backupdb'
,@dbname=db_name()

exec sp_add_jobstep @job_name='数据备份',
@step_name = '数据备份处理',
@subsystem = 'TSQL',
@database_name=@dbname,
@command = @sql
@retry_attempts = 5, --重试次数
@retry_interval = 5 --重试间隔

--创建调度
EXEC sp_add_jobschedule @job_name = '数据备份',
@name = '时间安排',
@freq_type = 4, --每天
@freq_interval = 1, --每天执行一次
@active_start_time = 17000 --下午17:00:00分执行

[zjcxc]

--数据库恢复,如果是程序的话,我自己有一个VB的备份,恢复程序,你可以参考::

/*
恢复数据库
指定备份文件名及要恢复的数据库名
自动恢复到SQL数据目录下

--调用示例
exec p_RestoreDb 'c:\fund\fund.smp','new'
*/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_RestoreDb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_RestoreDb]
GO

create proc p_RestoreDb
@bkfile varchar(1000), --定义要恢复的备份文件名
@redb varchar(200), --定义恢复后的数据库名
@overexist bit=1 --是否覆盖已经存在的数据库
as
/*--直接在查询分析器中执行
declare @bkfile varchar(1000),@redb varchar(200),@overexist bit
select @bkfile='c:\fund\fund.smp' --定义要恢复的备份文件名
,@redb='new' --定义恢复后的数据库名
,@overexist=0 --是否覆盖已经存在的数据库
-----------------------------------------------------------*/

--从备份文件中获取逻辑文件名和物理文件名
declare @logfn1 varchar(250),@logfn2 varchar(250)
declare @phyfn1 varchar(1000),@phyfn2 varchar(1000)

--创建临时表,保存获取的信息
create table #tb(lgfn varchar(250),pyfn varchar(1000),type char(1),fg varchar(200),size bigint,maxsize bigint)

--从备份文件中获取信息
insert into #tb
exec('restore filelistonly
from disk='''+@bkfile+'''')

--将信息保存到变量中
select @logfn1=lgfn,@phyfn1=pyfn from #tb where type='D'
select @logfn2=lgfn,@phyfn2=pyfn from #tb where type='L'

--删除临时表
drop table #tb

--显示获取的信息
select @logfn1 as 逻辑数据文件名,@phyfn1 as 物理数据文件名
union all
select @logfn2 as 逻辑日志文件名,@phyfn2 as 物理日志文件名

--生成恢复的语句
declare @sql varchar(8000)

--得到SQL安装时的数据文件路径
select @phyfn1=rtrim(reverse(filename)) from master..sysfiles where name='master'

select @phyfn1=reverse(substring(@phyfn1,charindex('\',@phyfn1),8000))

--生成数据恢复语句,将数据库恢复到SQL数据目录下
set @sql='restore database '+@redb
+' from disk='''+@bkfile+''''
+' with move '''+@logfn1+''' to '''
+@phyfn1+@redb+'.mdf'',move '''
+@logfn2+''' to '''+@phyfn1+@redb+'.ldf'''
+case @overexist when 1 then ',replace' else '' end
exec(@sql)
go

[布学无数]

是呀，我要编程实现诸如：
1、可以列出数据库的备份清单，并显示详细信息，如备份日期等。
2、备份时间选择备份进行恢复。

[realgz]

我写了一个存储过程玩。。。。。
-------------------------------use master
go
sp_addmessage 577056500,'16','Sorry,Database NOT Foud','us_english' --未指定数据库名称
go
sp_addmessage 577056501,'16','Sorry,Path NOT found,can''t backup database','us_english' --无路径还原数据库
go
sp_addmessage 577056502,'16','Sorry,Path NOT found,can''t restore database','us_english' --无备份文件
go
sp_addmessage 577056503,'16','I DON''T KNOWN HOW OLD TO DELETE? ','us_english' --无删除范围
go
sp_addmessage 577056504,'16','Sorry,Path NOT found,can''t backup log','us_english' --无备份文件
go
sp_addmessage 577056599,'16','COMMAND NOT FOUND','us_english' --暂不提供本功能
go
if exists (select * from sysobjects where name='sp_dropconn')
drop procedure sp_dropconn
go
create PROCEDURE dbo.sp_dropconn
@db_name varchar(255)
AS
/*
断开特定数据库用户的连接

*/
declare @spid int
, @execsour varchar(8000)
---------------------------------------------------------------------------------------
create table #tmp_login
(spid int,ecid int,status varchar(255),loginname varchar(255),hostname varchar(255),blk varchar(255),dbname varchar(255),cmd varchar(255))
insert into #tmp_login exec master..sp_who
declare tmp_l cursor for select spid from #tmp_login where dbname=@db_name --使用该数据库的用户

open tmp_l
fetch next from tmp_l into @spid
while @@fetch_status=0
begin
set @execsour=convert(varchar(300),@spid)
set @execsour='kill '+@execsour
exec (@execsour ) --断开当前数据库连接
fetch next from tmp_l into @spid
end
close tmp_l
deallocate tmp_l
----------------------------------------------------------------------------------------------

go

if exists (select * from sysobjects where name='sp_db_tools')
drop procedure sp_db_tools
go
/*
数据维护的存储过程，
@db_name 数据库名称
@work 任务
@File_Path 相关路径
@Othe 其他
@make 路径不存在时创建 值0（不创建，直接抛出异常）和1（创建）
任务：
1、备份数据库 backup sp_db_tools 'kq','backup','d:\tmp\'[,'mybak.myfile']
2、转移数据库 move sp_db_tools 'kq','move','d:\tmp\'
3、还原数据库 restore sp_db_tools 'kq','restore','d:\tmp\mybak.bak'[,'f:\mydata\']
4、收缩数据库 SHRINK
5、打开日志 log_trun_on sp_db_tools 'kq','log_trun_on'
6、关闭日志 log_trun_off sp_db_tools 'kq','log_trun_off'
7、删除日志 drop_log sp_db_tools 'kq','drop_log'
8、删除多余的备份 drop_bak drop_bak sp_db_tools 'kq','drop_bak','d:\tmp\','8'
9、备份日志 log_bak sp_db_tools 'kq','log_bak','d:\tmp\'[,'mylogbak.myfile']
10、从日志还原 log_restore
11、建立数据库 make_db sp_db_tools 'kq','make_db','f:\mydata\'
12、设置sa密码 pass sp_db_tools 'sa','new password'
相关路径：涉及文件操作的路径
其他：如在备份时指定了文件名或还原时指定了数据库路径，使用本参数
*/

create procedure dbo.sp_db_tools
@db_name varchar(255),@work varchar(127),@File_Path varchar(511)='',@Other varchar(511)='',@make tinyint=0
as

declare @date varchar(20)
, @execsour varchar(8000)
, @logical_file varchar(255)

set @logical_file=''
set @date=convert(varchar(20),getdate(),112)+'_'+replace(convert(varchar(20),getdate(),108),':','')
--print @date
if (@db_name='' or @db_name is null)and @work<>'pass'
begin
raiserror (577056500,16,1) --没有数据库名
return 0
end
------------------------------------------------------------

create table #file (isfile int,isdir int,father int)
if @make=1
begin --创建不存在的路径
declare @dir varchar(255) --当前操作路径
set @dir=@file_path
set @logical_file=left(@dir,charindex('\',@dir)-1)
set @dir=right(@dir,len(@dir)-charindex('\',@dir))
while 1>0
begin
if charindex('\',@dir)=0
begin
if len(@dir)=0
break

set @logical_file=@logical_file+'\'+@dir
set @dir=''
end
else
begin
set @logical_file=@logical_file+'\'+left(@dir,charindex('\',@dir)-1)
set @dir=right(@dir,len(@dir)-charindex('\',@dir))
end

insert into #file exec xp_fileexists @logical_file
if (select sum(isdir) from #file)=0 and right(@logical_file,1)<>':'
begin
set @execsour='xp_cmdshell ''md "'+@logical_file +'"'''

exec (@execsour)

end
delete from #file
end
end
----------------------------------------------------------------------

[realgz]

/****************************************备份数据库************************************************/
if @work='backup'
begin
if @File_Path =''
begin
RAISERROR (577056501, 16, 1) --没有备份路径
return 0
end
set @execsour=@file_path

if @other<>'' --指定了文件名
set @execsour='backup database '+@db_name+' to disk =''' +@execsour+'\'+@other+''''
else --自动的文件名
set @execsour='backup database '+@db_name+' to disk =''' +@execsour+'\bak_'+@db_name+@date+'.bak'''


set @execsour=replace(@execsour,'\\','\')

exec (@execsour)
return 0
end
--备份
/*******************************************备份数据库完****************************************/


/*******************************************移动数据库****************************************/
if @work='move'
begin

if exists (select * from sysobjects where name='tmp_f' )
drop table tmp_f

exec ('select * into tmp_f from '+@db_name+'.dbo.sysfiles')
-----------------------------------------------------------------------------------------
exec master.dbo.sp_dropconn @db_name --断开连接
----------------------------------------------------------------------------------------------
exec sp_detach_db @db_name
set @execsour='exec master..sp_attach_db '+''''+@db_name+''''
declare tmp_f cursor for select replace(filename,' ','') from tmp_f
open tmp_f
fetch next from tmp_f into @logical_file
while @@fetch_status=0
begin
exec ('xp_cmdshell ''move "'+@logical_file+'" "' +@file_path+'"''')
--set @logical_file=replace(@logical_file,' ','')
while charindex('\',@logical_file)<>0
begin
set @logical_file=right(@logical_file,len(@logical_file)-charindex('\',@logical_file))
print @logical_file
end
set @logical_file=@file_path+'\'+@logical_file
set @execsour=@execsour+','''+@logical_file+''''
fetch next from tmp_f into @logical_file
end
close tmp_f
deallocate tmp_f
set @execsour=replace(@execsour,'\\','\')
exec (@execsour)
return 0
end

/*******************************************移动数据库完****************************************/



/*******************************************还原数据库****************************************/
if @work='restore'
begin
-----------------------------------------------------------------------------------------
exec master.dbo.sp_dropconn @db_name --断开连接
----------------------------------------------------------------------------------------------
if @file_path=''
begin
RAISERROR (577056502, 16, 1) --没有备份文件路径
return
end
set @execsour='restore database '+@db_name+' from disk ='''+@file_path+''''
if @other <>'' and @other is not null
begin
create table #tmp_file (name varchar(255), PhysicalName varchar(255),type varchar(255),FileGroupName varchar(255),size bigint,maxsize bigint)
insert into #tmp_file
exec ('RESTORE FILELISTONLY FROM DISK = N'''+@file_path+''' WITH FILE = 1 , NOUNLOAD ' ) --取得逻辑文件名


declare tmp_1 cursor for select name from #tmp_file order by type
open tmp_1
fetch next from tmp_1 into @logical_file
set @execsour=@execsour+' with move '+''''+@logical_file+'''' +' to '+ ''''+@other+'\'+@logical_file+'.mdf'''
fetch next from tmp_1 into @logical_file
while @@fetch_status=0
begin
set @execsour=@execsour+ ','+' move '''+@logical_file+'''' +' to '+ ''''+@other+'\'+@logical_file+'.ldf'''
fetch next from tmp_1 into @logical_file
end
close tmp_1
deallocate tmp_1
end
set @execsour=replace(@execsour,'\\','\')
exec (@execsour)
return 0
end

/*******************************************还原数据库完****************************************/

[realgz]

/*******************************************打开/关闭数据库日志****************************************/
if @work='log_trun_off'
begin
exec sp_dboption @db_name,'trunc. log on chkpt.','TRUE' --关闭日志
return 0
end
if @work='log_trun_on'
begin
exec sp_dboption @db_name,'trunc. log on chkpt.','FALSE' --打开日志
return 0
end

/*******************************************日志打开/关闭完****************************************/


/*******************************************删除日志文件****************************************/
if @work='drop_log'
begin
if exists (select * from sysobjects where name='tmp_f' )
drop table tmp_f

exec ('select * into tmp_f from '+@db_name+'.dbo.sysfiles')

-----------------------------------------------------------------------------------------
exec master.dbo.sp_dropconn @db_name --断开连接
----------------------------------------------------------------------------------------------
exec sp_detach_db @db_name
declare tmp_f2 cursor for select replace(filename,' ','') from tmp_f order by fileid desc
open tmp_f2
fetch next from tmp_f2 into @logical_file
while @@fetch_status=0
begin
if @logical_file like '%.ldf%' --日志文件，删除
exec ('xp_cmdshell ''del "'+@logical_file+'"''')
else
exec sp_attach_single_file_db @db_name,@logical_file --数据文件，附加

fetch next from tmp_f2 into @logical_file
end
close tmp_f2
deallocate tmp_f2
return 0
end

/*******************************************删除日志完****************************************/


/*******************************************删除备份**************+'\bak_'+@db_name**************************/
if @work='drop_bak'
begin
set @other=replace(@other,' ','')
if @other='' or isnumeric(@other)<>1
begin
raiserror (577056500,16,1) --没有数据库名,或不是有效的数字
return 0
end

declare @tmp varchar(255)
create table #tmp_bak
(item varchar(255),depth tinyint,isfile tinyint)
insert into #tmp_bak
EXECUTE master.dbo.xp_dirtree @file_path, 1, 1

declare tmp_file cursor for select item from #tmp_bak where isfile=1 and item like 'bak%'
open tmp_file
fetch next from tmp_file into @logical_file
while @@fetch_status=0
begin
set @tmp=left(replace(@logical_file, 'bak_'+@db_name,''),8)

if datediff(dd,convert(datetime,@tmp),getdate())>convert(int,@other)
begin
set @execsour='del "'+@file_path+'\'+@logical_file+'"'
set @execsour='del '+replace(@execsour,'\\','\')
exec xp_cmdshell @execsour
end
fetch next from tmp_file into @logical_file
end
close tmp_file
deallocate tmp_file
return 0
end
/*******************************************删除备份完****************************************/



/*******************************************备份数据库日志****************************************/
if @work='log_bak'
begin
if @File_Path =''
begin
RAISERROR (577056501, 16, 1) --没有备份路径
return
end
set @execsour=@file_path

if @other<>'' --指定了文件名
set @execsour='backup log '+@db_name+' to disk =''' +@execsour+'\'+@other+''''
else --自动的文件名
set @execsour='backup log '+@db_name+' to disk =''' +@execsour+'\bak_'+@db_name+@date+'.log.bak'''


set @execsour=replace(@execsour,'\\','\')

exec (@execsour)
return 0


end

/*******************************************备份数据库日志完****************************************/


/*******************************************建立数据库****************************************/
if @work='make_db'
begin
if @File_Path =''
begin
RAISERROR (577056504, 16, 1) --没有备份路径
return 0
end
SET @EXECSOUR=
'
create database '+@DB_NAME
+' ON '+
'(NAME='+@DB_NAME+
', FILENAME='''+@FILE_PATH+'\'+@DB_NAME+'.MDF'',
size=1,
FILEGROWTH = 10MB
)'

set @execsour=replace(@execsour,'\\','\')
exec (@EXECSOUR)
return 0
end

/*******************************************建立数据库****************************************/


/*******************************************更改sa密码****************************************/
if @work='pass'
begin
exec sp_password null,@file_path,'sa'
return 0
end

/*******************************************更改sa密码完****************************************/

RAISERROR (577056599, 16, 1) --没有该命令

[zjcxc]

备份方案参考:
http://expert.csdn.net/Expert/topic/2359/2359124.xml?temp=.9630091