What is the is_snapshot column on msdb.backupset for

Question

What is the is_snapshot column on msdb.backupset for

NigelM

SSC Enthusiast

Points: 174
More actions
April 22, 2020 at 10:50 am

#3745254
Hi,
I've found myself taking on a SQL Server 2014 instance and I'm trying to work out what backups are taken etc. There are 2 servers with an always on availability group setup. These are also on Azure VMs
I've worked out the main backups (and reinstated log backups which hadn't happened since Oct last year!!) but there is also something taking a "snapshot backup" everynight:
```
type database_name   user_name              backup_start_date    is_snapshot 
---- --------------- ---------------------- -----------------    ----------  
D    DBNameHere      NT AUTHORITY\SYSTEM    2020-04-22 02:49:06  1           
D    DBNameHere      Domain\zzProdSqlAgent  2020-04-22 00:06:19  0           
D    DBNameHere      NT AUTHORITY\SYSTEM    2020-04-21 02:44:27  1           
D    DBNameHere      Domain\zzProdSqlAgent  2020-04-21 01:00:29  0           
```
If I look this up in the docs it says:
is_snapshot bit 1 = Backup was taken using the SNAPSHOT option.
And elsewhere suggests "WITH SNAPSHOT" was used. However, even checking in the SQL Server 2014 docs I can find no reference as to what this actually does! Googling for it also shows many things about database snapshots but don't think that is what this is - i can't see any snapshot DBs on the servers.
Does anyone have any clue where I should look next?

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

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

NigelM SSC Enthusiast Points: 174 More actions · Answer 1

Ah - found https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2005/ms189548(v=sql.90)?redirectedfrom=MSDN

(via https://www.sqlservercentral.com/forums/topic/snapshot-backups-and-transaction-logs!)

So now to find which third party tool is doing it...

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

There's a column in backupset called description or backup_description or something like that. Many third party utilities will write their name in there.

John

NigelM SSC Enthusiast Points: 174 More actions · Answer 3

Thanks. Sadly it was blank - but it turns out it's something in Azure doing snapshots (not found where it's putting them or if they are actually any use - but that's a different question for a different forum!)