備份的檔案名稱為資料庫名+.bak--鄒建 2003.10--*//*--調用樣本--備份所有使用者資料庫
exec p_backupdb @bkpath='c:/',@dbname=''--備份指定資料庫
exec p_backupdb @bkpath='c:/',@dbname='客戶資料,xzkh_new'
--*/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]
GOcreate proc p_backupdb
@bkpath nvarchar(260)='', --備份檔案的存放目錄,不指定則使用SQL預設的備份目錄
@dbname nvarchar(4000)='' --要備份的資料庫名稱列表,不指定則備份所有使用者資料庫
as
declare @sql varchar(8000)--檢查參數
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
else if right(@bkpath,1)<>'/' set @bkpath=@bkpath+'/'--得到要備份的資料庫列表
if isnull(@dbname,'')=''
declare tb cursor local for
select name from master..sysdatabases where name not in('master','tempdb','model','msdb')
else
declare tb cursor local for
select name from master..sysdatabases
where name not in('master','tempdb','model','msdb')
and(@dbname like '%,'+name+',%' or @dbname like name+',%' or @dbname like '%,'+name)--備份處理
open tb
fetch next from tb into @dbname
while @@fetch_status=0
begin
set @sql='backup database '+@dbname
+' to disk='''+@bkpath+@dbname
+'.bak'' with init'
exec(@sql)
fetch next from tb into @dbname
end
close tb
deallocate tb
go--複製上面備份的所有使用者資料庫到另一台伺服器上.在master資料庫中建立下面的預存程序,然後用它來恢複所有的使用者資料庫./*--恢複指定目錄下的所有資料庫 恢複的資料庫名為備份檔案名(不含副檔名)
備份檔案的副檔名固定為.bak--鄒建 2003.10--*//*--調用樣本
--恢複指定目錄下的所有資料庫
exec p_RestoreDb @bkpath='c:/'--恢複指定目錄下的指定資料庫
exec p_RestoreDb @bkpath='c:/',@bkfile='客戶資料,xzkh_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]
GOcreate proc p_RestoreDb
@bkpath nvarchar(1000)='', --定義備份檔案的存放目錄,預設為SQL的備份目錄
@bkfile nvarchar(4000)='', --定義要恢複的備份檔案名,不含副檔名
@dbpath nvarchar(260)='', --恢複後的資料庫存放目錄,不指定則為SQL的預設資料目錄
@overexist bit=1, --是否覆蓋已經存在的資料庫,僅@retype為'DB'/'DBNOR'是有效
@killuser bit=1 --是否關閉使用者使用進程,僅@overexist=1時有效
as
declare @sql varchar(8000),@dbname sysnameif 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
else if right(@bkpath,1)<>'/' set @bkpath=@bkpath+'/'--得到恢複後的資料庫存放目錄
if isnull(@dbpath,'')=''
begin
select @dbpath=rtrim(reverse(filename)) from master..sysfiles where name='master'
select @dbpath=reverse(substring(@dbpath,charindex('/',@dbpath),4000))
end
else if right(@dbpath,1)<>'/' set @dbpath=@dbpath+'/'--得到指定目錄下的所有備份檔案
create table #t(fname varchar(260),depth int,isf bit)
insert into #t exec master..xp_dirtree @bkpath,1,1if isnull(@bkfile,'')=''
declare tb cursor local for select fn=left(fname,patindex('%.bak',fname)-1) from #t
where isf=1 and fname like '%.bak' --取.bak檔案
else
begin
set @bkfile=','+replace(@bkfile,',','.bak,')+'.bak,'
declare tb cursor local for select fn=left(fname,patindex('%.bak',fname)-1) from #t
where isf=1 and fname like '%.bak' and @bkfile like '%,'+fname+',%'
end--恢複資料庫處理
open tb
fetch next from tb into @dbname
while @@fetch_status=0
begin
--產生資料庫恢複語句
set @sql='restore database '+@dbname
+' from disk='''+@bkpath+@dbname+'.bak'''
+' with RECOVERY'
+case when @overexist=1 then ',replace' else '' end --添加移動邏輯檔案的處理
--從備份檔案中擷取邏輯檔案名稱
declare @lfn nvarchar(128),@tp char(1),@i int --建立暫存資料表,儲存擷取的資訊
create table #tb(ln nvarchar(128),pn nvarchar(260),tp char(1),fgn nvarchar(128),sz numeric(20,0),Msz numeric(20,0))
--從備份檔案中擷取資訊
insert into #tb exec('restore filelistonly from disk='''+@bkpath+@dbname+'.bak''')
declare #f cursor local for select ln,tp from #tb order by tp
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
drop table #tb --關閉使用者進程處理
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)
fetch next from tb into @dbname
end
close tb
deallocate tb
go
