编程实现备份和还原数据库

 注意，下面备份还原都是用存储过程实现！
 
if exists (select * from dbo.sysobjects where id = object_id(N@#[dbo].[p_backupdb]@#) and OBJECTPROPERTY(id, N@#IsProcedure@#) = 1)
drop procedure [dbo].[p_backupdb]
GO
 
/*--备份数据库的通用存储过程
 
--邹建 2003.10--*/
 
/*--调用示例
 
--备份当前数据库
exec p_backupdb @bkpath=@#c:\@#,@bkfname=@#\DBNAME\_\DATE\_db.bak@#
存储过程实现备份和还原数据库：
if exists (select * from dbo.sysobjects where id = object_id(N@#[dbo].[p_backupdb]@#) and OBJECTPROPERTY(id, N@#IsProcedure@#) = 1)
drop procedure [dbo].[p_backupdb]
GO
 
/*--备份数据库的通用存储过程
 
--邹建 2003.10--*/
 
/*--调用示例
 
--备份当前数据库
exec p_backupdb @bkpath=@#c:\@#,@bkfname=@#\DBNAME\_\DATE\_db.bak@#
 
--差异备份当前数据库
exec p_backupdb @bkpath=@#c:\@#,@bkfname=@#db_\DATE\_df.bak@#,@bktype=@#DF@#
 
--备份当前数据库日志
exec p_backupdb @bkpath=@#c:\@#,@bkfname=@#db_\DATE\_log.bak@#,@bktype=@#LOG@#
--*/
create proc p_backupdb
@dbname sysname=@#@#,   --要备份的数据库名称,不指定则备份当前数据库
@bkpath nvarchar(260)=@#@#, --备份文件的存放目录,不指定则使用SQL默认的备份目录
@bkfname nvarchar(260)=@#@#, --备份文件名,文件名中可以用\DBNAME\代表数据库名,\DATE\代表日期,\TIME\代表时间
@bktype nvarchar(10)=@#DB@#, --备份类型:@#DB@#备份数据库,@#DF@# 差异备份,@#LOG@# 日志备份
@appendfile bit=1,   --追加/覆盖备份文件
@password nvarchar(20)=@#@# --为备份文件设置的密码(仅sql2000支持),设置后,恢复时必须提供此密码
as
 declare @sql varchar(8000)
 if isnull(@dbname,@#@#)=@#@# set @dbname=db_name()
 if isnull(@bkpath,@#@#)=@#@#
 begin
  select @bkpath=rtrim(reverse(filename)) from master..sysfiles where name=@#master@#
  select @bkpath=substring(@bkpath,charindex(@#\@#,@bkpath)+1,4000)
   ,@bkpath=reverse(substring(@bkpath,charindex(@#\@#,@bkpath),4000))+@#BACKUP\@#
 end
 if isnull(@bkfname,@#@#)=@#@# set @bkfname=@#\DBNAME\_\DATE\_\TIME\.BAK@#
 set @bkfname=replace(replace(replace(@bkfname,@#\DBNAME\@#,@dbname)
  ,@#\DATE\@#,convert(varchar,getdate(),112))
  ,@#\TIME\@#,replace(convert(varchar,getdate(),108),@#:@#,@#@#))
 set @sql=@#backup @#+case @bktype when @#LOG@# then @#log @# else @#database @# end +@dbname
  +@# to disk=@#@#@#+@bkpath+@bkfname
  +@#@#@# with @#+case @bktype when @#DF@# then @#DIFFERENTIAL,@# else @#@# end
  +case @appendfile when 1 then @#NOINIT@# else @#INIT@# end
  +case isnull(@password,@#@#) when @#@# then @#@# else @#,PASSWORD=@#@#@#+@password+@#@#@#@# end
 exec(@sql)
go
 
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
 
/*--恢复数据库的通用存储过程
 
--邹建 2003.10--*/
 
/*--调用示例
--完整恢复数据库
exec p_RestoreDb @bkfile=@#c:\db_20031015_db.bak@#,@dbname=@#db@#
 
--差异备份恢复
exec p_RestoreDb @bkfile=@#c:\db_20031015_db.bak@#,@dbname=@#db@#,@retype=@#DBNOR@#
exec p_RestoreDb @bkfile=@#c:\db_20031015_df.bak@#,@dbname=@#db@#,@retype=@#DF@#
 
--日志备份恢复
exec p_RestoreDb @bkfile=@#c:\db_20031015_db.bak@#,@dbname=@#db@#,@retype=@#DBNOR@#
exec p_RestoreDb @bkfile=@#c:\db_20031015_log.bak@#,@dbname=@#db@#,@retype=@#LOG@#
 
--*/
 
create proc p_RestoreDb
@bkfile nvarchar(1000),  --定义要恢复的备份文件名(带路径)
@dbname sysname=@#@#,      --定义恢复后的数据库名,默认为备份的文件名
@dbpath nvarchar(260)=@#@#, --恢复后的数据库存放目录,不指定则为SQL的默认数据目录
@retype nvarchar(10)=@#DB@#, --恢复类型:@#DB@#完事恢复数据库,@#DBNOR@# 为差异恢复,日志恢复进行完整恢复,@#DF@# 差异备份的恢复,@#LOG@# 日志恢复
@filenumber int=1,   --恢复的文件号
@overexist bit=1,        --是否覆盖已经存在的数据库,仅@retype为@#DB@#/@#DBNOR@#是有效
@killuser bit=1,      --是否关闭用户使用进程,仅@overexist=1时有效
@password nvarchar(20)=@#@# --备份文件的密码(仅sql2000支持),如果备份时设置了密码,必须提供此密码
as
declare @sql varchar(8000)
 
--得到恢复后的数据库名
if isnull(@dbname,@#@#)=@#@#
 select @sql=reverse(@bkfile)
  ,@sql=case when charindex(@#.@#,@sql)=0 then @sql
   else substring(@sql,charindex(@#.@#,@sql)+1,1000) end
  ,@sql=case when charindex(@#\@#,@sql)=0 then @sql
   else left(@sql,charindex(@#\@#,@sql)-1) end
  ,@dbname=reverse(@sql)
 
--得到恢复后的数据库存放目录
if isnull(@dbpath,@#@#)=@#@#
begin
 select @dbpath=rtrim(reverse(filename)) from master..sysfiles where name=@#master@#
 select @dbpath=reverse(substring(@dbpath,charindex(@#\@#,@dbpath),4000))
end
 
--生成数据库恢复语句
set @sql=@#restore @#+case @retype when @#LOG@# then @#log @# else @#database @# end+@dbname
 +@# from disk=@#@#@#+@bkfile+@#@#@#@#
 +@# with file=@#+cast(@filenumber as varchar)
 +case when @overexist=1 and @retype in(@#DB@#,@#DBNOR@#) then @#,replace@# else @#@# end
 +case @retype when @#DBNOR@# then @#,NORECOVERY@# else @#,RECOVERY@# end
 +case isnull(@password,@#@#) when @#@# then @#@# else @#,PASSWORD=@#@#@#+@password+@#@#@#@# end
 
--添加移动逻辑文件的处理
if @retype=@#DB@# or @retype=@#DBNOR@#
begin
 --从备份文件中获取逻辑文件名
 declare @lfn nvarchar(128),@tp char(1),@i int,@s varchar(1000)
 
 --创建临时表,保存获取的信息
 create table #tb(ln nvarchar(128),pn nvarchar(260),tp char(1),fgn nvarchar(128),sz numeric(20,0),Msz numeric(20,0))
 --从备份文件中获取信息
 set @s=@#restore filelistonly from disk=@#@#@#+@bkfile+@#@#@#@#
  ++case isnull(@password,@#@#) when @#@# then @#@# else @# with PASSWORD=@#@#@#+@password+@#@#@#@# end
 insert into #tb exec(@s)
 declare #f cursor for select ln,tp from #tb
 open #f
 fetch next from #f into @lfn,@tp
 set @i=0
 while @@fetch_status=0
 begin
  select @sql=@sql+@#,move @#@#@#+@lfn+@#@#@# to @#@#@#+@dbpath+@dbname+cast(@i as varchar)
   +case @tp when @#D@# then @#.mdf@#@#@# else @#.ldf@#@#@# end
   ,@i=@i+1
  fetch next from #f into @lfn,@tp
 end
 close #f
 deallocate #f
end
 
--关闭用户进程处理
if @overexist=1 and @killuser=1
begin
 declare hCForEach cursor for
 select s=@#kill @#+cast(spid as varchar) from master..sysprocesses
 where dbid=db_id(@dbname)
 exec sp_msforeach_worker @#?@#
end
 
--恢复数据库
exec(@sql)
 
Go
邹建说：
说白了,就是备份数据库和还原数据库的SQL语句的应用:
 
--备份
backup database 数据库 to disk=@#c:\你的备份文件名@#
 
--还原
restore database 数据库 from disk=@#c:\你的备份文件名@#
 

PS:邹建老大真是我的偶像阿!

原文转自：http://www.ltesting.net