数据库备份存储过程 [SQL Server 2005]

四

2010

数据库备份存储过程 [SQL Server 2005]

发布者: seasun

————————————————————————–
– Author : htl258(Tony)
– Date : 2010-04-18 10:45:14
– 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及以上版本
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’)–这里筛选不参加备份的数据库
EXEC(@S)
GO
–调用方法：
EXEC sp_backupdatabase ‘f:\Backup\tony’
/*
–返回信息：
已为数据库 ‘mydb’，文件 ‘mydb’ (位于文件 1 上)处理了 312 页。
已为数据库 ‘mydb’，文件 ‘mydb_log’ (位于文件 1 上)处理了 1 页。
BACKUP DATABASE 成功处理了 313 页，花费 0.733 秒(3.336 MB/秒)。
已为数据库 ‘test’，文件 ‘test’ (位于文件 1 上)处理了 208 页。
已为数据库 ‘test’，文件 ‘test_log’ (位于文件 1 上)处理了 1 页。
BACKUP DATABASE 成功处理了 209 页，花费 0.413 秒(3.951 MB/秒)。
–备份后的文件列表：
mydb_20100418.bak
test_20100418.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
*/