2008恢复数据库存储过程(转)

use Northwind
/*1.--得到数据库的文件目录  
@dbname 指定要取得目录的数据库名  
如果指定的数据不存在,返回安装SQL时设置的默认数据目录  
如果指定NULL,则返回默认的SQL备份目录名  
*/   
/*--调用示例  
select 数据库文件目录=dbo.f_getdbpath('tempdb')  
,[默认SQL SERVER数据目录]=dbo.f_getdbpath('')  
,[默认SQL SERVER备份目录]=dbo.f_getdbpath(null)  
--*/   
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_getdbpath]') and xtype in (N'FN', N'IF', N'TF'))   
drop function [dbo].[f_getdbpath]   
GO   
create function f_getdbpath(@dbname sysname)   
returns nvarchar(260)   
as   
begin   
declare @re nvarchar(260)   
if @dbname is null or db_id(@dbname) is null   
select @re=rtrim(reverse(filename)) from master..sysdatabases where name='master'   
else   
select @re=rtrim(reverse(filename)) from master..sysdatabases where name=@dbname   
if @dbname is null   
set @re=reverse(substring(@re,charindex('\',@re)+5,260))+'BACKUP'   
else   
set @re=reverse(substring(@re,charindex('\',@re),260))   
return(@re)   
end   
go   
 
/*2.--备份数据库  
*/   
/*--调用示例  
--备份当前数据库  
exec p_backupdb @bkpath='c:\',@bkfname='db_\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'  
--*/   
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   
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 --追加/覆盖备份文件   
as   
declare @sql varchar(8000)   
if isnull(@dbname,'')='' set @dbname=db_name()   
if isnull(@bkpath,'')='' set @bkpath=dbo.f_getdbpath(null)   
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   
print @sql   
exec(@sql)   
go   
 
/*3.--恢复数据库  
*/   
/*--调用示例  
--完整恢复数据库  
exec p_RestoreDb @bkfile='c:\db_20031015_db.bak',@dbname='db'
exec p_RestoreDb @bkfile='c:\db_20031015_db.bak',@dbname='db',@dbpath='e:\SouFun_Data\',@retype='DB'
--差异备份恢复  
exec p_RestoreDb @bkfile='c:\db_20031015_db.bak',@dbname='db',@retype='DBNOR'  
exec p_backupdb @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_backupdb @bkfile='c:\db_20031015_log.bak',@dbname='db',@retype='LOG'  
--*/   
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 nvarchar(1000), --定义要恢复的备份文件名   
@dbname sysname='', --定义恢复后的数据库名,默认为备份的文件名   
@dbpath nvarchar(260)='', --恢复后的数据库存放目录,不指定则为SQL的默认数据目录   
@retype nvarchar(10)='DB', --恢复类型:'DB'完事恢复数据库,'DBNOR' 为差异恢复,日志恢复进行完整恢复,'DF' 差异备份的恢复,'LOG' 日志恢复   
@filenumber int=1, --恢复的文件号   
@overexist bit=1, --是否覆盖已经存在的数据库,仅@retype为   
@killuser bit=1 --是否关闭用户使用进程,仅@overexist=1时有效   
as  
exec('use master
exec sp_configure ''show advanced options'', 1
RECONFIGURE
exec sp_configure ''xp_cmdshell'', 1
RECONFIGURE')
declare @path varchar(300)
set @path=@dbpath
set @path='md '+@path
exec xp_cmdshell @path
declare @sql varchar(max)   
--得到恢复后的数据库名   
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,'')='' set @dbpath=dbo.f_getdbpath('')   
--生成数据库恢复语句   
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   
print @sql   
--添加移动逻辑文件的处理   
if @retype='DB' or @retype='DBNOR'   
begin   
--从备份文件中获取逻辑文件名   
declare @lfn nvarchar(128),@pn nvarchar(260),@tp char(1),@i int,@j int
--创建临时表,保存获取的信息   
create table #tb(ln nvarchar(128),pn nvarchar(260),tp char(1),fgn nvarchar(128),sz numeric(20,0),Msz numeric(20,0),FileId int,reateLSN varchar(100),
DropLSN varchar(100),UniqueId varchar(100),ReadOnlyLSN varchar(100),ReadWriteLSN varchar(100),
BackupSizeInBytes bigint,SourceBlockSize bigint,FileGroupId int,LogGroupGUID varchar(300),
DifferentialBaseLSN varchar(100),DifferentialBaseGUID varchar(300),IsReadOnly varchar(2),
IsPresent varchar(2),TDEThumbprint varchar(100))   
--从备份文件中获取信息   
insert into #tb exec('restore filelistonly from disk='''+@bkfile+'''')   
declare #f cursor for select ln,pn,tp from #tb   
open #f   
fetch next from #f into @lfn,@pn,@tp   
set @i=0
set @j=0   
while @@fetch_status=0   
begin
if(right(@pn,3)='mdf')
begin
select @sql=@sql+',move '''+@lfn+''' to '''+@dbpath+@dbname   
+case @tp when 'D' then '.mdf''' else '.ldf''' end  
end
else if(right(@pn,3)='ndf')
begin
select @sql=@sql+',move '''+@lfn+''' to '''+@dbpath+@dbname+cast(@i as varchar)   
+case @tp when 'D' then '.ndf''' else '.ndf''' end
end
else if(right(@pn,3)='ldf')
begin
select @sql=@sql+',move '''+@lfn+''' to '''+@dbpath+@dbname+cast(@i as varchar)  
+case @tp when 'D' then '.ndf''' else '_Log.ldf''' end
set @j=@j+1
end
set @i=@i+1   
fetch next from #f into  @lfn,@pn,@tp   
end   
close #f   
deallocate #f 
end   
--关闭用户进程处理   
if @overexist=1 and @killuser=1   
begin   
declare @spid varchar(20)   
declare #spid cursor for   
select spid=cast(spid as varchar(20)) from master..sysprocesses where dbid=db_id(@dbname)   
open #spid   
fetch next from #spid into @spid   
while @@fetch_status=0   
begin   
exec('kill '+@spid)   
fetch next from #spid into @spid   
end   
close #spid   
deallocate #spid   
end   
--恢复数据库   
exec(@sql) 
exec('use master
exec sp_configure ''show advanced options'', 1
RECONFIGURE
exec sp_configure ''xp_cmdshell'', 0
RECONFIGURE') 
go