Database backup

Question

Database backup

olegasdo

Mr or Mrs. 500

Points: 585
More actions
March 5, 2014 at 6:16 am

#304961

Hello
I've came to situation where I can not find who is backing up the server. 🙂
At a specific time database get backed up with two tools. One is known, another is the one that needs to be tracked.
Windows logs show these backups as a regular SQL backups, nothing special about these.
SQL trace shows that backups as a backup with following info
BACKUP DATABASE [DBNAME] TO VIRTUAL_DEVICE='{GUID comes here}' WITH SNAPSHOT,BUFFERCOUNT=1,BLOCKSIZE=1024,COPY_ONLY
SQL trace shows that backups are done with LocalSystem account and hostname is server name.
I can not find any jobs that might start such backups. Also Backup admins say that they have one tool configured for backups (the known one). And Sharepoint Admins say that database backup is not enabled it the settings (I've checked in SP 2013 you can configure Content DB backup from Sharepoint administration).
Maybe someone can advice how to track down that application, which is doing backups?
Thanks.
Olegas

Viewing 8 posts - 1 through 7 (of 7 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

I have frequently seen Commvault to be the source of backups as you describe them.

You might want to check with the sysadmins to see if anybody has a tool such as commvault installed that might be running.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Matt Crowley SSCarpal Tunnel Points: 4163 More actions · Answer 2

Sounds like you have an agent installed for the backups, if the servername comes back as localhost. Poke on through the services on the box, and see if anything jumps out as a rogue backup agent. You can also peruse some of the backup tables in the msdb database to see if you can get any clues as to what the process is from there. Start with this:

select bs.backup_start_date, bs.type, bs.database_name, bs.name, bs.user_name, bmf.physical_device_name

from backupset bs join

backupmediafamily bmf on bs.media_set_id = bmf.media_set_id

order by backup_start_date desc

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 3

Run profiler during the backups and check for the Applicatio Name\Host name. That will give clue about the source of these backups.

Using this query might also help you ..

SELECT bs.database_name AS DbName

, bs.type AS BackupType

, CAST ( bs.backup_size / 1024.0 / 1024 AS DECIMAL(10, 2) ) AS BackupSize_MB

, CAST ( bs.backup_size / 1024.0 / 1024 / 1024 AS DECIMAL(10, 2) ) AS BackupSize_GB

, bs.backup_start_date AS Backup_StartDate

, bs.backup_finish_date AS Backup_EndDate

, DATEDIFF ( ss, bs.backup_start_date, bs.backup_finish_date ) AS TookHowLong_Seconds

, CAST ( DATEDIFF ( ss, bs.backup_start_date, bs.backup_finish_date ) / 60.0 AS DECIMAL(10,2) ) AS TookHowLong_Minutes

, bs.name AS BackupName

, bs.description AS BackupDescription

, bs.[user_name] AS UserNameTakingBackup

, CAST(bs.software_major_version AS VARCHAR) + '.' + CAST(bs.software_minor_version AS VARCHAR) + '.'

+ CAST(bs.software_build_version AS VARCHAR) AS SoftwareBuildVersion

, bs.server_name AS ServerName

, bs.machine_name AS MachineName

, bs.collation_name AS Collation

, bmf.physical_device_name AS DeviceName

FROM msdb.dbo.backupset bs JOIN msdb.dbo.backupmediafamily bmf

ON bs.media_set_id = bmf.media_set_id

WHERE bs.database_name = 'DBNAME'

order by bs.backup_start_date desc

--

SQLBuddy

Matt Crowley SSCarpal Tunnel Points: 4163 More actions · Answer 4

Actually, as I think about it, you should be able to capture the process ID of the backup process from sys.dm_exec_sessions and sys.dm_exec_requests.

select s.session_id, s.program_name, s.host_process_id, s.login_name, s.nt_user_name

from sys.dm_exec_sessions s join

sys.dm_exec_requests r on s.session_id = r.session_id

where r.command = 'BACKUP DATABASE'

knowing the process ID, you can track back to the executable name in Task Manager.

olegasdo Mr or Mrs. 500 Points: 585 More actions · Answer 5

Process ID and trace shows

Microsoft SQL Server VSS Writer

And that's it.

No idea who is initializing the backup.

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 6

olegas.domanskis (3/5/2014)
Process ID and trace shows
Microsoft SQL Server VSS Writer
And that's it.
No idea who is initializing the backup.

It could be a 3rd party backup. What was output of the script that I provided ?

Also see if you have any batch files scheduled using windows task scheduler that are taking these backups.

If you can post a screenshot of trace\query output that would be helpful.

--

SQLBuddy

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

Is somebody performing a SAN snapshot? This will also cause the GUID entry.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events