【转】SQL Server 数据备份存储过程

SQL Server 数据备份存储过程

view sourceprint?

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'