现在的位置: 首页 > 综合 > 正文

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

2013年04月28日 ⁄ 综合 ⁄ 共 2708字 ⁄ 字号 评论关闭

 

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

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

抱歉!评论已关闭.