May 25, 2023 at 3:27 pm
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,
May 25, 2023 at 6:21 pm
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
May 26, 2023 at 10:47 am
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:
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.
June 2, 2023 at 6:40 am
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