SQL Server 資料備份儲存過程

iSQlServer發表於2010-11-30
SQLServer儲存過程

今天開園,分享一下我一直在使用的資料備份儲存過程,歡迎轉載!!!

01 USE [master]
02 GO
03 SET ANSI_NULLS ON
04 GO
05 SET QUOTED_IDENTIFIER OFF
06 GO
07   
08 CREATE Proc [dbo].[SQL_Backup]
09     @DataBase Nvarchar(50)
10 AS
11 DUMP TRANSACTION @DataBase WITH  NO_LOG
12 BACKUP LOG @DataBase WITH NO_LOG
13 DBCC SHRINKDATABASE (@DataBase)
14   
15 ---------------------------- 原創SQL備份資料庫 ----------------------------
16 --DECLARE @DataBase Nvarchar(100)
17 DECLARE @DataPath Nvarchar(100)
18 DECLARE @FileName Nvarchar(100)   
19 DECLARE @BackupFileName Nvarchar(100)
20 DECLARE @DataDescription Nvarchar(200)
21 DECLARE @DataMediaName Nvarchar(50)
22 DECLARE @DataMediaDescription Nvarchar(200)
23 DECLARE @OkInfo Nvarchar(300)
24 DECLARE @str Nvarchar(300)
25 DECLARE @Rnd Nvarchar(11)
26 DECLARE @dir Nvarchar(15)
27 DECLARE @dirin bit
28 DECLARE @Temp Nvarchar(500)
29 -- Author: im531...
30 -- Description: SQL_Backup
31 -- http://im531.cnblogs.com/
32 -- Date: 2005-02-06
33   
34 --SET @DataBase = 'test'
35 SET @Temp = ''
36 SET @DataPath = 'C:\www\SQL_Backup\'
37 SET @Rnd = (SELECT Left(Newid(),4) + Right(Newid(),4)) + (SELECT Right(Datepart(ms,Getdate()),3))
38 SET @str = RTRIM(CONVERT(CHAR(10),GETDATE(),112)) + '_' + RTRIM(DATEPART(HOUR,GETDATE())) + '_' + LTRIM(DATEPART(MINUTE,GETDATE()))
39 SET @dir = RTRIM(CONVERT(CHAR(10),GETDATE(),112)) + '_' + RTRIM(DATEPART(HOUR,GETDATE()))
40 SET @FileName = @DataPath + @dir + '\'+ @DataBase + '_Backup_'
41 SET @BackupFileName = @FileName + @str  + '_' + @Rnd
42   
43 CREATE TABLE [#tb](a bit,b bit,c bit)
44 SET @Temp = @DataPath + @dir
45 INSERT INTO [#tb] EXECUTE master..xp_FileExist @Temp
46 IF Exists(SELECT 1 FROM [#tb] WHERE b = 1)
47     SET @dirin = 1
48 ELSE
49     SET @dirin = 0
50 DROP TABLE [#tb]
51   
52 SET @Temp = 'md ' + @DataPath + @dir
53 IF @dirin = 0
54     BEGIN
55         EXEC master..xp_cmdshell @Temp
56     END
57   
58 SET @DataDescription = 'SQL語句產生的備份,備份時間:' + CONVERT(CHAR(19),GETDATE(),121)
59 SET @DataMediaName = 'im531 Backup ...'
60 SET @DataMediaDescription = 'Author im531 ... '
61 SET @OkInfo = '資料庫 ' + @DataBase + ' 成功備份至 ' + @BackupFileName
62   
63 BACKUP DATABASE @DataBase TO DISK = @BackupFileName
64 WITH NOINIT , NOUNLOAD , NOSKIP , STATS = 10 , NOFORMAT ,
65 NAME = @DataBase , DESCRIPTION = @DataDescription , 
66 MEDIANAME = @DataMediaName , MEDIADESCRIPTION = @DataMediaDescription
67   
68   
69 -- 7z 壓縮
70 IF @DataBase = 'DataName'
71     BEGIN
72         SET @Temp = 'C:\7-zip\7z.exe a -t7z ' + @DataPath + @dir + '.7z ' + @DataPath + @dir + '\* -mx9 -r'
73         EXEC master..xp_cmdshell @Temp
74         SET @Temp = 'rd ' + @DataPath + @dir + ' /s/q'
75         EXEC master..xp_cmdshell @Temp
76     END
77   
78 SELECT @OkInfo AS BackupInfo
79 ------------------- End ---------------------
80 GO
81   
82 EXEC SQL_Backup @DataBase = N'DataName'

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-680671/，如需轉載，請註明出處，否則將追究法律責任。

相關文章