主页 / dba / 问题 / 135134
Accepted
SQLPRODDBA
Asked: 2016-04-14 01:37:43 +0800 CST

需要有关此备份审核 SQL Server 脚本的帮助

  • 772

我正在使用以下从http://www.ludovicocaldara.net/dba/sql-server-last-database-backup/链接获取的备份脚本来审核我的服务器以进行备份。这很好用，除非我有溢出的备份。

对于 splitbackups，它会根据编号提供很多行。拆分备份文件。例如，如果我有 4 个拆分的完整备份文件和 4 个拆分的差异备份文件，它会提供 16 行输出。这是因为LEFT JOIN用过。我需要帮助来修复脚本以删除所有重复项并提供正确的输出。例如，它应该只在输出中提供 4 个拆分的完整备份文件和 4 个差异备份文件。

WITH db
     AS (SELECT [Instance] = @@SERVERNAME,
                [Database] = name,
                [RecoveryMode] = DATABASEPROPERTYEX(name, 'Recovery'),
                [CreationTime] = crdate,
                [Status] = DATABASEPROPERTYEX(name, 'Status')
         FROM master..sysdatabases
         WHERE name != 'tempdb'),
lastfull
     AS (SELECT *
         FROM
         (
             SELECT [Database] = s.database_name,
                    --[Type]   = s.type,
                    [LastFullDate] = CONVERT( VARCHAR, s.backup_finish_date, 120),
                    --[LastFullSize]   = s.backup_size,
                    [LastFullDevice] = f.physical_device_name,
                    --[LastFullDevTyp] = f.device_type,
                    [Nrank] = RANK() OVER(PARTITION BY s.database_name ORDER BY s.backup_finish_date DESC)
             FROM msdb.dbo.backupset s,
                  msdb.dbo.backupmediafamily f
             WHERE s.media_set_id = f.media_set_id
                   AND s.type = 'D'
         -- and f.device_type = 7 -- only backup devices
         ) f
         WHERE nrank = 1),
lastdiff
     AS (SELECT *
         FROM
         (
             SELECT [Database] = s.database_name,
                    --[Type]   = s.type,
                    [LastDiffDate] = CONVERT( VARCHAR, s.backup_finish_date, 120),
                    --[LastDiffSize]   = s.backup_size,
                    [LastDiffDevice] = f.physical_device_name,
                    --[LastDiffDevTyp] = f.device_type,
                    [Nrank] = RANK() OVER(PARTITION BY s.database_name ORDER BY s.backup_finish_date DESC)
             FROM msdb.dbo.backupset s,
                  msdb.dbo.backupmediafamily f
             WHERE s.media_set_id = f.media_set_id
                   AND s.type = 'I'
         -- and f.device_type = 7 -- only backup devices
         ) d
         WHERE nrank = 1),
lastlog
     AS (SELECT *
         FROM
         (
             SELECT [Database] = s.database_name,
                    --[Type]   = s.type,
                    [LastLogDate] = CONVERT( VARCHAR, s.backup_finish_date, 120),
                    --[LastLogSize]   = s.backup_size,
                    [LastLogDevice] = f.physical_device_name,
                    --[LastLogDevTyp] = f.device_type,
                    [Nrank] = RANK() OVER(PARTITION BY s.database_name ORDER BY s.backup_finish_date DESC)
             FROM msdb.dbo.backupset s,
                  msdb.dbo.backupmediafamily f
             WHERE s.media_set_id = f.media_set_id
                   AND s.type = 'L'
         -- and f.device_type = 7 -- only backup devices
         ) l
         WHERE nrank = 1)
SELECT db.[Instance] AS ServerName,
    db.[Database] AS DatabaseName,
    db.[RecoveryMode] AS ReconveyModel,
    db.[CreationTime] AS DBCreationTime,
    db.[Status] AS DatabaseStatus,
    ISNULL(CONVERT( VARCHAR, lastfull.LastFullDate), 'Never') AS LastFullBackupDate,
    ISNULL(lastfull.[LastFullDevice], 'None') AS LastFullBackupPath,
    ISNULL(CONVERT( VARCHAR, lastdiff.LastDiffDate), 'Never') AS LastDiffBackupDate,
    ISNULL(lastdiff.[LastDiffDevice], 'None') AS LastDiffBackupPath,
    ISNULL(CONVERT( VARCHAR, lastlog.[LastLogDate]), 'Never') AS LastLogBackupDate,
    ISNULL(lastlog.[LastLogDevice], 'None') AS LastLogBackupPath
