250cfeoom 2011-07-06

SQL SERVER2000資料庫備份和恢復儲存過程，過程和函式的詳細說明在程式碼中

/*備份資料庫的過程*/

if exists（

select * from sysobjects

where name=‘pr_backup_db’ and xtype=‘p’

）

begin

drop proc pr_backup_db

end

go

create proc pr_backup_db

@flag varchar（20） out，

@backup_db_name varchar（128），

@filename varchar（1000） ——路徑＋檔名字

as

declare @sql nvarchar（4000），@par nvarchar（1000）

if not exists（

select * from master。。sysdatabases

where name=@backup_db_name

）

begin

select @flag=‘db not exist’ /*資料庫不存在*/

return

end

else

begin

if right（@filename，1）<>‘\’ and charindex（‘\’，@filename）<>0

begin

select @par=‘@filename varchar（1000）’

select @sql=‘BACKUP DATABASE ’+@backup_db_name+‘ to disk=@filename with init’

execute sp_executesql @sql，@par，@filename

select @flag=‘ok’

return

end

else

begin

select @flag=‘file type error’ /*引數@filename輸入格式錯誤*/

return

end

end

GO

說明：pr_backup_db過程是備份你的資料庫

/*建立函式，得到檔案得路徑*/

if exists（

select * from sysobjects

where name=‘fn_GetFilePath’ and xtype=‘fn’

）

begin

drop function fn_GetFilePath

end

go

create function fn_GetFilePath（@filename nvarchar（260））

returns nvarchar（260）

as

begin

declare @file_path nvarchar（260）

declare @filename_reverse nvarchar（260）

select @filename_reverse=reverse（@filename）

select @file_path=substring（@filename，1，len（@filename）+1-charindex（‘\’，@filename_reverse））

return @file_path

end

GO

/*恢復資料庫的過程*/

if exists（

select * from sysobjects

where name=‘pr_restore_db’ and xtype=‘p’

）

begin

drop proc pr_restore_db

end

go

CREATE proc pr_restore_db

@flag varchar（20） out， /*過程執行的狀態標誌，是輸入引數*/

@restore_db_name nvarchar（128）， /*要恢復的資料名字*/

@filename nvarchar（260） /*備份檔案存放的路徑+備份檔名字*/

as

declare @proc_result tinyint /*返回系統儲存過程xp_cmdshell執行結果*/

declare @loop_time smallint /*迴圈次數*/

declare @max_ids smallint /*@tem表的ids列最大數*/

declare @file_bak_path nvarchar（260） /*原資料庫存放路徑*/

declare @flag_file bit /*檔案存放標誌*/

declare @master_path nvarchar（260） /*資料庫master檔案路徑*/

declare @sql nvarchar（4000），@par nvarchar（1000）

declare @sql_sub nvarchar（4000）

declare @sql_cmd nvarchar（100）

declare @sql_kill nvarchar（100）

/*

判斷引數@filename檔案格式合法性，以防止使用者輸入類似d： 或者 c：\a\ 等非法檔名

引數@filename裡面必須有‘\’並且不以‘\’結尾

*/

if right（@filename，1）<>‘\’ and charindex（‘\’，@filename）<>0

begin

select @sql_cmd=‘dir ’+@filename

EXEC @proc_result = master。。xp_cmdshell @sql_cmd，no_output

IF （@proc_result<>0） /*系統儲存過程xp_cmdshell返回程式碼值：0（成功）或1（失敗）*/

begin

select @flag=‘not exist’ /*備份檔案不存在*/

return /*退出過程*/

end

/*建立臨時表，儲存由備份集內包含的資料庫和日誌檔案列表組成的結果集*/

create table #tem（

LogicalName nvarchar（128）， /*檔案的邏輯名稱*/

PhysicalName nvarchar（260） ， /*檔案的物理名稱或作業系統名稱*/

Type char（1）， /*資料檔案 （D） 或日誌檔案 （L）*/

FileGroupName nvarchar（128）， /*包含檔案的檔案組名稱*/

［Size］ numeric（20，0）， /*當前大小（以位元組為單位）*/

［MaxSize］ numeric（20，0） /*允許的最大大小（以位元組為單位）*/

）

/*

建立表變數，表結構與臨時表基本一樣

就是多了兩列，

列ids（自增編號列），

列file_path，存放檔案的路徑

*/

declare @tem table（

ids smallint identity， /*自增編號列*/

LogicalName nvarchar（128），

PhysicalName nvarchar（260），

File_path nvarchar（260），

Type char（1），

FileGroupName nvarchar（128）

）

insert into #tem

execute（‘restore filelistonly from disk=’‘’+@filename+‘’‘’）

/*將臨時表匯入表變數中，並且計算出相應得路徑*/

insert into @tem（LogicalName，PhysicalName，File_path，Type，FileGroupName）

select LogicalName，PhysicalName，dbo。fn_GetFilePath（PhysicalName），Type，FileGroupName

from #tem

if @@rowcount>0

begin

drop table #tem

end

select @loop_time=1

select @max_ids=max（ids） /*@tem表的ids列最大數*/

from @tem

while @loop_time<=@max_ids

begin

select @file_bak_path=file_path

from @tem where ids=@loop_time

select @sql_cmd=‘dir ’+@file_bak_path

EXEC @proc_result = master。。xp_cmdshell @sql_cmd，no_output

/*系統儲存過程xp_cmdshell返回程式碼值：0（成功）或1（失敗）*/

IF （@proc_result<>0）

select @loop_time=@loop_time+1

else

BREAK /*沒有找到備份前資料檔案原有存放路徑，退出迴圈*/

end

select @master_path=‘’

if @loop_time>@max_ids

select @flag_file=1 /*備份前資料檔案原有存放路徑存在*/

else

begin

select @flag_file=0 /*備份前資料檔案原有存放路徑不存在*/

select @master_path=dbo。fn_GetFilePath（filename）

from master。。sysdatabases

where name=‘master’

end

select @sql_sub=‘’

/*type=‘d’是資料檔案，type=‘l’是日誌檔案 */

/*@flag_file=1時新的資料庫檔案還是存放在原來路徑，否則存放路徑和master資料庫路徑一樣*/

select @sql_sub=@sql_sub+‘move ’‘’+LogicalName+‘’‘ to ’‘’

+case type

when ‘d’ then case @flag_file

when 1 then File_path

else @master_path

end

when ‘l’ then case @flag_file

when 1 then File_path

else @master_path

end

end

+case type

when ‘d’ then @restore_db_name

+‘_DATA’

+convert（sysname，ids） /*給檔案編號*/

+‘。’

+right（PhysicalName，3） /*給檔案加入字尾名，mdf or ndf*/

+‘’‘，’

when ‘l’ then @restore_db_name

+‘_LOG’

+convert（sysname，ids） /*給檔案編號*/

+‘。’

+right（PhysicalName，3） /*給檔案加入字尾名，mdf or ndf*/

+‘’‘，’

end

from @tem

select @sql=‘RESTORE DATABASE @db_name FROM DISK=@filename with ’

select @sql=@sql+@sql_sub+‘replace’

select @par=‘@db_name nvarchar（128），@filename nvarchar（260）’

/*關閉相關程序，把相應程序狀況匯入臨時表中*/

select identity（int，1，1） ids， spid

into #temp

from master。。sysprocesses

where dbid=db_id（@restore_db_name）

if @@rowcount>0 ——找到相應程序

begin

select @max_ids=max（ids）

from #temp

select @loop_time=1

while @loop_time<=@max_ids

begin

select @sql_kill=‘kill ’+convert（nvarchar（20），spid）

from #temp

where ids=@loop_time

execute sp_executesql @sql_kill

select @loop_time=@loop_time+1

end

end

drop table #temp

execute sp_executesql @sql，@par，@db_name=@restore_db_name，@filename=@filename

select @flag=‘ok’ /*操作成功*/

end

else

begin

SELECT @flag=‘file type error’ /*引數@filename輸入格式錯誤*/

end

GO

——run

——備份資料庫test_database

declare @fl varchar（10）

execute pr_backup_db @fl out，‘test_database’，‘c：\test_database。bak’

select @fl

——恢復資料庫，輸入的引數錯誤

declare @fl varchar（20）

exec pr_restore_db @fl out，‘sa’，‘c：\’

select @fl

——恢復資料庫，即建立資料庫test_database的複本test_db

declare @fl varchar（20）

exec pr_restore_db @fl out，‘test_db’，‘c：\test_database。bak’

select @fl

以上過程和函式在MS SQL2000執行成功，由於MS SQL7不支援使用者自定義函式和表變數，要在MS SQL7下使用可以把函式fn_GetFilePath改寫成過

程，把過程pr_restore_db中的表變數改寫為臨時表即可執行，有興趣的朋友可以試試！

龍天龑 2011-07-05

檢查資料庫時候有這個名字的庫用的。

我心21依舊 2011-07-05

你查詢的好像是有系統庫啊。