程式設計實現備份和還原資料庫

下面備份還原都是用儲存過程實現！[@more@]

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

+' to

+'' 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 '

+' from

+' 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

++case isnull(@password,') when ' then ' else ' with 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 to 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:你的備份檔名'