跟踪
--============================================================================
--新建两个目录 D:\InOut\TraceDB D:\InOut\TraceLog\
--建数据库，建跟踪执行时间超过1.5秒的语句及SP
--建作业，每天在固定时间将跟踪文件导入数据库
--============================================================================
USE [master]
GO
/****** Object: Database [TraceDB] Script Date: 2017/2/15 11:16:02 ******/
CREATE DATABASE [TraceDB]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'TraceDB', FILENAME = N'D:\inout\TraceDB\TraceDB.mdf' , SIZE = 102400KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB )
LOG ON
( NAME = N'TraceDB_log', FILENAME = N'D:\inout\TraceDB\TraceDB_log.ldf' , SIZE = 20416KB , MAXSIZE = 2048GB , FILEGROWTH = 102400KB )
GO
--ALTER DATABASE [TraceDB] SET COMPATIBILITY_LEVEL = 120
--GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [TraceDB].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [TraceDB] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [TraceDB] SET ANSI_NULLS OFF
GO
ALTER DATABASE [TraceDB] SET ANSI_PADDING OFF
GO
ALTER DATABASE [TraceDB] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [TraceDB] SET ARITHABORT OFF
GO
ALTER DATABASE [TraceDB] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [TraceDB] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [TraceDB] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [TraceDB] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [TraceDB] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [TraceDB] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [TraceDB] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [TraceDB] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [TraceDB] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [TraceDB] SET ENABLE_BROKER
GO
ALTER DATABASE [TraceDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [TraceDB] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [TraceDB] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [TraceDB] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [TraceDB] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [TraceDB] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [TraceDB] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [TraceDB] SET RECOVERY FULL
GO
ALTER DATABASE [TraceDB] SET MULTI_USER
GO
ALTER DATABASE [TraceDB] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [TraceDB] SET DB_CHAINING OFF
GO
ALTER DATABASE [TraceDB] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
GO
ALTER DATABASE [TraceDB] SET TARGET_RECOVERY_TIME = 0 SECONDS
GO
ALTER DATABASE [TraceDB] SET DELAYED_DURABILITY = DISABLED
GO
EXEC sys.sp_db_vardecimal_storage_format N'TraceDB', N'ON'
GO
USE [TraceDB]
GO
/****** Object: UserDefinedFunction [dbo].[Split] Script Date: 2017/2/15 11:16:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO --表值函数用以截取字符串
--如果为其添加一列主键id,则其顺序就会固定了
create FUNCTION [dbo].[Split](@text NVARCHAR(max))
RETURNS @tempTable TABLE(value NVARCHAR(1000))
AS
BEGIN
DECLARE @StartIndex INT --开始查找的位置
DECLARE @FindIndex INT --找到的位置
DECLARE @Content VARCHAR(4000) --找到的值
--初始化一些变量
SET @StartIndex = 1 --T-SQL中字符串的查找位置是从1开始的
SET @FindIndex=0 --开始循环查找字符串逗号
WHILE(@StartIndex <= LEN(@Text))
BEGIN
--查找字符串函数 CHARINDEX 第一个参数是要找的字符串
-- 第二个参数是在哪里查找这个字符串
-- 第三个参数是开始查找的位置
--返回值是找到字符串的位置
SELECT @FindIndex = CHARINDEX(',' ,@Text,@StartIndex)
--判断有没找到 没找到返回0
IF(@FindIndex =0 OR @FindIndex IS NULL)
BEGIN
--如果没有找到就表示找完了
SET @FindIndex = LEN(@Text)+1
END
--截取字符串函数 SUBSTRING 第一个参数是要截取的字符串
-- 第二个参数是开始的位置
-- 第三个参数是截取的长度
SET @Content =SUBSTRING(@Text,@StartIndex,@FindIndex-@StartIndex)
--初始化下次查找的位置
SET @StartIndex = @FindIndex+1
--把找的的值插入到要返回的Table类型中
INSERT INTO @tempTable (Value) VALUES (@Content)
END
RETURN
END GO
/****** Object: Table [dbo].[CommandLog] Script Date: 2017/2/15 11:16:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CommandLog](
[ID] [int] IDENTITY(1,1) NOT NULL,
[DatabaseName] [sysname] NULL,
[SchemaName] [sysname] NULL,
[ObjectName] [sysname] NULL,
[ObjectType] [char](2) NULL,
[IndexName] [sysname] NULL,
[IndexType] [tinyint] NULL,
[StatisticsName] [sysname] NULL,
[PartitionNumber] [int] NULL,
[ExtendedInfo] [xml] NULL,
[Command] [nvarchar](max) NOT NULL,
[CommandType] [nvarchar](60) NOT NULL,
[StartTime] [datetime] NOT NULL,
[EndTime] [datetime] NULL,
[ErrorNumber] [int] NULL,
[ErrorMessage] [nvarchar](max) NULL,
CONSTRAINT [PK_CommandLog] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[TraceLog] Script Date: 2017/2/15 11:16:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TraceLog](
[RowNumber] [int] IDENTITY(0,1) NOT NULL,
[EventClass] [int] NULL,
[Duration] [bigint] NULL,
[TextData] [ntext] NULL,
[SPID] [int] NULL,
[BinaryData] [image] NULL,
[CPU] [int] NULL,
[EndTime] [datetime] NULL,
[ObjectName] [nvarchar](128) NULL,
[StartTime] [datetime] NULL,
[Reads] [bigint] NULL,
[Writes] [bigint] NULL,
[DataBaseName] [nvarchar](256) NULL,
[ApplicationName] [nvarchar](256) NULL,
[HostName] [nvarchar](256) NULL,
PRIMARY KEY CLUSTERED
(
[RowNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO
/****** Object: StoredProcedure [dbo].[CommandExecute] Script Date: 2017/2/15 11:16:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO CREATE PROCEDURE [dbo].[CommandExecute] @Command nvarchar(max),
@CommandType nvarchar(max),
@Mode int,
@Comment nvarchar(max) = NULL,
@DatabaseName nvarchar(max) = NULL,
@SchemaName nvarchar(max) = NULL,
@ObjectName nvarchar(max) = NULL,
@ObjectType nvarchar(max) = NULL,
@IndexName nvarchar(max) = NULL,
@IndexType int = NULL,
@StatisticsName nvarchar(max) = NULL,
@PartitionNumber int = NULL,
@ExtendedInfo xml = NULL,
@LogToTable bit,
@Exec bit
AS
BEGIN SET NOCOUNT ON DECLARE @StartMessage nvarchar(max)
DECLARE @EndMessage nvarchar(max)
DECLARE @ErrorMessage nvarchar(max)
DECLARE @ErrorMessageOriginal nvarchar(max) DECLARE @StartTime datetime
DECLARE @EndTime datetime DECLARE @StartTimeSec datetime
DECLARE @EndTimeSec datetime DECLARE @ID int DECLARE @Error int
DECLARE @ReturnCode int SET @Error = 0
SET @ReturnCode = 0 ----------------------------------------------------------------------------------------------------
--// Check core requirements //--
---------------------------------------------------------------------------------------------------- IF @LogToTable = 1 AND NOT EXISTS (SELECT * FROM sys.objects objects INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] WHERE objects.[type] = 'U' AND schemas.[name] = 'dbo' AND objects.[name] = 'CommandLog')
BEGIN
SET @ErrorMessage = 'The table CommandLog is missing. Download https://ola.hallengren.com/scripts/CommandLog.sql.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END IF @Error <> 0
BEGIN
SET @ReturnCode = @Error
GOTO ReturnCode
END ----------------------------------------------------------------------------------------------------
--// Check input parameters //--
---------------------------------------------------------------------------------------------------- IF @Command IS NULL OR @Command = ''
BEGIN
SET @ErrorMessage = 'The value for the parameter @Command is not supported.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END IF @CommandType IS NULL OR @CommandType = '' OR LEN(@CommandType) > 60
BEGIN
SET @ErrorMessage = 'The value for the parameter @CommandType is not supported.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END IF @Mode NOT IN(1,2) OR @Mode IS NULL
BEGIN
SET @ErrorMessage = 'The value for the parameter @Mode is not supported.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END IF @Error <> 0
BEGIN
SET @ReturnCode = @Error
GOTO ReturnCode
END ----------------------------------------------------------------------------------------------------
--// Log initial information //--
---------------------------------------------------------------------------------------------------- SET @StartTime = GETDATE()
SET @StartTimeSec = CONVERT(datetime,CONVERT(nvarchar,@StartTime,120),120) IF @LogToTable=0
BEGIN
SET @StartMessage = 'Date and time: ' + CONVERT(nvarchar,@StartTimeSec,120) + CHAR(13) + CHAR(10)
SET @StartMessage = @StartMessage + 'Command: ' + @Command
IF @Comment IS NOT NULL SET @StartMessage = @StartMessage + CHAR(13) + CHAR(10) + 'Comment: ' + @Comment
SET @StartMessage = REPLACE(@StartMessage,'%','%%')
RAISERROR(@StartMessage,10,1) WITH NOWAIT
END
IF @LogToTable = 1
BEGIN
INSERT INTO dbo.CommandLog (DatabaseName, SchemaName, ObjectName, ObjectType, IndexName, IndexType, StatisticsName, PartitionNumber, ExtendedInfo, CommandType, Command, StartTime)
VALUES (@DatabaseName, @SchemaName, @ObjectName, @ObjectType, @IndexName, @IndexType, @StatisticsName, @PartitionNumber, @ExtendedInfo, @CommandType, @Command, @StartTime)
END SET @ID = SCOPE_IDENTITY() ----------------------------------------------------------------------------------------------------
--// Execute command //--
---------------------------------------------------------------------------------------------------- IF @Mode = 1 AND @Exec = 1
BEGIN
EXECUTE(@Command)
SET @Error = @@ERROR
SET @ReturnCode = @Error
END IF @Mode = 2 AND @Exec =1
BEGIN
BEGIN TRY
EXECUTE(@Command)
END TRY
BEGIN CATCH
SET @Error = ERROR_NUMBER()
SET @ReturnCode = @Error
SET @ErrorMessageOriginal = ERROR_MESSAGE()
SET @ErrorMessage = 'Msg ' + CAST(@Error AS nvarchar) + ', ' + ISNULL(@ErrorMessageOriginal,'')
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
END CATCH
END ----------------------------------------------------------------------------------------------------
--// Log completing information //--
---------------------------------------------------------------------------------------------------- SET @EndTime = GETDATE()
SET @EndTimeSec = CONVERT(datetime,CONVERT(varchar,@EndTime,120),120) IF @LogToTable=0
BEGIN
SET @EndMessage = 'Outcome: ' + CASE WHEN @Exec = 0 THEN 'Not Executed' WHEN @Error = 0 THEN 'Succeeded' ELSE 'Failed' END + CHAR(13) + CHAR(10)
SET @EndMessage = @EndMessage + 'Duration: ' + CASE WHEN DATEDIFF(ss,@StartTimeSec, @EndTimeSec)/(24*3600) > 0 THEN CAST(DATEDIFF(ss,@StartTimeSec, @EndTimeSec)/(24*3600) AS nvarchar) + '.' ELSE '' END + CONVERT(nvarchar,@EndTimeSec - @StartTimeSec,108) + CHAR(13) + CHAR(10)
--SET @EndMessage = @EndMessage + 'Date and time: ' + CONVERT(nvarchar,@EndTimeSec,120) + CHAR(13) + CHAR(10) + ' '
SET @EndMessage = REPLACE(@EndMessage,'%','%%')
RAISERROR(@EndMessage,10,1) WITH NOWAIT
END IF @LogToTable = 1
BEGIN
UPDATE dbo.CommandLog
SET EndTime = @EndTime,
ErrorNumber = CASE WHEN @Exec = 0 THEN NULL ELSE @Error END,
ErrorMessage = @ErrorMessageOriginal
WHERE ID = @ID
END ReturnCode:
IF @ReturnCode <> 0
BEGIN
RETURN @ReturnCode
END ---------------------------------------------------------------------------------------------------- END GO
/****** Object: StoredProcedure [dbo].[DataBaseBackup] Script Date: 2017/2/15 11:16:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO /****** Object: StoredProcedure [dbo].[DataBaseBackup] Script Date: 2015/11/5 9:06:43 ******/
create PROCEDURE [dbo].[DataBaseBackup]
@databases NVARCHAR(1000) ,
@directory NVARCHAR(MAX) = 'X:\Backup' ,
@BackupType NVARCHAR(MAX) ,/*FULL,DIFF,LOG*//*简写:D,I,L*/
@Verify BIT = 1 ,
@Compress BIT = 1 ,
@copyOnly BIT = 0 ,
@LogToTable BIT = 0,
@exec BIT = 0
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Description NVARCHAR(MAX) = NULL ,
@NumberOfFiles INT = NULL ,
@CheckSum BIT = 0 ,
@OverrideBackupPreference NVARCHAR(MAX) = 0 ,
@ReadWriteFileGroups NVARCHAR(MAX) = 0 ,
@Threads INT = NULL ,
@NoRecovery NVARCHAR(MAX) = 0;
-- 声明变量
BEGIN
DECLARE @Version NUMERIC(18, 10);
DECLARE @Cluster NVARCHAR(MAX); DECLARE @StartMessage NVARCHAR(MAX);
DECLARE @EndMessage NVARCHAR(MAX);
DECLARE @DatabaseMessage NVARCHAR(MAX);
DECLARE @ErrorMessage NVARCHAR(MAX); DECLARE @Error INT;
DECLARE @ReturnCode INT; DECLARE @CurrentBackupSet TABLE
(
ID INT IDENTITY
PRIMARY KEY ,
VerifyCompleted BIT ,
VerifyOutput INT
); DECLARE @CurrentFiles TABLE
(
[Type] NVARCHAR(MAX) ,
FilePath NVARCHAR(MAX)
); DECLARE @CurrentDirectories TABLE
(
ID INT PRIMARY KEY ,
DirectoryPath NVARCHAR(MAX) ,
CleanupDate DATETIME ,
CleanupMode NVARCHAR(MAX) ,
CreateCompleted BIT ,
CleanupCompleted BIT ,
CreateOutput INT ,
CleanupOutput INT
); -- 存放选择的数据库
DECLARE @SelectedDatabases TABLE
(
DatabaseName NVARCHAR(MAX) ,
DatabaseType NVARCHAR(MAX) ,
Selected BIT
); DECLARE @DirectoryInfo TABLE
(
FileExists BIT ,
FileIsADirectory BIT ,
ParentDirectoryExists BIT
); -- 存放所有数据库
DECLARE @tmpDatabases TABLE
(
ID INT IDENTITY ,
DatabaseName NVARCHAR(MAX) ,
DatabaseNameFS NVARCHAR(MAX) ,
DatabaseType NVARCHAR(MAX) ,
Selected BIT ,
Completed BIT ,
PRIMARY KEY ( Selected, Completed, ID )
); -- 存放备份目录
DECLARE @Directories TABLE
(
ID INT PRIMARY KEY ,
DirectoryPath NVARCHAR(MAX) ,
Mirror BIT ,
Completed BIT
);
DECLARE @CurrentRootDirectoryID INT;
DECLARE @CurrentRootDirectoryPath NVARCHAR(4000); DECLARE @CurrentDBID INT;
DECLARE @CurrentDatabaseID INT;
DECLARE @CurrentDatabaseName NVARCHAR(MAX);
DECLARE @CurrentBackupType NVARCHAR(MAX);
DECLARE @CurrentFileExtension NVARCHAR(MAX);
DECLARE @CurrentFileNumber INT;
-- 生成的文件名
DECLARE @fileName NVARCHAR(MAX);
DECLARE @CurrentDifferentialBaseLSN NUMERIC(25, 0);
DECLARE @CurrentDifferentialBaseIsSnapshot BIT;
DECLARE @CurrentLogLSN NUMERIC(25, 0);
DECLARE @CurrentLatestBackup DATETIME;
DECLARE @CurrentDatabaseNameFS NVARCHAR(MAX);
DECLARE @CurrentDirectoryID INT;
DECLARE @CurrentDirectoryPath NVARCHAR(MAX);
DECLARE @CurrentFilePath NVARCHAR(MAX);
DECLARE @CurrentDate DATETIME;
DECLARE @CurrentCleanupDate DATETIME;
DECLARE @CurrentIsDatabaseAccessible BIT;
DECLARE @CurrentAvailabilityGroup NVARCHAR(MAX);
DECLARE @CurrentAvailabilityGroupRole NVARCHAR(MAX);
DECLARE @CurrentAvailabilityGroupBackupPreference NVARCHAR(MAX);
DECLARE @CurrentIsPreferredBackupReplica BIT;
DECLARE @CurrentDatabaseMirroringRole NVARCHAR(MAX);
DECLARE @CurrentLogShippingRole NVARCHAR(MAX);
DECLARE @CurrentBackupSetID INT;
DECLARE @CurrentIsMirror BIT; DECLARE @CurrentCommand01 NVARCHAR(MAX);
DECLARE @CurrentCommand03 NVARCHAR(MAX);
DECLARE @CurrentCommand04 NVARCHAR(MAX); DECLARE @CurrentCommandOutput01 INT;
DECLARE @CurrentCommandOutput03 INT;
DECLARE @CurrentCommandOutput04 INT; DECLARE @CurrentCommandType01 NVARCHAR(MAX);
DECLARE @CurrentCommandType03 NVARCHAR(MAX);
DECLARE @CurrentCommandType04 NVARCHAR(MAX);
END; SET @Version = CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(MAX)),
CHARINDEX('.', CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(MAX))) - 1) + '.'
+ REPLACE(RIGHT(CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(MAX)),
LEN(CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(MAX))) - CHARINDEX('.',
CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(MAX)))),
'.', '') AS NUMERIC(18, 10)); IF @Version >= 11
BEGIN
SELECT @Cluster = cluster_name
FROM sys.dm_hadr_cluster;
END; WITH db1 ( dbname )
AS ( SELECT value AS dbname
FROM dbo.Split(@databases)
),
db2
AS ( SELECT CASE WHEN dbname LIKE '-%' THEN RIGHT(dbname, LEN(dbname) - 1)
ELSE dbname
END AS dbname, CASE WHEN dbname LIKE '-%' THEN 0
ELSE 1
END AS selected
FROM db1
),
db3
AS ( SELECT CASE WHEN dbname IN ( 'ALL_DATABASES', 'SYSTEM_DATABASES', 'USER_DATABASES' ) THEN '%'
ELSE dbname
END AS dbname, CASE WHEN dbname = 'SYSTEM_DATABASES' THEN 'S'
WHEN dbname = 'USER_DATABASES' THEN 'U'
ELSE NULL
END AS DatabaseType, selected
FROM db2
)
INSERT INTO @SelectedDatabases ( DatabaseName, DatabaseType, Selected )
SELECT dbname, DatabaseType, selected
FROM db3
OPTION ( MAXRECURSION 0 ); INSERT INTO @tmpDatabases ( DatabaseName, DatabaseNameFS, DatabaseType, Selected, Completed )
SELECT [name] AS DatabaseName,
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE([name], '\', ''), '/', ''),
':', ''), '*', ''), '?', ''), '"', ''), '<',
''), '>', ''), '|', ''), ' ', '') AS DatabaseNameFS,
CASE WHEN name IN ( 'master', 'msdb', 'model' ) THEN 'S'
ELSE 'U'
END AS DatabaseType, 0 AS Selected, 0 AS Completed
FROM sys.databases
WHERE [name] <> 'tempdb'
AND source_database_id IS NULL
ORDER BY [name] ASC; -- 先添加要备份的数据库
UPDATE tmpDatabases
SET tmpDatabases.Selected = SelectedDatabases.Selected
FROM @tmpDatabases tmpDatabases
INNER JOIN @SelectedDatabases SelectedDatabases ON tmpDatabases.DatabaseName LIKE REPLACE(SelectedDatabases.DatabaseName,
'_', '[_]')
AND ( tmpDatabases.DatabaseType = SelectedDatabases.DatabaseType
OR SelectedDatabases.DatabaseType IS NULL )
WHERE SelectedDatabases.Selected = 1; -- 再排除不要备份的数据库
UPDATE tmpDatabases
SET tmpDatabases.Selected = SelectedDatabases.Selected
FROM @tmpDatabases tmpDatabases
INNER JOIN @SelectedDatabases SelectedDatabases ON tmpDatabases.DatabaseName LIKE REPLACE(SelectedDatabases.DatabaseName,
'_', '[_]')
AND ( tmpDatabases.DatabaseType = SelectedDatabases.DatabaseType
OR SelectedDatabases.DatabaseType IS NULL )
WHERE SelectedDatabases.Selected = 0; IF @databases IS NULL
OR NOT EXISTS ( SELECT *
FROM @SelectedDatabases )
OR EXISTS ( SELECT *
FROM @SelectedDatabases
WHERE DatabaseName IS NULL
OR DatabaseName = '' )
BEGIN
SET @ErrorMessage = 'The value for the parameter @Databases is not supported.' + CHAR(13) + CHAR(10) + ' ';
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT;
SET @Error = @@ERROR;
END; ----------------------------------------------------------------------------------------------------
--// Check database names //--
---------------------------------------------------------------------------------------------------- SET @ErrorMessage = '';
SELECT @ErrorMessage = @ErrorMessage + QUOTENAME(DatabaseName) + ', '
FROM @tmpDatabases
WHERE Selected = 1
AND DatabaseNameFS = ''
ORDER BY DatabaseName ASC;
IF @@ROWCOUNT > 0
BEGIN
SET @ErrorMessage = 'The names of the following databases are not supported: ' + LEFT(@ErrorMessage,
LEN(@ErrorMessage) - 1)
+ '.' + CHAR(13) + CHAR(10) + ' ';
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT;
SET @Error = @@ERROR;
END; SET @ErrorMessage = '';
SELECT @ErrorMessage = @ErrorMessage + QUOTENAME(DatabaseName) + ', '
FROM @tmpDatabases
WHERE UPPER(DatabaseNameFS) IN ( SELECT UPPER(DatabaseNameFS)
FROM @tmpDatabases
GROUP BY UPPER(DatabaseNameFS)
HAVING COUNT(*) > 1 )
AND UPPER(DatabaseNameFS) IN ( SELECT UPPER(DatabaseNameFS)
FROM @tmpDatabases
WHERE Selected = 1 )
AND DatabaseNameFS <> ''
ORDER BY DatabaseName ASC
OPTION ( RECOMPILE ); IF @@ROWCOUNT > 0
BEGIN
SET @ErrorMessage = 'The names of the following databases are not unique in the file system: '
+ LEFT(@ErrorMessage, LEN(@ErrorMessage) - 1) + '.' + CHAR(13) + CHAR(10) + ' ';
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT;
SET @Error = @@ERROR;
END; INSERT INTO @Directories ( ID, DirectoryPath, Completed )
SELECT ROW_NUMBER() OVER ( ORDER BY value ASC ) AS ID, value, 0
FROM dbo.Split(@directory)
OPTION ( MAXRECURSION 0 ); IF EXISTS ( SELECT *
FROM @Directories
WHERE ( NOT ( DirectoryPath LIKE '_:'
OR DirectoryPath LIKE '_:\%'
OR DirectoryPath LIKE '\\%\%' )
OR DirectoryPath IS NULL
OR LEFT(DirectoryPath, 1) = ' '
OR RIGHT(DirectoryPath, 1) = ' ' ) )
BEGIN
SET @ErrorMessage = 'The value for the parameter @Directory is not supported.' + CHAR(13) + CHAR(10) + ' ';
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT;
SET @Error = @@ERROR;
END; -- 校验备份目录
WHILE EXISTS ( SELECT *
FROM @Directories
WHERE Completed = 0 )
BEGIN
SELECT TOP 1
@CurrentRootDirectoryID = ID, @CurrentRootDirectoryPath = DirectoryPath
FROM @Directories
WHERE Completed = 0
ORDER BY ID ASC; INSERT INTO @DirectoryInfo ( FileExists, FileIsADirectory, ParentDirectoryExists )
EXECUTE [master].dbo.xp_fileexist @CurrentRootDirectoryPath; IF NOT EXISTS ( SELECT *
FROM @DirectoryInfo
WHERE FileExists = 0
AND FileIsADirectory = 1
AND ParentDirectoryExists = 1 )
BEGIN
SET @ErrorMessage = 'The directory ' + @CurrentRootDirectoryPath + ' does not exist.' + CHAR(13)
+ CHAR(10) + ' ';
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT;
SET @Error = @@ERROR;
END; UPDATE @Directories
SET Completed = 1
WHERE ID = @CurrentRootDirectoryID; SET @CurrentRootDirectoryID = NULL;
SET @CurrentRootDirectoryPath = NULL; DELETE FROM @DirectoryInfo;
END; IF @Compress IS NULL
BEGIN
SELECT @Compress = CASE WHEN EXISTS ( SELECT *
FROM sys.configurations
WHERE name = 'backup compression default'
AND value_in_use = 1 ) THEN 1
ELSE 0
END;
END; IF @NumberOfFiles IS NULL
BEGIN
SELECT @NumberOfFiles = ( SELECT COUNT (*) FROM @Directories
);
END; IF @BackupType NOT IN ( 'FULL', 'DIFF', 'LOG' )
OR @BackupType IS NULL
BEGIN
SET @ErrorMessage = 'The value for the parameter @BackupType is not supported.' + CHAR(13) + CHAR(10) + ' ';
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT;
SET @Error = @@ERROR;
END; IF @Error <> 0
BEGIN
--SET @ErrorMessage = 'The documentation is available at https://ola.hallengren.com/sql-server-backup.html.' + CHAR(13) + CHAR(10) + ' '
--RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @ReturnCode = @Error;
GOTO Logging;
END; WHILE EXISTS ( SELECT *
FROM @tmpDatabases
WHERE Selected = 1
AND Completed = 0 )
BEGIN
SELECT TOP 1
@CurrentDBID = ID, @CurrentDatabaseName = DatabaseName, @CurrentDatabaseNameFS = DatabaseNameFS
FROM @tmpDatabases
WHERE Selected = 1
AND Completed = 0
ORDER BY ID ASC; SET @CurrentDatabaseID = DB_ID(@CurrentDatabaseName); IF DATABASEPROPERTYEX(@CurrentDatabaseName, 'Status') = 'ONLINE'
BEGIN
IF EXISTS ( SELECT *
FROM sys.database_recovery_status
WHERE database_id = @CurrentDatabaseID
AND database_guid IS NOT NULL )
BEGIN
SET @CurrentIsDatabaseAccessible = 1;
END;
ELSE
BEGIN
SET @CurrentIsDatabaseAccessible = 0;
END;
END; SELECT @CurrentDifferentialBaseLSN = differential_base_lsn
FROM sys.master_files
WHERE database_id = @CurrentDatabaseID
AND [type] = 0
AND [file_id] = 1; IF DATABASEPROPERTYEX(@CurrentDatabaseName, 'Status') = 'ONLINE'
BEGIN
SELECT @CurrentLogLSN = last_log_backup_lsn
FROM sys.database_recovery_status
WHERE database_id = @CurrentDatabaseID;
END; SET @CurrentBackupType = @BackupType;
IF @Version >= 11
AND @Cluster IS NOT NULL
BEGIN
SELECT @CurrentAvailabilityGroup = availability_groups.name,
@CurrentAvailabilityGroupRole = dm_hadr_availability_replica_states.role_desc,
@CurrentAvailabilityGroupBackupPreference = UPPER(availability_groups.automated_backup_preference_desc)
FROM sys.databases databases
INNER JOIN sys.availability_databases_cluster availability_databases_cluster ON databases.group_database_id = availability_databases_cluster.group_database_id
INNER JOIN sys.availability_groups availability_groups ON availability_databases_cluster.group_id = availability_groups.group_id
INNER JOIN sys.dm_hadr_availability_replica_states dm_hadr_availability_replica_states ON availability_groups.group_id = dm_hadr_availability_replica_states.group_id
AND databases.replica_id = dm_hadr_availability_replica_states.replica_id
WHERE databases.name = @CurrentDatabaseName;
END; IF @Version >= 11
AND @Cluster IS NOT NULL
AND @CurrentAvailabilityGroup IS NOT NULL
BEGIN
SELECT @CurrentIsPreferredBackupReplica = sys.fn_hadr_backup_is_preferred_replica(@CurrentDatabaseName);
END; SELECT @CurrentDatabaseMirroringRole = UPPER(mirroring_role_desc)
FROM sys.database_mirroring
WHERE database_id = @CurrentDatabaseID; IF EXISTS ( SELECT *
FROM msdb.dbo.log_shipping_primary_databases
WHERE primary_database = @CurrentDatabaseName )
BEGIN
SET @CurrentLogShippingRole = 'PRIMARY';
END;
ELSE
IF EXISTS ( SELECT *
FROM msdb.dbo.log_shipping_secondary_databases
WHERE secondary_database = @CurrentDatabaseName )
BEGIN
SET @CurrentLogShippingRole = 'SECONDARY';
END; IF @LogToTable=0 begin
-- Set database message
SET @DatabaseMessage = 'Date and time: ' + CONVERT(NVARCHAR, GETDATE(), 120) + CHAR(13) + CHAR(10);
SET @DatabaseMessage = @DatabaseMessage + 'Database: ' + QUOTENAME(@CurrentDatabaseName) + CHAR(13)
+ CHAR(10);
SET @DatabaseMessage = @DatabaseMessage + 'Status: '
+ CAST(DATABASEPROPERTYEX(@CurrentDatabaseName, 'Status') AS NVARCHAR) + CHAR(13) + CHAR(10);
SET @DatabaseMessage = @DatabaseMessage + 'Standby: '
+ CASE WHEN DATABASEPROPERTYEX(@CurrentDatabaseName, 'IsInStandBy') = 1 THEN 'Yes'
ELSE 'No'
END + CHAR(13) + CHAR(10);
SET @DatabaseMessage = @DatabaseMessage + 'Updateability: '
+ CAST(DATABASEPROPERTYEX(@CurrentDatabaseName, 'Updateability') AS NVARCHAR) + CHAR(13) + CHAR(10);
SET @DatabaseMessage = @DatabaseMessage + 'User access: '
+ CAST(DATABASEPROPERTYEX(@CurrentDatabaseName, 'UserAccess') AS NVARCHAR) + CHAR(13) + CHAR(10);
IF @CurrentIsDatabaseAccessible IS NOT NULL
SET @DatabaseMessage = @DatabaseMessage + 'Is accessible: '
+ CASE WHEN @CurrentIsDatabaseAccessible = 1 THEN 'Yes'
ELSE 'No'
END + CHAR(13) + CHAR(10);
SET @DatabaseMessage = @DatabaseMessage + 'Recovery model: '
+ CAST(DATABASEPROPERTYEX(@CurrentDatabaseName, 'Recovery') AS NVARCHAR) + CHAR(13) + CHAR(10);
IF @CurrentAvailabilityGroup IS NOT NULL
SET @DatabaseMessage = @DatabaseMessage + 'Availability group: ' + @CurrentAvailabilityGroup + CHAR(13)
+ CHAR(10);
IF @CurrentAvailabilityGroup IS NOT NULL
SET @DatabaseMessage = @DatabaseMessage + 'Availability group role: ' + @CurrentAvailabilityGroupRole
+ CHAR(13) + CHAR(10);
IF @CurrentAvailabilityGroup IS NOT NULL
SET @DatabaseMessage = @DatabaseMessage + 'Availability group backup preference: '
+ @CurrentAvailabilityGroupBackupPreference + CHAR(13) + CHAR(10);
IF @CurrentAvailabilityGroup IS NOT NULL
SET @DatabaseMessage = @DatabaseMessage + 'Is preferred backup replica: '
+ CASE WHEN @CurrentIsPreferredBackupReplica = 1 THEN 'Yes'
WHEN @CurrentIsPreferredBackupReplica = 0 THEN 'No'
ELSE 'N/A'
END + CHAR(13) + CHAR(10);
IF @CurrentDatabaseMirroringRole IS NOT NULL
SET @DatabaseMessage = @DatabaseMessage + 'Database mirroring role: ' + @CurrentDatabaseMirroringRole
+ CHAR(13) + CHAR(10);
IF @CurrentLogShippingRole IS NOT NULL
SET @DatabaseMessage = @DatabaseMessage + 'Log shipping role: ' + @CurrentLogShippingRole + CHAR(13)
+ CHAR(10);
IF @CurrentBackupType = 'DIFF'
SET @DatabaseMessage = @DatabaseMessage + 'Differential base is snapshot: '
+ CASE WHEN @CurrentDifferentialBaseIsSnapshot = 1 THEN 'Yes'
WHEN @CurrentDifferentialBaseIsSnapshot = 0 THEN 'No'
ELSE 'N/A'
END + CHAR(13) + CHAR(10);
SET @DatabaseMessage = REPLACE(@DatabaseMessage, '%', '%%') + ' ';
RAISERROR(@DatabaseMessage,10,1) WITH NOWAIT; END /*-- 需要满足以下条件
* 数据库状态为在线
* 数据库允许访问
* 数据库不是备库
* 不是简单恢复模式下的日志备份
* 不是无基准的差异备份
* 不是对master库做差异或日志备份
* 如果在可用性组中，不对辅助副本做常规备份(仅支持复制备份)，且需要备份首选项允许备份，
* 不对日志传输的主库做日志备份(会截断日志)
*
*/
IF DATABASEPROPERTYEX(@CurrentDatabaseName, 'Status') <> 'ONLINE'
OR ( @CurrentIsDatabaseAccessible IS NOT NULL
AND @CurrentIsDatabaseAccessible <> 1 )
OR @BackupType IS NULL
BEGIN
SET @DatabaseMessage = 'database ' + @CurrentDatabaseName + ' is not accessible.' + CHAR(13)
+ CHAR(10) + ' ';
RAISERROR(@DatabaseMessage,10,1) WITH NOWAIT;
END;
IF DATABASEPROPERTYEX(@CurrentDatabaseName, 'IsInStandBy') = 1
OR @BackupType IS NULL
BEGIN
SET @DatabaseMessage = 'database ' + @CurrentDatabaseName + ' is in Standby.' + CHAR(13) + CHAR(10)
+ ' ';
RAISERROR(@DatabaseMessage,10,1) WITH NOWAIT;
END;
IF @CurrentBackupType = 'LOG' AND @CurrentLogLSN IS NULL
BEGIN
SET @DatabaseMessage = 'database ' + @CurrentDatabaseName
+ ' never full backup.' + CHAR(13) + CHAR(10) + ' ';
RAISERROR(@DatabaseMessage,10,1) WITH NOWAIT;
END; IF @CurrentBackupType = 'LOG' AND ( DATABASEPROPERTYEX(@CurrentDatabaseName, 'Recovery') = 'SIMPLE')
BEGIN
SET @DatabaseMessage = 'database ' + @CurrentDatabaseName
+ ' Recovery mode is SIMPLE ,it''s can''t backup log.' + CHAR(13) + CHAR(10) + ' ';
RAISERROR(@DatabaseMessage,10,1) WITH NOWAIT;
END;
IF @CurrentBackupType = 'DIFF' AND @CurrentDifferentialBaseLSN IS NULL
BEGIN
SET @DatabaseMessage = 'database ' + @CurrentDatabaseName
+ ' backup type is DIFF but never backup database full.' + CHAR(13) + CHAR(10) + ' ';
RAISERROR(@DatabaseMessage,10,1) WITH NOWAIT;
END; IF @CurrentBackupType IN ( 'DIFF', 'LOG' ) AND @CurrentDatabaseName = 'master'
BEGIN
SET @DatabaseMessage = 'database is master,it is support full backup type only.' + CHAR(13)
+ CHAR(10) + ' ';
RAISERROR(@DatabaseMessage,10,1) WITH NOWAIT;
END; IF @CurrentAvailabilityGroup IS NOT NULL
AND @CurrentBackupType = 'FULL'
AND @copyOnly = 0
AND ( @CurrentAvailabilityGroupRole <> 'PRIMARY'
OR @CurrentAvailabilityGroupRole IS NULL )
BEGIN
SET @DatabaseMessage = 'database ' + @CurrentDatabaseName
+ ' is in Availability Group and it''s not Primary,it is support full backup type copy only.'
+ CHAR(13) + CHAR(10) + ' ';
RAISERROR(@DatabaseMessage,10,1) WITH NOWAIT;
END; IF @CurrentAvailabilityGroup IS NOT NULL
AND @CurrentBackupType = 'FULL'
AND @copyOnly = 1
AND ( @CurrentIsPreferredBackupReplica <> 1
OR @CurrentIsPreferredBackupReplica IS NULL )
AND @OverrideBackupPreference = 0
BEGIN
SET @DatabaseMessage = 'database ' + @CurrentDatabaseName
+ ' is in Availability Group ,but it''s not preferred backup replica and @OverrideBackupPreference is not.'
+ CHAR(13) + CHAR(10) + ' ';
RAISERROR(@DatabaseMessage,10,1) WITH NOWAIT;
END; IF @CurrentAvailabilityGroup IS NOT NULL
AND @CurrentBackupType = 'DIFF'
AND ( @CurrentAvailabilityGroupRole <> 'PRIMARY'
OR @CurrentAvailabilityGroupRole IS NULL )
BEGIN
SET @DatabaseMessage = 'database ' + @CurrentDatabaseName
+ ' is in Availability Group and it''s not Primary,it''s not support diff backup type.'
+ CHAR(13) + CHAR(10) + ' ';
RAISERROR(@DatabaseMessage,10,1) WITH NOWAIT;
END; IF @CurrentAvailabilityGroup IS NOT NULL
AND @CurrentBackupType = 'LOG'
AND @copyOnly = 0
AND ( @CurrentIsPreferredBackupReplica <> 1
OR @CurrentIsPreferredBackupReplica IS NULL )
AND @OverrideBackupPreference = 0
BEGIN
SET @DatabaseMessage = 'database ' + @CurrentDatabaseName
+ ' is in Availability Group ,but it''s not preferred backup replica and @OverrideBackupPreference is not.'
+ CHAR(13) + CHAR(10) + ' ';
RAISERROR(@DatabaseMessage,10,1) WITH NOWAIT;
END; IF ( @CurrentLogShippingRole = 'PRIMARY'
AND @CurrentLogShippingRole IS NOT NULL )
AND @CurrentBackupType = 'LOG'
BEGIN
SET @DatabaseMessage = 'database ' + @CurrentDatabaseName
+ ' is in log shipping role ,and it''s Primary,it''s not support backup log.' + CHAR(13)
+ CHAR(10) + ' ';
RAISERROR(@DatabaseMessage,10,1) WITH NOWAIT;
END; IF @CurrentAvailabilityGroup IS NOT NULL
AND @CurrentBackupType = 'LOG'
AND @copyOnly = 1
AND ( @CurrentAvailabilityGroupRole <> 'PRIMARY'
OR @CurrentAvailabilityGroupRole IS NULL )
BEGIN
SET @DatabaseMessage = 'database ' + @CurrentDatabaseName
+ ' is in Availability Group ,and it''s not Primary,it is support backup copy only.' + CHAR(13)
+ CHAR(10) + ' ';
RAISERROR(@DatabaseMessage,10,1) WITH NOWAIT;
END; IF DATABASEPROPERTYEX(@CurrentDatabaseName, 'Status') = 'ONLINE'
AND ( @CurrentIsDatabaseAccessible = 1
OR @CurrentIsDatabaseAccessible IS NULL )
AND DATABASEPROPERTYEX(@CurrentDatabaseName, 'IsInStandBy') = 0
AND NOT ( @CurrentBackupType = 'LOG'
AND ( DATABASEPROPERTYEX(@CurrentDatabaseName, 'Recovery') = 'SIMPLE'
OR @CurrentLogLSN IS NULL ) )
AND NOT ( @CurrentBackupType = 'DIFF'
AND @CurrentDifferentialBaseLSN IS NULL )
AND NOT ( @CurrentBackupType IN ( 'DIFF', 'LOG' )
AND @CurrentDatabaseName = 'master' )
AND NOT ( @CurrentAvailabilityGroup IS NOT NULL
AND @CurrentBackupType = 'FULL'
AND @copyOnly = 0
AND ( @CurrentAvailabilityGroupRole <> 'PRIMARY'
OR @CurrentAvailabilityGroupRole IS NULL ) )
AND NOT ( @CurrentAvailabilityGroup IS NOT NULL
AND @CurrentBackupType = 'FULL'
AND @copyOnly = 1
AND ( @CurrentIsPreferredBackupReplica <> 1
OR @CurrentIsPreferredBackupReplica IS NULL )
AND @OverrideBackupPreference = 0 )
AND NOT ( @CurrentAvailabilityGroup IS NOT NULL
AND @CurrentBackupType = 'DIFF'
AND ( @CurrentAvailabilityGroupRole <> 'PRIMARY'
OR @CurrentAvailabilityGroupRole IS NULL ) )
AND NOT ( @CurrentAvailabilityGroup IS NOT NULL
AND @CurrentBackupType = 'LOG'
AND @copyOnly = 0
AND ( @CurrentIsPreferredBackupReplica <> 1
OR @CurrentIsPreferredBackupReplica IS NULL )
AND @OverrideBackupPreference = 0 )
AND NOT ( @CurrentAvailabilityGroup IS NOT NULL
AND @CurrentBackupType = 'LOG'
AND @copyOnly = 1
AND ( @CurrentAvailabilityGroupRole <> 'PRIMARY'
OR @CurrentAvailabilityGroupRole IS NULL ) )
AND NOT ( ( @CurrentLogShippingRole = 'PRIMARY'
AND @CurrentLogShippingRole IS NOT NULL )
AND @CurrentBackupType = 'LOG' )
BEGIN
SET @CurrentDate = GETDATE();
SELECT @CurrentFileExtension = CASE WHEN @CurrentBackupType = 'FULL' THEN 'bak'
WHEN @CurrentBackupType = 'DIFF' THEN 'bak'
WHEN @CurrentBackupType = 'LOG' THEN 'trn'
END; INSERT INTO @CurrentDirectories ( ID, DirectoryPath, CreateCompleted, CleanupCompleted )
SELECT ROW_NUMBER() OVER ( ORDER BY ID ),
DirectoryPath + '\' + ( @CurrentBackupType )
+ CASE WHEN RIGHT(DirectoryPath, 1) = '\' THEN ''
ELSE '\'
END + @CurrentDatabaseNameFS + CASE WHEN @BackupType = 'LOG'
THEN '\' + REPLACE(CONVERT(VARCHAR(7), GETDATE(), 120),
'-', '')
ELSE ''
END, 0, 0
FROM @Directories
ORDER BY ID ASC; IF EXISTS ( SELECT *
FROM @CurrentDirectories )
BEGIN
SET @CurrentFileNumber = 0; WHILE @CurrentFileNumber < @NumberOfFiles
BEGIN
SET @CurrentFileNumber = @CurrentFileNumber + 1; SELECT @CurrentDirectoryPath = DirectoryPath
FROM @CurrentDirectories
WHERE @CurrentFileNumber >= 1
AND @CurrentFileNumber <= ( SELECT @NumberOfFiles / COUNT (*)
FROM @CurrentDirectories
); SET @fileName = CASE WHEN @CurrentAvailabilityGroup IS NOT NULL THEN UPPER(@Cluster)
--+ '$' + @CurrentAvailabilityGroup
ELSE REPLACE(CAST(SERVERPROPERTY('servername') AS nvarchar),'\','$') END + '_' + @CurrentDatabaseNameFS + '_'
+ UPPER(@CurrentBackupType) + CASE WHEN @copyOnly = 1 THEN '_COPY_ONLY'
ELSE ''
END + '_'
+ REPLACE(REPLACE(REPLACE(( CONVERT(NVARCHAR, @CurrentDate, 120) ), '-', ''),
' ', '_'), ':', '')
+ CASE WHEN @NumberOfFiles > 1
AND @NumberOfFiles <= 9
THEN '_' + CAST(@CurrentFileNumber AS NVARCHAR)
WHEN @NumberOfFiles >= 10
THEN '_' + RIGHT('' + CAST(@CurrentFileNumber AS NVARCHAR), 2)
ELSE ''
END + '.' + @CurrentFileExtension; SET @CurrentFilePath = @CurrentDirectoryPath + '\' + @fileName;
INSERT INTO @CurrentFiles ( [Type], FilePath )
SELECT 'DISK', @CurrentFilePath; SET @CurrentDirectoryPath = NULL;
SET @CurrentFilePath = NULL;
END; INSERT INTO @CurrentBackupSet ( VerifyCompleted )
SELECT 0;
END; -- Create directory
WHILE EXISTS ( SELECT *
FROM @CurrentDirectories
WHERE CreateCompleted = 0 )
BEGIN
SELECT TOP 1
@CurrentDirectoryID = ID, @CurrentDirectoryPath = DirectoryPath
FROM @CurrentDirectories
WHERE CreateCompleted = 0
ORDER BY ID ASC; SET @CurrentCommandType01 = 'xp_create_subdir';
SET @CurrentCommand01 = 'DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_create_subdir N'''
+ REPLACE(@CurrentDirectoryPath, '''', '''''')
+ ''' IF @ReturnCode <> 0 RAISERROR(''Error creating directory.'', 16, 1)'; EXECUTE @CurrentCommandOutput01 = [dbo].[CommandExecute] @Command = @CurrentCommand01,
@CommandType = @CurrentCommandType01, @Mode = 1, @DatabaseName = @CurrentDatabaseName,
@LogToTable = @LogToTable, @Exec = @exec; SET @Error = @@ERROR;
IF @Error <> 0
SET @CurrentCommandOutput01 = @Error;
IF @CurrentCommandOutput01 <> 0
SET @ReturnCode = @CurrentCommandOutput01; UPDATE @CurrentDirectories
SET CreateCompleted = 1, CreateOutput = @CurrentCommandOutput01
WHERE ID = @CurrentDirectoryID; SET @CurrentDirectoryID = NULL;
SET @CurrentDirectoryPath = NULL; SET @CurrentCommand01 = NULL; SET @CurrentCommandOutput01 = NULL; SET @CurrentCommandType01 = NULL;
END; -- Perform a backup
IF NOT EXISTS ( SELECT *
FROM @CurrentDirectories
WHERE CreateOutput <> 0
OR CreateOutput IS NULL )
BEGIN
SELECT @CurrentCommandType03 = CASE WHEN @CurrentBackupType IN ( 'DIFF', 'FULL' )
THEN 'BACKUP_DATABASE'
WHEN @CurrentBackupType = 'LOG' THEN 'BACKUP_LOG'
END; SELECT @CurrentCommand03 = CASE WHEN @CurrentBackupType IN ( 'DIFF', 'FULL' )
THEN 'BACKUP DATABASE ' + QUOTENAME(@CurrentDatabaseName)
WHEN @CurrentBackupType = 'LOG'
THEN 'BACKUP LOG ' + QUOTENAME(@CurrentDatabaseName)
END; IF @ReadWriteFileGroups = 1
AND @CurrentDatabaseName <> 'master'
SET @CurrentCommand03 = @CurrentCommand03 + ' READ_WRITE_FILEGROUPS'; SET @CurrentCommand03 = @CurrentCommand03 + ' TO'; SELECT @CurrentCommand03 = @CurrentCommand03 + ' ' + [Type] + ' = N''' + REPLACE(FilePath,
'''', '''''')
+ ''''
+ CASE WHEN ROW_NUMBER() OVER ( ORDER BY FilePath ASC ) <> @NumberOfFiles THEN ','
ELSE ''
END
FROM @CurrentFiles
ORDER BY FilePath ASC; SET @CurrentCommand03 = @CurrentCommand03 + ' WITH ';
IF @CheckSum = 1
SET @CurrentCommand03 = @CurrentCommand03 + 'CHECKSUM';
IF @CheckSum = 0
SET @CurrentCommand03 = @CurrentCommand03 + 'NO_CHECKSUM';
IF @Compress = 1
SET @CurrentCommand03 = @CurrentCommand03 + ', COMPRESSION';
IF @Compress = 0
AND @Version >= 10
SET @CurrentCommand03 = @CurrentCommand03 + ', NO_COMPRESSION';
IF @CurrentBackupType = 'DIFF'
SET @CurrentCommand03 = @CurrentCommand03 + ', DIFFERENTIAL'; IF @copyOnly = 1
SET @CurrentCommand03 = @CurrentCommand03 + ', COPY_ONLY';
IF @NoRecovery = 1
AND @CurrentBackupType = 'LOG'
SET @CurrentCommand03 = @CurrentCommand03 + ', NORECOVERY';
IF @Description IS NOT NULL
SET @CurrentCommand03 = @CurrentCommand03 + ', DESCRIPTION = N''' + REPLACE(@Description,
'''', '''''')
+ '''';
IF @CurrentCommand03 IS NULL
SELECT @CurrentCommand03;
EXECUTE @CurrentCommandOutput03 = [dbo].[CommandExecute] @Command = @CurrentCommand03,
@CommandType = @CurrentCommandType03, @Mode = 1, @DatabaseName = @CurrentDatabaseName,
@LogToTable = @LogToTable, @Exec = @Exec;
SET @Error = @@ERROR;
IF @Error <> 0
SET @CurrentCommandOutput03 = @Error;
IF @CurrentCommandOutput03 <> 0
SET @ReturnCode = @CurrentCommandOutput03;
END; -- Verify the backup
IF @CurrentCommandOutput03 = 0
AND @Verify = 1
BEGIN
WHILE EXISTS ( SELECT *
FROM @CurrentBackupSet
WHERE VerifyCompleted = 0 )
BEGIN
SELECT TOP 1
@CurrentBackupSetID = ID
FROM @CurrentBackupSet
WHERE VerifyCompleted = 0
ORDER BY ID ASC; SET @CurrentCommandType04 = 'RESTORE_VERIFYONLY'; SET @CurrentCommand04 = 'RESTORE VERIFYONLY FROM'; SELECT @CurrentCommand04 = @CurrentCommand04 + ' ' + [Type] + ' = N'''
+ REPLACE(FilePath, '''', '''''') + ''''
+ CASE WHEN ROW_NUMBER() OVER ( ORDER BY FilePath ASC ) <> @NumberOfFiles
THEN ','
ELSE ''
END
FROM @CurrentFiles
ORDER BY FilePath ASC; SET @CurrentCommand04 = @CurrentCommand04 + ' WITH ';
IF @CheckSum = 1
SET @CurrentCommand04 = @CurrentCommand04 + 'CHECKSUM';
IF @CheckSum = 0
SET @CurrentCommand04 = @CurrentCommand04 + 'NO_CHECKSUM';
EXECUTE @CurrentCommandOutput04 = [dbo].[CommandExecute] @Command = @CurrentCommand04,
@CommandType = @CurrentCommandType04, @Mode = 1,
@DatabaseName = @CurrentDatabaseName, @LogToTable = @LogToTable, @Exec = @Exec;
SET @Error = @@ERROR;
IF @Error <> 0
SET @CurrentCommandOutput04 = @Error;
IF @CurrentCommandOutput04 <> 0
SET @ReturnCode = @CurrentCommandOutput04; UPDATE @CurrentBackupSet
SET VerifyCompleted = 1, VerifyOutput = @CurrentCommandOutput04
WHERE ID = @CurrentBackupSetID; SET @CurrentBackupSetID = NULL;
SET @CurrentIsMirror = NULL; SET @CurrentCommand04 = NULL;
SET @CurrentCommandOutput04 = NULL;
SET @CurrentCommandType04 = NULL;
END;
END;
END; -- Update that the database is completed
UPDATE @tmpDatabases
SET Completed = 1
WHERE Selected = 1
AND Completed = 0
AND ID = @CurrentDBID; -- Clear variables
SET @CurrentDBID = NULL;
SET @CurrentDatabaseID = NULL;
SET @CurrentDatabaseName = NULL;
SET @CurrentBackupType = NULL;
SET @CurrentFileExtension = NULL;
SET @CurrentFileNumber = NULL;
SET @CurrentDifferentialBaseLSN = NULL;
SET @CurrentDifferentialBaseIsSnapshot = NULL;
SET @CurrentLogLSN = NULL;
SET @CurrentLatestBackup = NULL;
SET @CurrentDatabaseNameFS = NULL;
SET @CurrentDate = NULL;
SET @CurrentCleanupDate = NULL;
SET @CurrentIsDatabaseAccessible = NULL;
SET @CurrentAvailabilityGroup = NULL;
SET @CurrentAvailabilityGroupRole = NULL;
SET @CurrentAvailabilityGroupBackupPreference = NULL;
SET @CurrentIsPreferredBackupReplica = NULL;
SET @CurrentDatabaseMirroringRole = NULL;
SET @CurrentLogShippingRole = NULL; SET @CurrentCommand03 = NULL;
SET @CurrentCommandOutput03 = NULL;
SET @CurrentCommandType03 = NULL; DELETE FROM @CurrentDirectories;
DELETE FROM @CurrentFiles;
DELETE FROM @CurrentBackupSet;
END; ----------------------------------------------------------------------------------------------------
--// Log completing information //--
---------------------------------------------------------------------------------------------------- Logging:
IF @LogToTable = 0
BEGIN
SET @EndMessage = 'Date and time: ' + CONVERT(NVARCHAR, GETDATE(), 120);
SET @EndMessage = REPLACE(@EndMessage, '%', '%%');
RAISERROR(@EndMessage,10,1) WITH NOWAIT;
END
IF @ReturnCode <> 0
BEGIN
RETURN @ReturnCode;
END;
END; GO
/****** Object: StoredProcedure [dbo].[sp_MGR_trace_SetStatus] Script Date: 2017/2/15 11:16:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO -- =============================================
-- Author: hcc
-- Create date: 2015-9-19
-- Description: 自动生成跟踪. 只跟踪超过1500ms的sql和过程,
-- 会自动将上一个跟踪导入到TraceLog表,并启用新的跟踪.使用Job定时调用该过程 -- Parameters
-- @rootPath: 跟踪文件存放目录
-- @status: 是否启用跟踪.1为启用,0为关闭
-- @path 手动写入某跟踪文件到表.要求为全路径
-- =============================================
CREATE PROC [dbo].[sp_MGR_trace_SetStatus]
@rootPath NVARCHAR(200) = 'D:\InOut\TraceLog\' ,--
@status BIT = 1 ,
@path NVARCHAR(200) = NULL
AS
BEGIN
DECLARE @rc INT;
DECLARE @TraceID INT ,
@curid INT;
DECLARE @maxfilesize BIGINT;
DECLARE @DateTime DATETIME; -- 先关闭已打开的自定义跟踪
IF @path IS NULL
OR LEN(@path) = 0
SELECT @curid = id,
@path = CASE WHEN CHARINDEX('_', [path]) > 0
THEN LEFT([path], CHARINDEX('_', [path]) - 1)
+ '.trc'
ELSE [path]
END
FROM sys.traces
WHERE id > 1
AND path IS NOT NULL
AND path LIKE @rootPath + '%'
AND status = 1; IF @path IS NULL
OR LEN(@path) = 0
GOTO CreateNew; DECLARE @table TABLE ( filePath VARCHAR(500) ); EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE; DECLARE @dir VARCHAR(500); SET @dir = 'dir /b/s /o:d ' + REPLACE(@path, '.trc', '*.trc'); INSERT INTO @table ( filePath )
EXEC master..xp_cmdshell @dir; EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE; DELETE FROM @table
WHERE filePath IS NULL
OR filePath = '系统找不到指定的文件。'
OR filePath = '找不到文件'
OR filePath = '系统找不到指定的路径。'; IF (
SELECT COUNT(1)
FROM @table
) > 1
BEGIN
DECLARE multiTraceFile CURSOR LOCAL FORWARD_ONLY FAST_FORWARD READ_ONLY
FOR
SELECT filePath
FROM @table; OPEN multiTraceFile; FETCH NEXT FROM multiTraceFile INTO @path;
WHILE ( @@fetch_status = 0 )
BEGIN
INSERT INTO TraceDB.dbo.tracelog ( [EventClass], [Duration],
[TextData], [SPID],
[BinaryData], [CPU],
[EndTime], [ObjectName],
[StartTime],Reads,Writes ,databaseName,ApplicationName,HostName )
SELECT [EventClass], [Duration], [TextData],
[SPID], [BinaryData], [CPU], [EndTime],
[ObjectName], [StartTime],Reads,Writes,databaseName,ApplicationName,HostName
FROM ::
fn_trace_gettable(@path, DEFAULT) a
WHERE a.EventClass IN ( 10, 12 ); -- PRINT @path;
FETCH NEXT FROM multiTraceFile INTO @path;
END; CLOSE multiTraceFile;
DEALLOCATE multiTraceFile; END;
ELSE
INSERT INTO TraceDB.dbo.tracelog ( [EventClass], [Duration],
[TextData], [SPID],
[BinaryData], [CPU],
[EndTime], [ObjectName],
[StartTime],Reads,Writes,databaseName,ApplicationName,HostName )
SELECT [EventClass], [Duration], [TextData],
[SPID], [BinaryData], [CPU], [EndTime],
[ObjectName], [StartTime],Reads,Writes,databaseName,ApplicationName,HostName
FROM ::
fn_trace_gettable(@path, DEFAULT) a
WHERE a.EventClass IN ( 10, 12 ); IF ( @curid IS NOT NULL )
BEGIN
EXEC sp_trace_setstatus @curid, 0;
EXEC sp_trace_setstatus @curid, 2;
END; CreateNew: IF @status = 0
GOTO finish; SET @maxfilesize = 20;
SET @path = @rootPath + CONVERT(VARCHAR(6), GETDATE(), 112) + '\'; EXECUTE master.dbo.xp_create_subdir @path; SET @path = @rootPath + CONVERT(VARCHAR(6), GETDATE(), 112) + '\'
+ REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), '-',
''), ' ', ''), ':', ''); EXEC @rc = sp_trace_create @TraceID OUTPUT, 2, @path, @maxfilesize,
NULL;
IF ( @rc != 0 )
GOTO error; -- Set the events
DECLARE @on BIT;
SET @on = 1;
EXEC sp_trace_setevent @TraceID, 10, 2, @on;
EXEC sp_trace_setevent @TraceID, 10, 8, @on;
EXEC sp_trace_setevent @TraceID, 10, 10, @on;
EXEC sp_trace_setevent @TraceID, 10, 12, @on;
EXEC sp_trace_setevent @TraceID, 10, 13, @on;
EXEC sp_trace_setevent @TraceID, 10, 14, @on;
EXEC sp_trace_setevent @TraceID, 10, 15, @on;
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
EXEC sp_trace_setevent @TraceID, 10, 18, @on;
EXEC sp_trace_setevent @TraceID, 10, 34, @on;
exec sp_trace_setevent @TraceID, 10, 35, @on; EXEC sp_trace_setevent @TraceID, 12, 1, @on;
EXEC sp_trace_setevent @TraceID, 12, 8, @on;
EXEC sp_trace_setevent @TraceID, 12, 10, @on;
EXEC sp_trace_setevent @TraceID, 12, 12, @on;
EXEC sp_trace_setevent @TraceID, 12, 13, @on;
EXEC sp_trace_setevent @TraceID, 12, 14, @on;
EXEC sp_trace_setevent @TraceID, 12, 15, @on;
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
EXEC sp_trace_setevent @TraceID, 12, 18, @on;
exec sp_trace_setevent @TraceID, 12, 35, @on; -- Set the Filters
DECLARE @intfilter INT;
DECLARE @bigintfilter BIGINT; -- 1500毫秒即1500000微秒
SET @bigintfilter = 1500000;
EXEC sp_trace_setfilter @TraceID, 13, 0, 4, @bigintfilter; -- 排除空值
SET @bigintfilter = NULL;
EXEC sp_trace_setfilter @TraceID, 13, 0, 1, @bigintfilter; -- Set the trace status to start
EXEC sp_trace_setstatus @TraceID, 1; -- display trace id for future references
-- SELECT TraceID = @TraceID;
SELECT *
FROM sys.traces
WHERE id = @TraceID; GOTO finish; error:
SELECT ErrorCode = @rc; END; finish:; GO
USE [master]
GO
ALTER DATABASE [TraceDB] SET READ_WRITE
GO
作业
USE [msdb]
GO /****** Object: Job [Admin_Add_Trace] Script Date: 2017/2/15 11:34:28 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 2017/2/15 11:34:28 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback END DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Admin_Add_Trace',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'无描述。',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [job1] Script Date: 2017/2/15 11:34:28 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'job1',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'exec [sp_MGR_trace_SetStatus]',
@database_name=N'TraceDB',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'job2',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20170215,
@active_end_date=99991231,
@active_start_time=500,
@active_end_time=235959,
@schedule_uid=N'2f9ebad5-d3f4-42d6-98e8-19b3433d6eb3'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave: GO