FROM db
    LEFT OUTER JOIN lastfull ON(db.[Database] = lastfull.[Database])
    LEFT OUTER JOIN lastdiff ON(db.[Database] = lastdiff.[Database])
    LEFT OUTER JOIN lastlog ON(db.[Database] = lastlog.[Database]);

编辑：

假设我正在按照以下方式进行完整和差异备份： 

backup database AdventureWorks to disk =
'D:\MSSQL\AdventureWorks_1_041320161807.bak', disk =
'D:\MSSQL\AdventureWorks_2_041320161807.bak', disk =
'D:\MSSQL\AdventureWorks_3_041320161807.bak', disk =
'D:\MSSQL\AdventureWorks_4_041320161807.bak' with stats = 10;

backup database AdventureWorks
to disk = 'D:\MSSQL\AdventureWorks_1_041320161808.bak',
disk = 'D:\MSSQL\AdventureWorks_2_041320161808.bak',
disk = 'D:\MSSQL\AdventureWorks_3_041320161808.bak',
disk = 'D:\MSSQL\AdventureWorks_4_041320161808.bak'
with stats = 10, differential;

使用上面的脚本，我得到如下结果。请注意，它有 16 行。4*4。

在此处输入图像描述

我需要的是，脚本应该将输出显示为 4 个完整备份文件和 4 个差异备份文件。即只有文件的实际数量。目前由于 JOIN 它给出了 16 行。如果您需要进一步说明，请告诉我。

sql-server t-sql

2 个回答

  1. Best Answer

    您需要在子查询中添加一个新列来计算ROW_NUMBER()除 之外的RANK()，以便您可以匹配具有相同ROW_NUMBER().

    此外，您需要将 s 更改LEFT JOIN为FULL JOINs：

    WITH db
     AS (SELECT [Instance] = @@SERVERNAME,
                [Database] = name,
                [RecoveryMode] = DATABASEPROPERTYEX(name, 'Recovery'),
                [CreationTime] = crdate,
                [Status] = DATABASEPROPERTYEX(name, 'Status')
         FROM master..sysdatabases
         WHERE name != 'tempdb'),
lastfull
     AS (SELECT *
         FROM
         (
             SELECT [Database] = s.database_name,
                    --[Type]   = s.type,
                    [LastFullDate] = CONVERT( VARCHAR, s.backup_finish_date, 120),
                    --[LastFullSize]   = s.backup_size,
                    [LastFullDevice] = f.physical_device_name,
                    --[LastFullDevTyp] = f.device_type,
                    [Nrank] = RANK() OVER(PARTITION BY s.database_name ORDER BY s.backup_finish_date DESC),
                    [RN] = ROW_NUMBER() OVER(PARTITION BY s.database_name ORDER BY s.backup_finish_date DESC)
             FROM msdb.dbo.backupset s,
                  msdb.dbo.backupmediafamily f
             WHERE s.media_set_id = f.media_set_id
                   AND s.type = 'D'
         -- and f.device_type = 7 -- only backup devices
         ) f
         WHERE nrank = 1),
lastdiff
     AS (SELECT *
         FROM
         (
             SELECT [Database] = s.database_name,
                    --[Type]   = s.type,
                    [LastDiffDate] = CONVERT( VARCHAR, s.backup_finish_date, 120),
                    --[LastDiffSize]   = s.backup_size,
                    [LastDiffDevice] = f.physical_device_name,
                    --[LastDiffDevTyp] = f.device_type,
                    [Nrank] = RANK() OVER(PARTITION BY s.database_name ORDER BY s.backup_finish_date DESC),
                    [RN] = ROW_NUMBER() OVER(PARTITION BY s.database_name ORDER BY s.backup_finish_date DESC)
             FROM msdb.dbo.backupset s,
                  msdb.dbo.backupmediafamily f
             WHERE s.media_set_id = f.media_set_id
                   AND s.type = 'I'
         -- and f.device_type = 7 -- only backup devices
         ) d
         WHERE nrank = 1),
