編程實現備份與還原資料庫

來源:互聯網
上載者:User
備份|編程|資料|資料庫  注意,下面備份還原都是用預存程序實現!
 
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:鄒建老大真是我的偶像阿!



相關文章

Beyond APAC's No.1 Cloud

19.6% IaaS Market Share in Asia Pacific - Gartner IT Service report, 2018

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。