April 3, 2017 at 11:30 am
We have a SQL server that is a virtual server.
I query the backupset table to see the compression info for my backups. But found there are some backups that are not what I setup using the native backup/maintenance process. Because I know I setup to use compression mode for my backups. But these backups are not using compression. So I think the backup is done through the VM snapshot process our network persons setup nightly.
Is there a way in the query to find out which are done by native tool, which are done by other tools?
Thanks,
April 3, 2017 at 12:32 pm
My first suggestion would be to check the "Name" column. In my case, when the backups are run by the 3rd party backup tool we use (SQL backups, not VM snapshots) the tool itself puts the name in there (when running a native SQL backup, this would be the name = 'some name' parameter)
If it's VM snapshots, then I'd be willing to bet the name field will likely be empty.
April 3, 2017 at 12:42 pm
Thanks,
yes, I noticed that tool, I see it is null at name column for those VM backup records I guess.
When you say you use SQL backups, the tool itself puts the name in there, what is that name, is there any difference like default name created by maintenance plan of the native tool?
Thanks,
April 3, 2017 at 12:43 pm
Yes third party tools generally insert a backup row into the tables. The name of the backup will usually be some useless uniquifier. From my experience this includes vm snapshots and vm backups.
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
April 4, 2017 at 3:01 am
Speaking as a 3rd party backup tool vendor, ours sure does.
Most of the 3rd party backup tools are, to one degree or another, using native backups. So yes, they will show up there.
However, it is possible to use some of the snapshot technologies to create a "backup" that is a page-by-page, transactionally consistent, copy of the database without using native backup. Those would not be written to the msdb tables.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 4, 2017 at 3:31 am
Actually, they are, Grant - with SnapManager at least. Here's an example.
description backup_start_date type database_name is_snapshot DeviceType physical_device_name
----------------------------------------------------------------------- ----------------------- ---- -------------- ----------- -------------- ---------------------------
NetApp SnapManager Snapshot based backup, restore it using SnapManager. 2017-04-03 21:31:24.000 D MyDatabase 1 Virtual device NTAPDV__MyServer_MyDatabase
John
April 4, 2017 at 3:45 am
John Mitchell-245523 - Tuesday, April 4, 2017 3:31 AMActually, they are, Grant - with SnapManager at least. Here's an example.
description backup_start_date type database_name is_snapshot DeviceType physical_device_name
----------------------------------------------------------------------- ----------------------- ---- -------------- ----------- -------------- ---------------------------
NetApp SnapManager Snapshot based backup, restore it using SnapManager. 2017-04-03 21:31:24.000 D MyDatabase 1 Virtual device NTAPDV__MyServer_MyDatabaseJohn
Cool. I guess I should have said, "they won't be automatically". I'm sure they add that in some fashion through the tool because there's no requirement in how snapshots work that would make that happen on it's own, unlike the native backup process.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply