--============================================================================--新建两个目录 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--GOIF (1 = FULLTEXTSERVICEPROPERTY(‘IsFullTextInstalled‘))beginEXEC [TraceDB].[dbo].[sp_fulltext_database] @action = ‘enable‘endGOALTER DATABASE [TraceDB] SET ANSI_NULL_DEFAULT OFF GOALTER DATABASE [TraceDB] SET ANSI_NULLS OFF GOALTER DATABASE [TraceDB] SET ANSI_PADDING OFF GOALTER DATABASE [TraceDB] SET ANSI_WARNINGS OFF GOALTER DATABASE [TraceDB] SET ARITHABORT OFF GOALTER DATABASE [TraceDB] SET AUTO_CLOSE OFF GOALTER DATABASE [TraceDB] SET AUTO_SHRINK OFF GOALTER DATABASE [TraceDB] SET AUTO_UPDATE_STATISTICS ON GOALTER DATABASE [TraceDB] SET CURSOR_CLOSE_ON_COMMIT OFF GOALTER DATABASE [TraceDB] SET CURSOR_DEFAULT  GLOBAL GOALTER DATABASE [TraceDB] SET CONCAT_NULL_YIELDS_NULL OFF GOALTER DATABASE [TraceDB] SET NUMERIC_ROUNDABORT OFF GOALTER DATABASE [TraceDB] SET QUOTED_IDENTIFIER OFF GOALTER DATABASE [TraceDB] SET RECURSIVE_TRIGGERS OFF GOALTER DATABASE [TraceDB] SET  ENABLE_BROKER GOALTER DATABASE [TraceDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF GOALTER DATABASE [TraceDB] SET DATE_CORRELATION_OPTIMIZATION OFF GOALTER DATABASE [TraceDB] SET TRUSTWORTHY OFF GOALTER DATABASE [TraceDB] SET ALLOW_SNAPSHOT_ISOLATION OFF GOALTER DATABASE [TraceDB] SET PARAMETERIZATION SIMPLE GOALTER DATABASE [TraceDB] SET READ_COMMITTED_SNAPSHOT OFF GOALTER DATABASE [TraceDB] SET HONOR_BROKER_PRIORITY OFF GOALTER DATABASE [TraceDB] SET RECOVERY FULL GOALTER DATABASE [TraceDB] SET  MULTI_USER GOALTER DATABASE [TraceDB] SET PAGE_VERIFY CHECKSUM  GOALTER DATABASE [TraceDB] SET DB_CHAINING OFF GOALTER DATABASE [TraceDB] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) GOALTER DATABASE [TraceDB] SET TARGET_RECOVERY_TIME = 0 SECONDS GOALTER DATABASE [TraceDB] SET DELAYED_DURABILITY = DISABLED GOEXEC sys.sp_db_vardecimal_storage_format N‘TraceDB‘, N‘ON‘GOUSE [TraceDB]GO/****** Object:  UserDefinedFunction [dbo].[Split]    Script Date: 2017/2/15 11:16:02 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO--表值函数用以截取字符串--如果为其添加一列主键id,则其顺序就会固定了create FUNCTION [dbo].[Split](@text NVARCHAR(max))RETURNS @tempTable TABLE(value NVARCHAR(1000))ASBEGIN     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     RETURNENDGO/****** Object:  Table [dbo].[CommandLog]    Script Date: 2017/2/15 11:16:02 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE 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]GOSET ANSI_PADDING OFFGO/****** Object:  Table [dbo].[TraceLog]    Script Date: 2017/2/15 11:16:02 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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 bitASBEGIN  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  ----------------------------------------------------------------------------------------------------ENDGO/****** Object:  StoredProcedure [dbo].[DataBaseBackup]    Script Date: 2017/2/15 11:16:02 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO/****** 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 = 0ASBEGIN    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(‘0‘ + 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 ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- 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) = NULLAS    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:; GOUSE [master]GOALTER DATABASE [TraceDB] SET  READ_WRITE GO

USE [msdb]GO/****** Object:  Job [Admin_Add_Trace]    Script Date: 2017/2/15 11:34:28 ******/BEGIN TRANSACTIONDECLARE @ReturnCode INTSELECT @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)BEGINEXEC @ReturnCode = msdb.dbo.sp_add_category @class=N‘JOB‘, @type=N‘LOCAL‘, @name=N‘[Uncategorized (Local)]‘IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackENDDECLARE @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 OUTPUTIF (@@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=0IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @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 QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N‘(local)‘IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackCOMMIT TRANSACTIONGOTO EndSaveQuitWithRollback:    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTIONEndSave:GO