SQL Server 批量备份数据库（主分区）

--批量创建文件夹
EXEC sp_configure ‘show advanced options‘, 1
RECONFIGURE
EXEC sp_configure ‘xp_cmdshell‘, 1
RECONFIGURE

DECLARE @DBName VARCHAR(100)
DECLARE @SQL VARCHAR(1000)

DECLARE CurDBName CURSOR FOR
    SELECT name FROM sys.databases WHERE name LIKE ‘%Opinion%‘ AND STATE =0

OPEN CurDBName
    FETCH NEXT FROM CurDBName INTO @DBName
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @SQL = ‘mkdir E:\DBBackup\‘ + @DBName
        EXEC xp_cmdshell @SQL
        
        FETCH NEXT FROM CurDBName INTO @DBName
    END
CLOSE CurDBName
DEALLOCATE CurDBName

EXEC sp_configure ‘show advanced options‘, 0
RECONFIGURE
EXEC sp_configure ‘xp_cmdshell‘, 0
RECONFIGURE

--作业记录表
USE [msdb]
GO
CREATE TABLE [dbo].[JobLog](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [DB_Name] [varchar](50) NULL,
    [Backup_Date] [int] NULL,
    [Backup_Time] [int] NULL,
    [Backup_Duration] [int] NULL,
    [Backup_Type] [char](4) NULL,
 CONSTRAINT [PK_JobLog] 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]

--错误记录表
USE [msdb]
GO
CREATE TABLE [dbo].[ErrorLog](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [DB_Name] [varchar](50) NOT NULL,
    [Backup_Time] [datetime] NOT NULL CONSTRAINT [DF_ErrorLog_Backup_Time]  DEFAULT (getdate()),
    [Messages] [nvarchar](500) NULL,
 CONSTRAINT [PK_ErrorLog] 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]

--批量备份数据库
DECLARE @DBName VARCHAR(100)
DECLARE @CurrentTime VARCHAR(50)
DECLARE @FileName VARCHAR(200)
DECLARE @WithType CHAR(20)
DECLARE @Backup_Date VARCHAR(50)
DECLARE @Backup_Time VARCHAR(50)
DECLARE @Backup_Duration VARCHAR(50)
DECLARE @Backup_Start DATETIME
DECLARE @Backup_End DATETIME
DECLARE @BackupType CHAR(4)
DECLARE @SQL VARCHAR(MAX)

--防止作业遗漏备份
INSERT INTO [msdb].[dbo].[JobLog]([DB_Name],[Backup_Date],[Backup_Time],[Backup_Duration],[Backup_Type])
SELECT name,0,0,0,NULL FROM sys.databases WHERE name LIKE ‘%Opinion%‘ AND STATE =0
AND name NOT IN (SELECT DISTINCT [DB_Name] FROM [msdb].[dbo].[JobLog])
ORDER BY name

DECLARE CurDBName CURSOR FOR
    SELECT name FROM sys.databases WHERE name LIKE ‘%Opinion%‘ AND STATE =0 ORDER BY name

OPEN CurDBName
    FETCH NEXT FROM CurDBName INTO @DBName

    WHILE @@FETCH_STATUS = 0
    BEGIN
        --Execute Backup
        --捕获异常
        BEGIN TRY
            PRINT @DBName
            SET @CurrentTime = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120 ),‘-‘,‘_‘),‘ ‘,‘_‘),‘:‘,‘‘)
            IF(DATEPART(DW, GETDATE()) = 2)--星期一
            BEGIN
                SET @FileName = ‘E:\DBBackup\‘+@DBName+‘\‘+@DBName+‘_Primary_Full_‘ + @CurrentTime+‘.bak‘
                SET @WithType = ‘ FORMAT‘
                SET @BackupType = ‘Full‘
            END
            ELSE
            BEGIN
                SET @FileName = ‘E:\DBBackup\‘+@DBName+‘\‘+@DBName+‘_Primary_Diff_‘ + @CurrentTime+‘.bak‘
                SET @WithType = ‘ DIFFERENTIAL,FORMAT‘
                SET @BackupType = ‘Diff‘
            END

            SET @Backup_Start = GETDATE()
            SET @SQL = ‘
            --1设置完整模式
            ALTER DATABASE [‘+@DBName+‘] SET RECOVERY FULL WITH NO_WAIT;
            --2备份主分区
            BACKUP DATABASE [‘+@DBName+‘]
            FILEGROUP=‘‘PRIMARY‘‘ TO DISK=‘‘‘+@FileName+‘‘‘ WITH ‘+@WithType+‘;
            --3设置简单模式
            ALTER DATABASE [‘+@DBName+‘] SET RECOVERY SIMPLE WITH NO_WAIT;
            ‘
            EXEC(@SQL)

            SET @Backup_End = GETDATE()
            SET @Backup_Date = CONVERT(VARCHAR, GETDATE(),112)
            SET @Backup_Time = REPLACE(CONVERT(VARCHAR, GETDATE(),24),‘:‘,‘‘)
            SET @Backup_Duration = CONVERT(VARCHAR,DATEDIFF(ss,@Backup_Start,@Backup_End))
            PRINT @Backup_Date +@Backup_Time +@Backup_Duration
            SET @SQL = ‘
            INSERT INTO [msdb].[dbo].[JobLog]([DB_Name],[Backup_Date],[Backup_Time],[Backup_Duration],[Backup_Type])
                VALUES(‘‘‘+@DBName+‘‘‘,‘+@Backup_Date+‘,‘+@Backup_Time+‘,‘+@Backup_Duration+‘,‘‘‘+@BackupType+‘‘‘);
            ‘
            EXEC(@SQL)
        END TRY
        BEGIN CATCH
            INSERT INTO [dbo].[ErrorLog]([DB_Name],[ErrorMessage])
            VALUES(@DBName,ERROR_MESSAGE())
            --ROLLBACK TRANSACTION
        END CATCH

        --Get Next DataBase
        FETCH NEXT FROM CurDBName INTO @DBName
    END
CLOSE CurDBName
DEALLOCATE CurDBName

--删除14天之前的备份文件
DECLARE @DeleteDate DATETIME
SET @DeleteDate = DATEADD(DAY, -14, GETDATE())

EXECUTE MASTER.SYS.XP_DELETE_FILE
0,
N‘E:\DBBackup\‘,
N‘bak‘,
@DeleteDate

--行转列（备份类型）
DECLARE @s NVARCHAR(MAX)
SET @s=‘‘
SELECT @s=@s+‘,‘+quotename([Backup_Date])+‘=MAX(CASE WHEN [Backup_Date]=‘+quotename([Backup_Date],‘‘‘‘)+‘ THEN [Backup_Type] ELSE NULL END)‘
FROM [msdb].[dbo].[JobLog] GROUP BY [Backup_Date] ORDER BY [Backup_Date]
PRINT @s
EXEC(‘SELECT [DB_Name] ‘+@s+‘ FROM [msdb].[dbo].[JobLog]
GROUP BY [DB_Name] ORDER BY [DB_Name]‘)

--行转列（执行时间）
DECLARE @s NVARCHAR(MAX)
SET @s=‘‘
SELECT @s=@s+‘,‘+quotename([Backup_Date])+‘=MAX(CASE WHEN [Backup_Date]=‘+quotename([Backup_Date],‘‘‘‘)+‘ THEN [Backup_Duration] ELSE NULL END)‘
FROM [msdb].[dbo].[JobLog] GROUP BY [Backup_Date] ORDER BY [Backup_Date]
PRINT @s
EXEC(‘SELECT [DB_Name] ‘+@s+‘ FROM [msdb].[dbo].[JobLog]
GROUP BY [DB_Name] ORDER BY [DB_Name]‘)