SQL 实现定期备份数据库

SQL 实现定期备份数据库

转自：http://www.cnblogs.com/jx270/archive/2013/03/10/2952568.html

--------------------------------------------------------------------------
--Subject:数据库批量备份存储过程(判断盘符、路径，错误盘符返回，不存在的路径自动创建)
--适用环境：SQL Server 2005及以上版本(主要用于作业中调用以及程序开发中调用)
--------------------------------------------------------------------------
---通过使用sp_configure启用‘xp_cmdshell’
USE [master]
GO
sp_configure 'show advanced options',1
reconfigure
go
sp_configure 'xp_cmdshell',1
reconfigure
go
---下面开始备份数据库操作
 
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)--路径
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+'_'+CONVERT(NVARCHAR(8),getdate(),112)+N'.bak'''
FROM master..sysdatabases 
WHERE name NOT IN('master','tempdb','model','msdb','pubs')--这里筛选不参加备份的数据库
AND NAME ='book09'--此处可以指定任意某个数据库，也可以屏蔽该行则全部备份
EXEC(@S)
GO
--调用方法：
EXEC sp_backupdatabase 'e:\DB\ByDate\testBackupDB'
/*
--返回信息：
已为数据库 'book09'，文件 'book09_Data' (位于文件 1 上)处理了 224 页。
已为数据库 'book09'，文件 'book09_Log' (位于文件 1 上)处理了 1 页。
BACKUP DATABASE 成功处理了 225 页，花费 0.358 秒(4.910 MB/秒)。
--备份后的文件列表：
book09_20130310.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')--这里筛选不参加备份的数据库
AND NAME ='book09'--此处可以指定任意某个数据库，也可以屏蔽该行则全部备份
EXEC(@S)
GO
--调用方法：
EXEC sp_backupdatabase 'e:\DB\ByDate\testBackupDB'
/*
--返回信息：
已为数据库 'book09'，文件 'book09_Data' (位于文件 1 上)处理了 224 页。
已为数据库 'book09'，文件 'book09_Log' (位于文件 1 上)处理了 1 页。
BACKUP DATABASE 成功处理了 225 页，花费 0.456 秒(3.854 MB/秒)。
--备份后的文件列表：
book09.bak 
*/