数据库批量备份存储过程(判断盘符、路径，错误盘符返回，不存在的路径自动创建)...

--------------------------------------------------------------------------

-- Author : htl258(Tony)

-- Date : 2010-04-18 10:45:18

-- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)

-- Jul 9 2008 14:43:34

-- Copyright (c) 1988-2008 Microsoft Corporation

-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)

-- Blog :http://blog.youkuaiyun.com/htl258

-- Subject:数据库备份存储过程(判断盘符、路径，错误盘符返回，不存在的路径自动创建)

--------------------------------------------------------------------------

--适用环境：SQL Server 2005及以上版本(主要用于作业中调用以及程序开发中调用)

--新增功能：备份文件保留天数，超出保留天数以外的自动删除。（2010-05-07）

USE master

GO

--1.周期性备份数据库代码(保留原来备份的):

--备份文件名为：原数据库名称+'_'+备份日期.bak

IF OBJECT_ID('sp_backupdatabase') IS NOT NULL

DROP PROC sp_backupdatabase

GO

CREATE PROC sp_backupdatabase

@path NVARCHAR(100),--输入备份路径，如：F:\BackUp

@savedays int=0 --保留天数,值为0时保留所有

AS

SET NOCOUNT ON

--路径名格式标准化

IF RIGHT(@path,1)<>'\' SET @path=@path+'\'

SET @path=REPLACE(@path,'：',':')

--获取文件夹信息

DECLARE @t TABLE(id INT IDENTITY,a INT,b INT,c INT)

DECLARE @fpath NVARCHAR(3)

SET @fpath=LEFT(@path,3)

INSERT @t EXEC master..xp_fileexist @fpath

INSERT @t EXEC master..xp_fileexist @path

--如果指定盘符有误不存在，则返回错误提示：

IF EXISTS(SELECT 1 FROM @t WHERE id=1 AND c=0)

BEGIN

RAISERROR(N'输入的盘符不存在，请重新输入!',16,1)

RETURN

END

--如果不存在指定的文件夹，则创建:

ELSE IF EXISTS(SELECT 1 FROM @t WHERE b=0 AND id=2)

BEGIN

DECLARE @mddir NVARCHAR(100)

SET @mddir='md '+@path

EXEC master..xp_cmdshell @mddir

END

--只保留指定天数的数据，删除指定天数外的数据

IF @savedays>0

BEGIN

DECLARE @tb TABLE(dbname nvarchar(100))

DECLARE @path2 nvarchar(500),@del nvarchar(500)

SET @PATH2='dir '+@path+' /b'

INSERT @tb EXEC master..xp_cmdshell @path2

DECLARE c CURSOR FOR

SELECT 'del '+@path+dbname FROM @tb

WHERE RIGHT(REPLACE(dbname,'.bak',''),CHARINDEX('_',REVERSE(REPLACE(dbname,'.bak','')))-1)

< DATEADD(DD,-@savedays,GETDATE())-1

OPEN c

FETCH c INTO @del

WHILE @@fetch_status=0

BEGIN

EXEC master..xp_cmdshell @del

FETCH c INTO @del

END

CLOSE c

DEALLOCATE c

END

--开始备份数据库到指定的目录

DECLARE @s nvarchar(4000)

SELECT @s=ISNULL(@s+';','')

+N'BACKUP database ['+name+'] TO DISK = '''

+@path+name+'_'+CONVERT(NVARCHAR(8),getdate(),112)+N'.bak'''-- SQL2008 加 WITH COMPRESSION 可压缩备份

FROM master..sysdatabases

WHERE name NOT IN('master','tempdb','model','msdb','pubs')--这里筛选不参加备份的数据库

--WHERE name IN ('db1','db2','db3') --或注释上一行，取消注释本行，选择参加备份的数据库

EXEC(@S)

SET NOCOUNT OFF

GO

--调用方法：

EXEC sp_backupdatabase 'F:\Backup\tony',30 --30 表示保留当前日期（不包括）以前天内的数据，其余自动删除。

/*

--返回信息：

已为数据库'mydb'，文件'mydb' (位于文件上)处理了页。

已为数据库'mydb'，文件'mydb_log' (位于文件上)处理了页。

BACKUP DATABASE 成功处理了页，花费.733 秒(3.336 MB/秒)。

已为数据库'test'，文件'test' (位于文件上)处理了页。

已为数据库'test'，文件'test_log' (位于文件上)处理了页。

BACKUP DATABASE 成功处理了页，花费.413 秒(3.951 MB/秒)。

--备份后的文件列表：

mydb_20100507.bak

test_20100507.bak

*/

--2.周期性备份数据库代码(自动删除原备份文件):

--备份文件名为：原数据库名称.bak

IF OBJECT_ID('sp_backupdatabase') IS NOT NULL

DROP PROC sp_backupdatabase

GO

CREATE PROC sp_backupdatabase

@path NVARCHAR(100)--路径

AS

--路径名格式标准化

IF RIGHT(@path,1)<>'\' SET @path=@path+'\'

--获取文件夹信息

DECLARE @t TABLE(id INT IDENTITY,a INT,b INT,c INT)

DECLARE @fpath NVARCHAR(3)

SET @fpath=LEFT(@path,3)

INSERT @t EXEC master..xp_fileexist @fpath

INSERT @t EXEC master..xp_fileexist @path

--如果指定盘符有误不存在，则返回错误提示：

IF EXISTS(SELECT 1 FROM @t WHERE id=1 AND c=0)

BEGIN

RAISERROR(N'输入的盘符不存在，请重新输入!',16,1)

RETURN

END

--如果不存在指定的文件夹，则创建:

ELSE IF EXISTS(SELECT 1 FROM @t WHERE b=0 AND id=2)

BEGIN

DECLARE @mddir NVARCHAR(100)

SET @mddir='md '+@path

EXEC master..xp_cmdshell @mddir

END

--开始备份数据库到指定的目录

DECLARE @s nvarchar(4000)

SELECT @s=ISNULL(@s+';','')

+N'BACKUP database ['+name+'] TO DISK = '''

+@path+name+N'.bak'' WITH INIT'

FROM master..sysdatabases

WHERE name NOT IN('master','tempdb','model','msdb','pubs')--这里筛选不参加备份的数据库

EXEC(@S)

GO

--调用方法：

EXEC sp_backupdatabase 'f:\Backup\tony2'

/*

--返回信息：

已为数据库'mydb'，文件'mydb' (位于文件1 上)处理了312 页。

已为数据库'mydb'，文件'mydb_log' (位于文件1 上)处理了1 页。

BACKUP DATABASE 成功处理了313 页，花费0.599 秒(4.082 MB/秒)。

已为数据库'test'，文件'test' (位于文件1 上)处理了208 页。

已为数据库'test'，文件'test_log' (位于文件1 上)处理了1 页。

BACKUP DATABASE 成功处理了209 页，花费0.351 秒(4.651 MB/秒)。

--备份后的文件列表：

mydb.bak

test.bak

*/

--以下转自:perfectaction(2010-05-07)

create proc sp_backupdatabase
@bak_path nvarchar(4000)=''