Skip to content

Instantly share code, notes, and snippets.

@tcartwright

tcartwright/GetBackupInformation.sql

Last active March 4, 2021 15:19
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save tcartwright/9e1c29bdc5648140d1eafc9595f1cdfc to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save tcartwright/9e1c29bdc5648140d1eafc9595f1cdfc to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. tcartwright revised this gist Mar 4, 2021. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion GetBackupInformation.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,5 +1,5 @@
    /*
    Tim C: This query can be used as a normal query, or it can also be used to import into an excel calendar so
    Tim C: This query can be used as a normal query, or it can also be used to import into an OUTLOOK calendar so
    you can visualize your backups. Similar to https://dbatools.io/timeline/
    RECOMMENDATIONS:
  2. tcartwright revised this gist Jan 26, 2021. 1 changed file with 2 additions and 1 deletion.
    3 changes: 2 additions & 1 deletion GetBackupInformation.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -99,8 +99,9 @@ CROSS APPLY (
    ) fnSize
    WHERE bs.backup_start_date > DATEADD(DAY, -14, GETDATE())
    AND LEN(fnInfo.file_name) > 0
    --/* temp to check for fulls during the day */
    AND bs.type IN ('D', 'I')
    --/* temp to check for fulls during the day */
    --AND bs.type = 'D'
    --AND CAST(bs.backup_start_date AS TIME) BETWEEN '08:00:00' AND '19:00:00'
    --AND DATEPART(WEEKDAY, bs.backup_start_date) BETWEEN 2 AND 6
    --/* temp to check for fulls during the day */
  3. tcartwright revised this gist Jan 26, 2021. 1 changed file with 7 additions and 4 deletions.
    11 changes: 7 additions & 4 deletions GetBackupInformation.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,8 +1,11 @@
    /*
    Tim C: This query can be used as a normal query, or it can also be used to import into an excel calendar so you can visualize your backups.
    RECOMMENDATIONS:
    1) You should import into a CUSTOM blank excel calendar
    2) You should not import TLOG backups, as that will utterly bloat the calendar
    Tim C: This query can be used as a normal query, or it can also be used to import into an excel calendar so
    you can visualize your backups. Similar to https://dbatools.io/timeline/
    RECOMMENDATIONS:
    1) You should import into a CUSTOM blank excel calendar
    2) You should not import TLOG backups, as that will utterly bloat the calendar
    ******************************************************************************************
    TO CREATE A NEW CALENDAR:
    - Switch to the calendar view
  4. tcartwright created this gist Jan 26, 2021.
    107 changes: 107 additions & 0 deletions GetBackupInformation.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,107 @@
    /*
    Tim C: This query can be used as a normal query, or it can also be used to import into an excel calendar so you can visualize your backups.
    RECOMMENDATIONS:
    1) You should import into a CUSTOM blank excel calendar
    2) You should not import TLOG backups, as that will utterly bloat the calendar
    ******************************************************************************************
    TO CREATE A NEW CALENDAR:
    - Switch to the calendar view
    - Click the Folder menu
    - Click New Calendar on the menu bar
    ******************************************************************************************
    TO IMPORT TO EXCEL:
    - Save results as CSV, ensure the headers are saved into the CSV
    - In Outlook click the File menu
    - Click on open and Export
    - Click Import/Export
    - Select "Import from another program or file"
    - Click Next
    - Select "Comma seperated values"
    - Click Next
    - Browse to and select the CSV saved from these query results
    - Optional: If you have imported results previously, you should select "Replace duplicates with items imported"
    - Select the custom destination calendar folder previously created. (Highly advise not to import to an existing calendar)
    - Click the checkbox in front of the import file name
    - The important fields should auto match up.
    - Subject, start date, start time, end date, end time, description
    - If some of these fields did not auto map, you will have to manually map
    - Click OK
    - Click Finish
    ******************************************************************************************
    TO DELETE ALL ITEMS OUT OF A CALENDAR:
    OPTION 1)
    - Delete the calendar and recreate
    OPTION 2)
    - In Calendar, click View.
    - In the Current View group, click Change View, and then click List.
    - Click in the item list, and then press Ctrl+A. All items in the list should now be selected.
    - Press Delete.
    */


    SELECT [server_name] = fn.server
    ,[subject] = CONCAT(fnInfo.server, ' - ', bs.[database_name], ' - ', bs.type)
    ,[backup_week_day] = DATENAME(weekday, bs.backup_start_date)
    ,[start date] = FORMAT(bs.[backup_start_date], 'yyyy-MM-dd HH:mm:ss')
    ,[start time] = FORMAT(bs.[backup_start_date], 'yyyy-MM-dd HH:mm:ss')
    ,[end date] = FORMAT(bs.[backup_finish_date], 'yyyy-MM-dd HH:mm:ss')
    -- this column is for excel. the calendar does not support very short intervals, so add 15 if less than 15
    ,[end time] = FORMAT(CASE
    WHEN DATEDIFF(MINUTE,bs.backup_start_date, bs.backup_finish_date) < 15
    THEN DATEADD(MINUTE, 15, bs.backup_start_date)
    ELSE bs.backup_start_date
    END, 'yyyy-MM-dd HH:mm:ss')
    ,fnInfo.[backup_size_mb]
    ,fnInfo.[backup_size_gb]
    ,[backup_elapsed_time] = fnInfo.[backup_time]
    ,bs.[type]
    ,fnInfo.type_name
    ,fnInfo.[path]
    ,fnInfo.[file_name]
    ,[description] = CONCAT('"',
    'Server = ', CAST(SERVERPROPERTY('Servername') AS sysname), fn.crlf,
    'Database = ', bs.database_name, fn.crlf,
    'Type = ', fnInfo.type_name, '(', bs.type, ')', fn.crlf,
    'Start Date = ', bs.backup_start_date, fn.crlf,
    'End Date = ', bs.backup_finish_date, fn.crlf,
    'Size = ', fnSize.size, fn.crlf,
    'Path = ', fnInfo.path, fnInfo.file_name, fn.crlf, '"')
    FROM msdb.dbo.backupset bs
    INNER JOIN msdb.dbo.backupmediafamily bm ON (bs.media_set_id = bm.media_set_id)
    CROSS APPLY (
    SELECT [last_back_slash] = CHARINDEX('\',REVERSE(bm.physical_device_name)) - 1
    ,crlf = CHAR(13) + CHAR(10)
    ,[server] = CAST(SERVERPROPERTY('Servername') AS sysname)
    ) fn
    CROSS APPLY (
    SELECT [path] = CASE WHEN fn.[last_back_slash] <= 0 THEN bm.physical_device_name ELSE LEFT(bm.physical_device_name, LEN(bm.physical_device_name) - fn.[last_back_slash]) END
    ,[file_name] = CASE WHEN fn.[last_back_slash] <= 0 THEN '' ELSE REVERSE(LEFT(REVERSE(bm.physical_device_name), fn.[last_back_slash])) END
    ,[backup_time] = CONVERT(VARCHAR(20),(bs.backup_finish_date - bs.[backup_start_date]), 108)
    ,[backup_size_mb] = CAST(bs.backup_size/1000.0/1000.0 AS DECIMAL(18, 2))
    ,[backup_size_gb] = CAST(bs.backup_size/1000.0/1000.0/1000.0 AS DECIMAL(18, 2))
    ,[type_name] = CASE bs.[type]
    WHEN 'D' THEN 'Database'
    WHEN 'I' THEN 'Differential database'
    WHEN 'L' THEN 'Log'
    WHEN 'F' THEN 'File or filegroup'
    WHEN 'G' THEN 'Differential file'
    WHEN 'P' THEN 'Partial'
    WHEN 'Q' THEN 'Differential partial'
    ELSE 'Unknown'
    END
    ,[server] = LEFT(fn.server, CHARINDEX('\', fn.server) - 1)
    ) fnInfo
    CROSS APPLY (
    SELECT [size] = CASE WHEN fnInfo.backup_size_gb > 0 THEN CONCAT(fnInfo.backup_size_gb, ' GB') ELSE CONCAT(fnInfo.backup_size_mb, ' MB') END
    ) fnSize
    WHERE bs.backup_start_date > DATEADD(DAY, -14, GETDATE())
    AND LEN(fnInfo.file_name) > 0
    --/* temp to check for fulls during the day */
    AND bs.type IN ('D', 'I')
    --AND CAST(bs.backup_start_date AS TIME) BETWEEN '08:00:00' AND '19:00:00'
    --AND DATEPART(WEEKDAY, bs.backup_start_date) BETWEEN 2 AND 6
    --/* temp to check for fulls during the day */
    ORDER BY bs.database_name,
    bs.backup_start_date,
    bs.backup_finish_date