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

/*--備份所有數(shù)據(jù)庫
	
	備份的文件名為數(shù)據(jù)庫名+.bak
	將所有的用戶數(shù)據(jù)庫(或指定的數(shù)據(jù)庫列表)
	備分到指定的目錄下.

--鄒建 2003.10(引用請保留此信息)--*/

/*--調(diào)用示例

--備份所有用戶數(shù)據(jù)庫
exec p_backupdb @bkpath='c:\',@dbname=''

--備份指定數(shù)據(jù)庫
exec p_backupdb @bkpath='c:\',@dbname='客戶資料,xzkh_new'
--*/

create proc p_backupdb
@bkpath nvarchar(260)='',	--備份文件的存放目錄,不指定則使用SQL默認(rèn)的備份目錄
@dbname nvarchar(4000)=''	--要備份的數(shù)據(jù)庫名稱列表,不指定則備份所有用戶數(shù)據(jù)庫
as
declare @sql varchar(8000)

--檢查參數(shù)
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+'\'

--得到要備份的數(shù)據(jù)庫列表
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 format'
	exec(@sql)
	fetch next from tb into @dbname
end
close tb
deallocate tb
go