Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Wednesday, May 29, 2013 7:15 PM
I keep seeing backups type=VIRTUAL_DEVICE in the sql server logs. How do I determine what is triggering these backups and where they are going?
Thursday, May 30, 2013 5:47 PM ✅Answered | 2 votes
>>We have VMware snapshots of the actual server which get's sent to DR
There's your answer. The VMware snapshot is using VSS to quiesce the SQL IO so it can take an application-consistent server backup. See, eg
This is the correct and only supported way to take a system backup of a SQL Server VM. See:
"SQL Server supports virtualization-aware backup solutions that use VSS (volume snapshots). For example, SQL Server supports Hyper-V backup.
Virtual machine snapshots that do not use VSS volume snapshots are not supported by SQL Server. Any snapshot technology that does a behind-the-scenes save of a VM’s point-in-time memory, disk, and device state without interacting with applications on the guest using VSS may leave SQL Server in an inconsistent state."
http://support.microsoft.com/?id=956893
David
David http://blogs.msdn.com/b/dbrowne/
Wednesday, May 29, 2013 7:19 PM | 1 vote
Hi,
IBM Tivoli SQL Agent backups show that same message.
Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu
Wednesday, May 29, 2013 7:23 PM
We are not using that, How can I determine what's triggering it?
Wednesday, May 29, 2013 7:30 PM | 1 vote
Hi,
Try the following script from SSMS (on msdb database), then look for the entry whose timestamp matches your
SQL log entries.
USE msdbSELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.expiration_date,
msdb.dbo.backupset.user_name,
CASE msdb..backupset.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
END AS backup_type,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name,
msdb.dbo.backupset.description
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)
ORDER BY
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date
Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu
Wednesday, May 29, 2013 7:56 PM
well , I ran a trace and it shows it's microsoft sql server vss writer but how can I tell which hostname is triggering it along with the application name
Wednesday, May 29, 2013 8:41 PM
You would need to add the hostname and application name to the fields you trace.
However, the easiest way is to look in the Windows event log. It almost always logs backup information.
Thursday, May 30, 2013 12:25 PM | 1 vote
Dear Coder
type=VIRTUAL_DEVICE in sql server error log means some Third party backup tool ,not just Trivoli,it can be any third party tool Veritas,sql safe etc is taking backup of your database and backup is being taken on tape its good method to keep data (store huge data)
Note the time when you are getting this message ,contact your backup/IT/storage team reg the same I am sure they will say that they are backing up the database.
Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers
Thursday, May 30, 2013 12:48 PM
I don't see where to add hostname and windows logs just say DB was backed up and has the servername where the databases are located. I know we have backupexec onanother server but the one person responsible for it says it 's not backing up the DB
Thursday, May 30, 2013 1:00 PM | 1 vote
Try the below whether it would help you.
--Last Backup information ; WITH bus AS ( SELECT database_name, machine_name, backup_finish_date, media_set_id, type,msdb.dbo.backupset.software_vendor_id, msdb.dbo.backupset.user_name, row_number () OVER(PARTITION BY database_name ORDER BY backup_finish_date DESC) AS rowno FROM msdb.dbo.backupset)SELECT bus.machine_name AS ServerName, sdb.Name AS DatabaseName, COALESCE(convert(varchar(23), bus.backup_finish_date), 'No backup') AS BackupTime , CASE bus.type WHEN 'D' THEN 'Database' WHEN 'L' THEN 'Log File' WHEN 'I' THEN 'Differential' END AS BackupType,software_vendor_id,user_name,busf.physical_device_name AS LocationFROM sys.databases sdbLEFT OUTER JOIN bus ON bus.database_name = sdb.name AND rowno <= 2LEFT OUTER JOIN msdb.dbo.backupmediafamily busf ON busf.media_set_id = bus.media_set_idWHERE database_id > 4ORDER BY bus.machine_name
Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
Thursday, May 30, 2013 1:02 PM
VIrtual_Device itself says that backup is done by a thirb party tool, please make sure
Ramesh Babu Vavilla MCTS,MSBI
Thursday, May 30, 2013 1:12 PM
There must be something running on the server. databases are on "serverA" and hostname says "serverA"
Thursday, May 30, 2013 1:32 PM
- System - Provider [ Name] MSSQLSERVER
- EventID 18264 [ Qualifiers] 16384
Level 4
Task 6
Keywords 0x80000000000000
- TimeCreated [ SystemTime] 2013-05-30T13:08:15.000000000Z
EventRecordID 178568
Channel Application
Computer ServerA
- Security [ UserID] S-1-5-18 - EventData TraQ6
2013/03/28
10:02:27
9705
31102:441:1
31102:443:1
1
FILE=1, TYPE=VIRTUAL_DEVICE: {'{A0DFFAFD-43AB-4636-812C-3B68145EC678}19'}
584700000A0000000700000053004F00530051004C0031000000070000006D00610073007400650072000000
Thursday, May 30, 2013 2:29 PM | 1 vote
Did you find any VSS errors or any mesage realted to VSS,
chek whether snapshot backup is not configured.
read this links
Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers
Thursday, May 30, 2013 3:00 PM
I just see I/O is frozen on Database messages. No user action is required
Thursday, May 30, 2013 3:10 PM | 1 vote
The prev post given by me has details regarding that..also below duplicate link is there for I/O frozen
Use both link to help urself..reply if any issue
Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers
Thursday, May 30, 2013 5:39 PM
Those links are useful in explaining VDI and VSS and I appreciate it but I'm still searching for what is making these vdi calls. It seems I need a special profiler template to capture what is making these VDI calls? any ideas? from what I understand , backupexec runs nightly. We have VMware snapshots of the actual server which get's sent to DR site so something is causing these backups to run and code posted above works great but is not telling me what is making these calls.