none
trying to find out what's triggering backups and where they are going to

    Question

  • 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?
    Wednesday, May 29, 2013 7:15 PM

Answers

  • >>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

    http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=1007696

    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/


    Thursday, May 30, 2013 5:47 PM

All replies

  • Hi,

    IBM Tivoli SQL Agent backups show that same message.


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    • Proposed as answer by vr.babu Thursday, May 30, 2013 1:01 PM
    Wednesday, May 29, 2013 7:19 PM
  • We are not using that, How can I determine what's triggering it?
    Wednesday, May 29, 2013 7:23 PM
  • Hi,

    Try the following script from SSMS (on msdb database), then look for the entry whose timestamp matches your

    SQL log entries.

    USE msdb

    SELECT 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:30 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 7:56 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.

    Wednesday, May 29, 2013 8:41 PM
    Moderator
  • 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:25 PM
    Moderator
  • 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 12:48 PM
  • 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 Location
    FROM sys.databases sdb
    LEFT OUTER JOIN bus ON bus.database_name = sdb.name AND rowno <= 2
    LEFT OUTER JOIN msdb.dbo.backupmediafamily busf ON busf.media_set_id = bus.media_set_id
    WHERE database_id > 4
    ORDER 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:00 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:02 PM
  • There must be something running on the server. databases are on "serverA" and hostname says "serverA"
    Thursday, May 30, 2013 1:12 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 1:32 PM
  • Did you find any VSS errors or any mesage realted to VSS,

    chek whether snapshot backup is not configured.

    read this links

    http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/bd1480cc-7f39-49d0-be8e-02c0a3b543f2

    http://blogs.msdn.com/b/sqlserverfaq/archive/2009/04/28/informational-shedding-light-on-vss-vdi-backups-in-sql-server.aspx


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Thursday, May 30, 2013 2:29 PM
    Moderator
  • I just see I/O is frozen on Database messages. No user action is required
    Thursday, May 30, 2013 3:00 PM
  • The prev post given by me has details regarding that..also below duplicate link is there for I/O frozen

    http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/884f74e2-829b-4371-843d-13381511f040

    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 3:10 PM
    Moderator
  • 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.
    Thursday, May 30, 2013 5:39 PM
  • >>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

    http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=1007696

    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/


    Thursday, May 30, 2013 5:47 PM