--------------------------------------------------------------------------
-- 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.csdn.net/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