編程實現SQL Server備份與還原

注意，下面備份還原都是用預存程序實現！
 
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:\你的備份檔案名'