Database backups going to device

  • Hi Friends,

    How can I know the exact device where my backups are going on?

    I only see {67BAB2D0-A1DC-44F8-BF73-EBAFD5AE3220}16 as one device. However, I do not find out where are my backup files located.

    Some databases are going to a particular drive, but others are using that expression,

    Thank you,

    Best Regards,

  • Adapted from https://www.mssqltips.com/sqlservertip/2960/sql-server-backup-paths-and-file-management/

    DECLARE @media_family_id uniqueidentifier = '67BAB2D0-A1DC-44F8-BF73-EBAFD5AE322'

    -- File name : Where are the backups.sql
    -- Author : Graham Okely B App Sc
    -- Scope : OK on SQL Server 2000,2005,2008R2,2012
    -- Select the information we require to make a decision about which backup we want to use

    select a.server_name, a.database_name, backup_finish_date, a.backup_size,
    CASE a.[type] -- Let's decode the three main types of backup here
    WHEN 'D' THEN 'Full'
    WHEN 'I' THEN 'Differential'
    WHEN 'L' THEN 'Transaction Log'
    ELSE a.[type]
    END as BackupType
    ,b.physical_device_name
    from msdb.dbo.backupset a join msdb.dbo.backupmediafamily b
    on a.media_set_id = b.media_set_id
    where b.media_family_id = @media_family_id
    order by a.backup_finish_date desc
  • These are backups taken using the SQL Server VSS Writer service which was presumably invoked by whatever backup software your infrastructure team are using to back up servers. If you think they are interfering with your backups, run the following to check they are copy_only:

    SELECT  S.backup_start_date, S.backup_finish_date, M.physical_device_name, S.[database_name], S.[type], S.is_copy_only
    FROM msdb.dbo.backupset S
    JOIN msdb.dbo.backupmediafamily M
    ON S.media_set_id = M.media_set_id
    WHERE M.physical_device_name = '{67BAB2D0-A1DC-44F8-BF73-EBAFD5AE3220}16'
    ORDER BY S.[database_name], S.backup_finish_date DESC;

    If they are not copy_only, and they are interfering with your backups, then try contacting your infrastructure team to see if:

    1. They can make the SQL Server VSS backups copy_only.
    2. Disable VSS backups for SQL Server. This will mean if the server is every restored the SQL Server databases are likely to be in an inconsistent state so SQL Server restores will be needed as well.

    If you do not get very far with this you can just disable the SQL Server VSS Writer service on the server. Again, this will cause the databases to be in an inconsistent state if a server restore is done.

    It may also be possible to look into using your infrastructure team's software to do the SQL Server backups. If the software supports this it may be a better long term solution (reduced involvement in DR, immutable backups etc) but it will mean rewriting your backup and restore routines.

     

  • As Ken says, these are very likely being produced by whatever process your infrastructure team is using to back up the entire server estate (all servers, not just SQL).

    There definitely should be a conversation between the DB team and the infrastructure team on what is being achieved with these backups, and how useful they are thought to be if a restore is needed.

    I have seen an infrastructure viewpoint that 'their' backups mean job done and nothing else needed. The DB team need to make their point that copy-only backups without log backups may not result in a useable database.

    As Jeff Moden said (or quoted another wise person as saying) 'You do not need a backup strategy, you need a restore strategy'. Your restore strategy needs to produce a useable database restored to the specific timestamp that is needed by the business. If you rely only on copy-only backups it will be very hard to achieve this.

     

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply