SQL 实现定期备份数据库

fteair

1. --------------------------------------------------------------------------
   
2. --Subject:数据库批量备份存储过程(判断盘符、路径，错误盘符返回，不存在的路径自动创建)
   
3. --适用环境：SQL Server 2005及以上版本(主要用于作业中调用以及程序开发中调用)
   
4. --------------------------------------------------------------------------
   
5. ---通过使用sp_configure启用‘xp_cmdshell’
   
6. USE [master]
   
7. GO
   
8. sp_configure 'show advanced options',1
   
9. reconfigure
   
10. go
    
11. sp_configure 'xp_cmdshell',1
    
12. reconfigure
    
13. go
    
14. ---下面开始备份数据库操作
    
15. 
    
16. USE master
    
17. GO
    
18. --1.周期性备份数据库代码(保留原来备份的):
    
19. --备份文件名为：原数据库名称+'_'+备份日期.bak
    
20. IF OBJECT_ID('sp_backupdatabase') IS NOT NULL
    
21.     DROP PROC sp_backupdatabase
    
22. GO
    
23. CREATE PROC sp_backupdatabase
    
24. @path NVARCHAR(100)--路径
    
25. AS
    
26. --路径名格式标准化
    
27. IF RIGHT(@path,1)<>'\' SET @path=@path+'\'
    
28. --获取文件夹信息
    
29. DECLARE @t TABLE(id INT IDENTITY,a INT,b INT,c INT)
    
30. DECLARE @fpath NVARCHAR(3)
    
31. SET @fpath=LEFT(@path,3)
    
32. INSERT @t EXEC master..xp_fileexist @fpath
    
33. INSERT @t EXEC master..xp_fileexist @path
    
34. --如果指定盘符有误不存在，则返回错误提示：
    
35. IF EXISTS(SELECT 1 FROM @t WHERE id=1 AND c=0)
    
36. BEGIN
    
37.     RAISERROR(N'输入的盘符不存在，请重新输入!',16,1)
    
38.     RETURN
    
39. END
    
40. --如果不存在指定的文件夹，则创建:
    
41. ELSE IF EXISTS(SELECT 1 FROM @t WHERE b=0 AND id=2)
    
42. BEGIN
    
43.     DECLARE @mddir NVARCHAR(100)
    
44.     SET @mddir='md '+@path
    
45.     EXEC MASTER..xp_cmdshell @mddir
    
46. END
    
47. --开始备份数据库到指定的目录
    
48. DECLARE @s nvarchar(4000)
    
49. SELECT @s=ISNULL(@s+';','')
    
50.         +N'BACKUP database ['+name+'] TO DISK = '''
    
51.         +@path+name+'_'+CONVERT(NVARCHAR(8),getdate(),112)+N'.bak'''
    
52. FROM master..sysdatabases
    
53. WHERE name NOT IN('master','tempdb','model','msdb','pubs')--这里筛选不参加备份的数据库
    
54. AND NAME ='book09'--此处可以指定任意某个数据库，也可以屏蔽该行则全部备份
    
55. EXEC(@S)
    
56. GO
    
57. --调用方法：
    
58. EXEC sp_backupdatabase 'e:\DB\ByDate\testBackupDB'
    
59. /*
    
60. --返回信息：
    
61. 已为数据库 'book09'，文件 'book09_Data' (位于文件 1 上)处理了 224 页。
    
62. 已为数据库 'book09'，文件 'book09_Log' (位于文件 1 上)处理了 1 页。
    
63. BACKUP DATABASE 成功处理了 225 页，花费 0.358 秒(4.910 MB/秒)。
    
64. --备份后的文件列表：
    
65. book09_20130310.bak
    
66. */
    
67. 
    
68. 
    
69. --2.周期性备份数据库代码(自动删除原备份文件):
    
70. --备份文件名为：原数据库名称.bak
    
71. IF OBJECT_ID('sp_backupdatabase') IS NOT NULL
    
72.     DROP PROC sp_backupdatabase
    
73. GO
    
74. CREATE PROC sp_backupdatabase
    
75. @path NVARCHAR(100)--路径
    
76. AS
    
77. --路径名格式标准化
    
78. IF RIGHT(@path,1)<>'\' SET @path=@path+'\'
    
79. --获取文件夹信息
    
80. DECLARE @t TABLE(id INT IDENTITY,a INT,b INT,c INT)
    
81. DECLARE @fpath NVARCHAR(3)
    
82. SET @fpath=LEFT(@path,3)
    
83. INSERT @t EXEC master..xp_fileexist @fpath
    
84. INSERT @t EXEC master..xp_fileexist @path
    
85. --如果指定盘符有误不存在，则返回错误提示：
    
86. IF EXISTS(SELECT 1 FROM @t WHERE id=1 AND c=0)
    
87. BEGIN
    
88.     RAISERROR(N'输入的盘符不存在，请重新输入!',16,1)
    
89.     RETURN
    
90. END
    
91. --如果不存在指定的文件夹，则创建:
    
92. ELSE IF EXISTS(SELECT 1 FROM @t WHERE b=0 AND id=2)
    
93. BEGIN
    
94.     DECLARE @mddir NVARCHAR(100)
    
95.     SET @mddir='md '+@path
    
96.     EXEC master..xp_cmdshell @mddir
    
97. END
    
98. --开始备份数据库到指定的目录
    
99. DECLARE @s nvarchar(4000)
    
100. SELECT @s=ISNULL(@s+';','')
     
101.         +N'BACKUP database ['+name+'] TO DISK = '''
     
102.         +@path+name+N'.bak''  WITH INIT'
     
103. FROM master..sysdatabases
     
104. WHERE name NOT IN('master','tempdb','model','msdb','pubs')--这里筛选不参加备份的数据库
     
105. AND NAME ='book09'--此处可以指定任意某个数据库，也可以屏蔽该行则全部备份
     
106. EXEC(@S)
     
107. GO
     
108. --调用方法：
     
109. EXEC sp_backupdatabase 'e:\DB\ByDate\testBackupDB'
     
110. /*
     
111. --返回信息：
     
112. 已为数据库 'book09'，文件 'book09_Data' (位于文件 1 上)处理了 224 页。
     
113. 已为数据库 'book09'，文件 'book09_Log' (位于文件 1 上)处理了 1 页。
     
114. BACKUP DATABASE 成功处理了 225 页，花费 0.456 秒(3.854 MB/秒)。
     
115. --备份后的文件列表：
     
116. book09.bak
     
117. */

复制代码

张兴康

张兴康

学习了