今天开园,分享一下我一直在使用的数据备份存储过程,欢迎转载!!!
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博客

SQL Server 数据备份存储过程

最新文章
