Как администраторы баз данных SQL Server, важно правильно управлять резервными копиями, чтобы обеспечить восстановление из любого сценария катастрофы. Однако просто наличие файла резервной копии может быть недостаточно в некоторых случаях. Вы можете не знать фактического объема дискового пространства, необходимого для файлов .mdf и .ldf, пока они не будут восстановлены. Кроме того, у вас может не быть доступа к исходному экземпляру SQL Server для определения размера файлов данных.

В этой статье мы рассмотрим различные подходы к отслеживанию полезной информации для полных резервных копий, включая фактические размеры файлов.

Вариант 1 – Список размеров файлов баз данных SQL Server и размер резервной копии

В этом варианте мы сосредоточимся на данных самих баз данных и добавим размер их последней полной резервной копии. Скрипт регулярно захватывает эту информацию и сохраняет ее во временной таблице. Запрос объединяет данные из различных системных таблиц, чтобы предоставить всесторонний обзор размеров баз данных и размеров резервных копий.

IF OBJECT_ID('tempdb..#FreeSpace') IS NOT NULL DROP TABLE #FreeSpace

CREATE TABLE #FreeSpace([database] VARCHAR(64) NOT NULL,amount INT NOT NULL)

DECLARE @sqlCommand varchar(2048)

SELECT @sqlCommand = 'USE [?]
           DECLARE @freeSpace INT
           SELECT @freeSpace = SUM(size/128 -(FILEPROPERTY(name, ''SpaceUsed'')/128)) FROM sys.master_files
           INSERT INTO #FreeSpace VALUES(''?'', @freeSpace)
          '

EXEC sp_MSforeachdb @sqlCommand

SELECT DISTINCT
          d.name AS 'DatabaseName',
          (SELECT CONVERT( DECIMAL(10,2),SUM(size)*8.0/1024) 
           FROM sys.master_files 
           WHERE type_desc = 'ROWS' 
             AND database_id = mf.database_id 
           GROUP BY database_id) AS 'DataSizeInMB',
          (SELECT CONVERT(DECIMAL(10,2),SUM(size)*8.0/1024) 
           FROM sys.master_files 
           WHERE type_desc = 'LOG' 
              AND database_id = mf.database_id 
           GROUP BY database_id) AS 'LogSizeInMB',
          (SELECT amount 
           FROM #FreeSpace 
           WHERE [database] = d.name) AS 'FreeSpaceInMB',
          CONVERT(DECIMAL(10,2),b.compressed_backup_size/1024.0/1024.0) AS CompressedBackupSizeInMB,
          d.state_desc AS 'State',
          suser_sname(d.owner_sid) AS 'Owner',
          d.compatibility_level AS 'CompatibilityLevel',
          d.create_date AS 'DBCreatedDate'
FROM      sys.databases d
JOIN      sys.master_files mf ON d.database_id = mf.database_id
LEFT JOIN (
         SELECT bs.compressed_backup_size,bs.database_name
         FROM msdb.dbo.backupset bs
         WHERE bs.backup_set_id IN (SELECT backup_set_id FROM msdb.dbo.backupset WHERE backup_start_date = (SELECT MAX(backup_start_date) FROM msdb.dbo.backupset WHERE database_name = bs.database_name))
        ) AS b ON b.database_name = d.name
WHERE     d.name NOT IN ('tempdb')
ORDER BY  d.name

DROP TABLE #FreeSpace

Вариант 2 – Список размеров файлов баз данных SQL Server и размер резервной копии

Этот вариант сосредоточен на информации о резервной копии. Скрипт использует общие выражения (CTE) для создания наборов результатов, содержащих самые последние полные резервные копии и соответствующую информацию. Запрос объединяет данные из системных таблиц и CTE, чтобы предоставить всесторонний обзор размеров баз данных, размеров резервных копий и другой актуальной информации.

WITH 
   MostRecentBackups
   AS(
      SELECT
         database_name AS [Database],
         MAX(bus.backup_finish_date) AS LastBackupTime,
         CASE bus.type
            WHEN 'D' THEN 'Full'
         END AS Type
      FROM msdb.dbo.backupset bus
      WHERE bus.type <> 'F'
      GROUP BY bus.database_name,bus.type
   ),
   BackupsWithSize
   AS(
      SELECT
	mrb.*,
	(SELECT TOP 1 CONVERT(DECIMAL(10,2), b.compressed_backup_size/1024/1024) AS backup_size FROM msdb.dbo.backupset b WHERE [Database] = b.database_name AND LastBackupTime = b.backup_finish_date) AS [Backup Size],
	(SELECT TOP 1 DATEDIFF(s, b.backup_start_date, b.backup_finish_date) FROM msdb.dbo.backupset b WHERE [Database] = b.database_name AND LastBackupTime = b.backup_finish_date) AS [Seconds],
        (SELECT TOP 1 b.media_set_id FROM msdb.dbo.backupset b WHERE [Database] = b.database_name AND LastBackupTime = b.backup_finish_date) AS media_set_id
      FROM MostRecentBackups mrb
   )

SELECT
    d.name AS [Database],
    d.state_desc AS State,
    bf.LastBackupTime AS [LastFull],
    DATEDIFF(DAY,bf.LastBackupTime,GETDATE()) AS [TimeSinceLastFullInDays],
    bf.[Backup Size] AS [FullBackupSizeInMB],
    bf.Seconds AS [FullBackupSecondsToComplete],
    CASE WHEN DATEDIFF(DAY,bf.LastBackupTime,GETDATE()) > 14 THEN NULL ELSE (SELECT TOP 1 bmf.physical_device_name FROM msdb.dbo.backupmediafamily bmf WHERE bmf.media_set_id = bf.media_set_id AND bmf.device_type = 2) END AS [FullBackupLocalPath],     
	(SELECT CONVERT(DECIMAL(10,2),SUM(size)*8.0/1024) AS size FROM sys.master_files WHERE type = 0 AND d.name = DB_NAME(database_id)) AS DataFileSize,
    (SELECT CONVERT(DECIMAL(10,2),SUM(size)*8.0/1024) AS size FROM sys.master_files WHERE type = 1 AND d.name = DB_NAME(database_id)) AS LogFileSize