lastlog
     AS (SELECT *
         FROM
         (
             SELECT [Database] = s.database_name,
                    --[Type]   = s.type,
                    [LastLogDate] = CONVERT( VARCHAR, s.backup_finish_date, 120),
                    --[LastLogSize]   = s.backup_size,
                    [LastLogDevice] = f.physical_device_name,
                    --[LastLogDevTyp] = f.device_type,
                    [Nrank] = RANK() OVER(PARTITION BY s.database_name ORDER BY s.backup_finish_date DESC),
                    [RN] = ROW_NUMBER() OVER(PARTITION BY s.database_name ORDER BY s.backup_finish_date DESC)
             FROM msdb.dbo.backupset s,
                  msdb.dbo.backupmediafamily f
             WHERE s.media_set_id = f.media_set_id
                   AND s.type = 'L'
         -- and f.device_type = 7 -- only backup devices
         ) l
         WHERE nrank = 1)
SELECT db.[Instance] AS ServerName,
    db.[Database] AS DatabaseName,
    db.[RecoveryMode] AS ReconveyModel,
    db.[CreationTime] AS DBCreationTime,
    db.[Status] AS DatabaseStatus,
    ISNULL(CONVERT( VARCHAR, lastfull.LastFullDate), 'Never') AS LastFullBackupDate,
    ISNULL(lastfull.[LastFullDevice], 'None') AS LastFullBackupPath,
    ISNULL(CONVERT( VARCHAR, lastdiff.LastDiffDate), 'Never') AS LastDiffBackupDate,
    ISNULL(lastdiff.[LastDiffDevice], 'None') AS LastDiffBackupPath,
    ISNULL(CONVERT( VARCHAR, lastlog.[LastLogDate]), 'Never') AS LastLogBackupDate,
    ISNULL(lastlog.[LastLogDevice], 'None') AS LastLogBackupPath
FROM db
    FULL OUTER JOIN lastfull 
        ON db.[Database] = lastfull.[Database]
    FULL OUTER JOIN lastdiff 
        ON db.[Database] = lastdiff.[Database]
        AND lastfull.RN = lastdiff.RN
    FULL OUTER JOIN lastlog 
        ON db.[Database] = lastlog.[Database]
        AND lastfull.RN = lastlog.RN;
    • 1

  2. 除了spaghettidba的回答，我一直在使用这个脚本，它没有太多信息，但如果需要可以添加。

    它似乎更快，并且您可以指定是要显示完整备份还是要显示日志备份。

    主要是我用它来找出这些问题：我们有没有备份，备份在哪里，有多大，备份通常需要多长时间。

    declare  @backup_type CHAR(1) = 'D' --'D' full, 'L' log
                ;with Radhe as (
                    SELECT  @@Servername as [Server_Name],
                    B.name as Database_Name, 
                    ISNULL(STR(ABS(DATEDIFF(day, GetDate(), MAX(Backup_finish_date)))), 'NEVER') as DaysSinceLastBackup,
                    ISNULL(Convert(char(11), MAX(backup_finish_date), 113)+ ' ' + CONVERT(VARCHAR(8),MAX(backup_finish_date),108), 'NEVER') as LastBackupDate
                    ,BackupSize_GB=CAST(COALESCE(MAX(A.BACKUP_SIZE),0)/1024.00/1024.00/1024.00 AS NUMERIC(18,2))
                    ,BackupSize_MB=CAST(COALESCE(MAX(A.BACKUP_SIZE),0)/1024.00/1024.00 AS NUMERIC(18,2))
                    ,media_set_id = MAX(A.media_set_id)
                    ,[AVG Backup Duration]= AVG(CAST(DATEDIFF(s, A.backup_start_date, A.backup_finish_date) AS int))
                    ,[Longest Backup Duration]= MAX(CAST(DATEDIFF(s, A.backup_start_date, A.backup_finish_date) AS int))
                    ,A.type
                    FROM sys.databases B 

                    LEFT OUTER JOIN msdb.dbo.backupset A 
                                 ON A.database_name = B.name 
                                AND (@backup_type IS NULL OR A.type = @backup_type  )

                    GROUP BY B.Name, A.type

                )

                 SELECT r.[Server_Name]
                       ,r.Database_Name
                       ,[Backup Type] = r.type 
                       ,r.DaysSinceLastBackup
                       ,r.LastBackupDate
                       ,r.BackupSize_GB
                       ,r.BackupSize_MB
                       ,F.physical_device_name
                       ,r.[AVG Backup Duration]
                       ,r.[Longest Backup Duration]

                   FROM Radhe r

                    LEFT OUTER JOIN msdb.dbo.backupmediafamily F
                                 ON R.media_set_id = F.media_set_id

                    ORDER BY r.Server_Name, r.Database_Name
    • 1

相关问题