Backups to tape - how to check.

Question

Post reply

Backups to tape - how to check.

Joy Smith San

SSC-Insane

Points: 24877
More actions
December 23, 2014 at 3:04 am

#302052

Hi,
One of the user SQL Server, managed by them.
I can see all the DBs are backed up, but no jobs have been configured for backups.
By checking the physical_device_name, I assume it's directly taken to tape.
Is there anyway to find ourselves how the DB backups are taken ?.
..and also When I check from SSMS I see tape option disabled as well.
Thanks.
Joy.

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Joy Smith San (12/23/2014)
By checking the physical_device_name, I assume it's directly taken to tape.

What in the physical name leads to that assumption?

Is there anyway to find ourselves how the DB backups are taken ?.

Start with asking your sysadmin. Someone would have had to set that up.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 2

Thanks Gila.

Well, I just assumed as I saw the value as some guid kind of value instead of the actual physical location.

Yes, will be getting the details directly from them now.

Thanks.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

If the backup is to tape, there has to be a tape drive in the server, the backup will be very, very slow and the physical device is something like '\\.\tape0'

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 4

Joy Smith San

SSC-Insane

Points: 24877

December 23, 2014 at 3:22 am

#1766142

ok.. Let me check with them anyway.

Thank you.

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 5

Confirmed they haven't configured anything.

I checked it further, found device_type = 7 and hence assume its the VSS backup updating backupset table in msdb database (Not sure if the actual DB backups are taken or not..). Not familiar with VM or VSS backups... Investigating further....

Anyone knows, how to confirm if the actual backup is taken by VSS... Does it update backupset table..? Thanks.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 6

Joy Smith San (1/4/2015)
Confirmed they haven't configured anything.
I checked it further, found device_type = 7 and hence assume its the VSS backup updating backupset table in msdb database (Not sure if the actual DB backups are taken or not..). Not familiar with VM or VSS backups... Investigating further....
Anyone knows, how to confirm if the actual backup is taken by VSS... Does it update backupset table..? Thanks.

What is "VSS"? Last time I heard that abbreviation, is was for "Visual Source Safe".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Jeff Moden (1/5/2015)
What is "VSS"? Last time I heard that abbreviation, is was for "Visual Source Safe".

Volume Shadow (Copy) Service

It's what's used to take 'snapshot'-type backups of SQL databases. The service communicates with SQL to quiesce the IOs, then 'snaps' the files to the backup.

If it's a backup that SQL knows about, the msdb tables are updated.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 8

Volume Shadow (Copy) Service
It's what's used to take 'snapshot'-type backups of SQL databases. The service communicates with SQL to quiesce the IOs, then 'snaps' the files to the backup.
If it's a backup that SQL knows about, the msdb tables are updated.

I believe that is how it's done. The backups are taken at around 1 AM everyday.

Not getting any idea about where the backup timing is set and how it's triggered. The service keep running anyway.

Thanks.