当前位置：网学 > 设计资源 > 数据库 > 正文

SQLServer数据备份存储过程

论文降重修改服务、格式排版等获取论文论文降重及排版论文发表相关服务

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE Proc [dbo].[SQL_Backup]
@DataBase Nvarchar(50)
AS
DUMP TRANSACTION @DataBase WITH NO_LOG
BACKUP LOG @DataBase WITH NO_LOG
DBCC 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 bit
DECLARE @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 + '_' + @Rnd
CREATE TABLE [#tb](a bit,b bit,c bit)
SET @Temp = @DataPath + @dir
INSERT INTO [#tb] EXECUTE master..xp_FileExist @Temp
IF Exists(SELECT 1 FROM [#tb] WHERE b = 1)
SET @dirin = 1
ELSE
SET @dirin = 0
DROP TABLE [#tb]
SET @Temp = 'md ' + @DataPath + @dir
IF @dirin = 0
BEGIN
EXEC master..xp_cmdshell @Temp
END
SET @DataDescription = 'SQL语句产生的备份,备份时间:' + CONVERT(CHAR(19),GETDATE(),121)
SET @DataMediaName = 'im531 Backup ...'
SET @DataMediaDescription = 'Author im531 ... '
SET @OkInfo = '数据库 ' + @DataBase + ' 成功备份至 ' + @BackupFileName
BACKUP DATABASE @DataBase TO DISK = @BackupFileName
WITH NOINIT , NOUNLOAD , NOSKIP , STATS = 10 , NOFORMAT ,
NAME = @DataBase , DESCRIPTION = @DataDescription ,
MEDIANAME = @DataMediaName , MEDIADESCRIPTION = @DataMediaDescription
-- 7z 压缩
IF @DataBase = 'DataName'
BEGIN
SET @Temp = 'C:\7-zip\7z.exe a -t7z ' + @DataPath + @dir + '.7z ' + @DataPath + @dir + '\* -mx9 -r'
EXEC master..xp_cmdshell @Temp
SET @Temp = 'rd ' + @DataPath + @dir + ' /s/q'
EXEC master..xp_cmdshell @Temp
END
SELECT @OkInfo AS BackupInfo
------------------- End ---------------------
GO
EXEC SQL_Backup @DataBase = N'DataName'