今天开园,分享一下我一直在使用的数据备份存储过程,欢迎转载!!!
05 | SET QUOTED_IDENTIFIER OFF |
08 | CREATE Proc [dbo].[SQL_Backup] |
09 | @DataBase Nvarchar(50) |
11 | DUMP TRANSACTION @DataBase WITH NO_LOG |
12 | BACKUP LOG @DataBase WITH NO_LOG |
13 | DBCC SHRINKDATABASE (@DataBase) |
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) |
28 | DECLARE @Temp Nvarchar(500) |
30 | -- Description: SQL_Backup |
34 | --SET @DataBase = 'test' |
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 |
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) |
52 | SET @Temp = 'md ' + @DataPath + @dir |
55 | EXEC master..xp_cmdshell @Temp |
58 | SET @DataDescription = 'SQL语句产生的备份,备份时间:' + CONVERT(CHAR(19),GETDATE(),121) |
59 | SET @DataMediaName = 'im531 Backup ...' |
60 | SET @DataMediaDescription = 'Author im531 ... ' |
61 | SET @OkInfo = '数据库 ' + @DataBase + ' 成功备份至 ' + @BackupFileName |
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 |
70 | IF @DataBase = 'DataName' |
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 |
78 | SELECT @OkInfo AS BackupInfo |
79 | ------------------- End --------------------- |
82 | EXEC SQL_Backup @DataBase = N'DataName' |
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16436858/viewspace-680671/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/16436858/viewspace-680671/