SQL Server 資料備份預存程序[原創]

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

USE [master]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER OFFGOCREATE Proc [dbo].[SQL_Backup]@DataBase Nvarchar(50)ASDUMP TRANSACTION @DataBase WITH  NO_LOGBACKUP LOG @DataBase WITH NO_LOGDBCC SHRINKDATABASE (@DataBase)---------------------------- 原創SQL備份資料庫 ------------------------------DECLARE @DataBase Nvarchar(100)DECLARE @DataPath Nvarchar(100)DECLARE @FileName Nvarchar(100)   DECLARE @BackupFileName Nvarchar(100)DECLARE @DataDescription Nvarchar(200)DECLARE @DataMediaName Nvarchar(50)DECLARE @DataMediaDescription Nvarchar(200)DECLARE @OkInfo Nvarchar(300)DECLARE @str Nvarchar(300)DECLARE @Rnd Nvarchar(11)DECLARE @dir Nvarchar(15)DECLARE @dirin bitDECLARE @Temp Nvarchar(500)-- Author: im531...-- Description: SQL_Backup-- http://im531.cnblogs.com/-- Date: 2005-02-06--SET @DataBase = 'test'SET @Temp = ''SET @DataPath = 'C:\www\SQL_Backup\'SET @Rnd = (SELECT Left(Newid(),4) + Right(Newid(),4)) + (SELECT Right(Datepart(ms,Getdate()),3))SET @str = RTRIM(CONVERT(CHAR(10),GETDATE(),112)) + '_' + RTRIM(DATEPART(HOUR,GETDATE())) + '_' + LTRIM(DATEPART(MINUTE,GETDATE()))SET @dir = RTRIM(CONVERT(CHAR(10),GETDATE(),112)) + '_' + RTRIM(DATEPART(HOUR,GETDATE()))SET @FileName = @DataPath + @dir + '\'+ @DataBase + '_Backup_'SET @BackupFileName = @FileName + @str  + '_' + @RndCREATE TABLE [#tb](a bit,b bit,c bit)SET @Temp = @DataPath + @dirINSERT INTO [#tb] EXECUTE master..xp_FileExist @TempIF Exists(SELECT 1 FROM [#tb] WHERE b = 1)SET @dirin = 1ELSESET @dirin = 0DROP TABLE [#tb]SET @Temp = 'md ' + @DataPath + @dirIF @dirin = 0BEGINEXEC master..xp_cmdshell @TempENDSET @DataDescription = 'SQL語句產生的備份,備份時間:' + CONVERT(CHAR(19),GETDATE(),121)SET @DataMediaName = 'im531 Backup ...'SET @DataMediaDescription = 'Author im531 ... 'SET @OkInfo = '資料庫 ' + @DataBase + ' 成功備份至 ' + @BackupFileNameBACKUP DATABASE @DataBase TO DISK = @BackupFileNameWITH NOINIT , NOUNLOAD , NOSKIP , STATS = 10 , NOFORMAT ,NAME = @DataBase , DESCRIPTION = @DataDescription , MEDIANAME = @DataMediaName , MEDIADESCRIPTION = @DataMediaDescription-- 7z 壓縮IF @DataBase = 'DataName'BEGINSET @Temp = 'C:\7-zip\7z.exe a -t7z ' + @DataPath + @dir + '.7z ' + @DataPath + @dir + '\* -mx9 -r'EXEC master..xp_cmdshell @TempSET @Temp = 'rd ' + @DataPath + @dir + ' /s/q'EXEC master..xp_cmdshell @TempENDSELECT @OkInfo AS BackupInfo------------------- End ---------------------GOEXEC SQL_Backup@DataBase = N'DataName'