FROM sys.databases d
LEFT JOIN BackupsWithSize bf ON (d.name = bf.[Database] AND (bf.Type = 'Full' OR bf.Type IS NULL))
WHERE d.name <> 'tempdb' AND d.source_database_id IS NULL
ORDER BY d.name

Вариант 3 – Получение размера файлов баз данных SQL Server из файла резервной копии

Этот вариант сосредоточен исключительно на самом файле резервной копии. Скрипт использует команду “RESTORE FILELISTONLY” для извлечения информации о размере непосредственно из файла. Он создает временную таблицу для хранения набора результатов, а затем извлекает необходимую информацию. Скрипт учитывает дополнительный столбец, добавленный в набор результатов в SQL Server 2016, и соответствующим образом корректирует определение таблицы.

-- введите путь и имя файла резервной копии
DECLARE @filename nvarchar(500) = 'C:\Temp\test.bak'    
    
IF NOT EXISTS (SELECT * FROM dbo.sysobjects where id = object_id(N'RestoreFilelistOnly') and OBJECTPROPERTY(id, N'IsTable') = 1)
BEGIN
SET NOCOUNT ON;
DECLARE @sqlCommand NVARCHAR(2048);

IF(
   (SELECT
      CASE 
         WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) LIKE '8%'    THEN 0
         WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) LIKE '9%'    THEN 0
         WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) LIKE '10.0%' THEN 0
         WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) LIKE '10.5%' THEN 0
         WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) LIKE '11%'   THEN 0
         WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) LIKE '12%'   THEN 0
         WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) LIKE '13%'   THEN 1     
         WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) LIKE '14%'   THEN 1
         WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) LIKE '15%'   THEN 1 
         ELSE 1
      END
   ) = 0
)

SET @sqlCommand = '
CREATE TABLE ##RestoreFilelistOnly (
    [LogicalName]           NVARCHAR(128),
    [PhysicalName]          NVARCHAR(260),
    [Type]                  CHAR(1),
    [FileGroupName]         NVARCHAR(128),
    [Size]                  NUMERIC(20,0),
    [MaxSize]               NUMERIC(20,0),
    [FileID]                BIGINT,
    [CreateLSN]             NUMERIC(25,0),
    [DropLSN]               NUMERIC(25,0),
    [UniqueID]              UNIQUEIDENTIFIER,
    [ReadOnlyLSN]           NUMERIC(25,0),
    [ReadWriteLSN]          NUMERIC(25,0),
    [BackupSizeInBytes]     BIGINT,
    [SourceBlockSize]       INT,
    [FileGroupID]           INT,
    [LogGroupGUID]          UNIQUEIDENTIFIER,
    [DifferentialBaseLSN]   NUMERIC(25,0),
    [DifferentialBaseGUID]  UNIQUEIDENTIFIER,
    [IsReadOnly]            BIT,
    [IsPresent]             BIT,
    [TDEThumbprint]         VARBINARY(32)
)'

ELSE
SET @sqlCommand = '
CREATE TABLE ##RestoreFilelistOnly (
    [LogicalName]           NVARCHAR(128),
    [PhysicalName]          NVARCHAR(260),
    [Type]                  CHAR(1),
    [FileGroupName]         NVARCHAR(128),
    [Size]                  NUMERIC(20,0),
    [MaxSize]               NUMERIC(20,0),
    [FileID]                BIGINT,
    [CreateLSN]             NUMERIC(25,0),
    [DropLSN]               NUMERIC(25,0),
    [UniqueID]              UNIQUEIDENTIFIER,
    [ReadOnlyLSN]           NUMERIC(25,0),
    [ReadWriteLSN]          NUMERIC(25,0),
    [BackupSizeInBytes]     BIGINT,
    [SourceBlockSize]       INT,
    [FileGroupID]           INT,
    [LogGroupGUID]          UNIQUEIDENTIFIER,
    [DifferentialBaseLSN]   NUMERIC(25,0),
    [DifferentialBaseGUID]  UNIQUEIDENTIFIER,
    [IsReadOnly]            BIT,
    [IsPresent]             BIT,
    [TDEThumbprint]         VARBINARY(32), 
    [SnapshotUrl]           NVARCHAR(360)
)'

EXEC sp_executesql @sqlCommand;

INSERT INTO ##RestoreFilelistOnly EXEC('RESTORE FILELISTONLY FROM DISK = ''' + @filename + '''')

SELECT PhysicalName, CONVERT(DECIMAL(10,3),(Size/1024/1024)) as FileSizeMB, CONVERT(DECIMAL(10,3),(BackupSizeInBytes/1024/1024)) as BackupSizeMB
FROM ##RestoreFilelistOnly

DROP TABLE ##RestoreFilelistOnly

END

Эти три варианта предоставляют разные подходы к отслеживанию размеров баз данных SQL Server и размеров резервных копий. В зависимости от ваших конкретных требований и предпочтений вы можете выбрать наиболее подходящий вариант.

Регулярное захватывание и анализ этой информации позволяет обеспечить точное понимание размеров баз данных и размеров резервных копий, что позволяет принимать обоснованные решения и эффективно управлять вашей средой SQL Server.