none
ntbackup causes SQL Server backups

    Question

  • Hello,

    Can anyone explain to me what is going on...?

    I have a nightly backup of key files on a production server, which - among other things - hosts two SQL server instances. The backup is scheduled and uses ntbackup.exe as the backup engine. SQL Server data directories are not included in the ntbackup script.

    For a reason I don't understand, and can't find documented anywhere, every time the backup starts, I find in the event log an entry for each database on each SQL Server instance suggesting that the database has been backed up to a virtual device:

    Event Type: Information
    Event Source: MSSQL$SQLEXPRESS
    Event Category: (6)
    Event ID: 18264
    Date:  9/06/2009
    Time:  2:31:49 AM
    User:  NT AUTHORITY\SYSTEM
    Computer: WSAPP2019
    Description:
    Database backed up. Database: master, creation date(time): 2009/05/09(10:54:47), pages dumped: 1, first LSN: 8736:288:37, last LSN: 8736:472:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{FCE1C4D7-09F1-44CB-80EB-77507A4B59BF}1'}). This is an informational message only. No user action is required.

    Can anyone tell me:

    * Why does ntbackup trigger an SQL server backup (if indeed it does)?
    * What is the linkage between ntbackup and SQL server backups - I haven't seen any ntbackup options that suggest ntbackup could in any way trigger an SQL server backup
    * What is the virtual device that has been targeted? How can I resolve the GUID to an actual target? Note that there are no entries in sys.sysdevices.

    Although this doesn't seem to be causing any issues per se (that I know of), I would like to understand what is happening and why.

    Thanks,

    Nick

     

    Tuesday, June 09, 2009 12:36 AM

All replies

  • External backup products use SQLVDI (Virtual Device Interface) to actually perform the backups which are just SQL Native Backups to an external Virtual Device.  NTBackup, NetBackup, BackupExec, all use SQLVDI to backup SQL Server Instances.  The directories for SQL don't have to be included in the backup configuration.  You have the SQL Instance configured in the Backup Configuration which is why you have SQLVDI backups occuring.


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



    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    Tuesday, June 09, 2009 2:48 AM
    Moderator
  • But where are these backups going? They couldn't be going to the backup file generated by ntbackup, as it is not big enough. The GUID gives no clue as to where the virtual device is, and as I say, there don't seem to be any devices defined.

    Also, what do you mean by "You have the SQL Instance configured in the Backup Configuration"?

    Thanks,

    Nick
    Tuesday, June 09, 2009 5:35 AM
  • My guess is that you get rid of these if you just stop the "SQL Server VSS Writer" service.
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Tuesday, June 09, 2009 6:57 AM
    Moderator
  • That's a bit indirect...
    Tuesday, June 09, 2009 7:43 AM
  • Hmm, I don't understand what you mean by "indirect". That service is what communicates to SQL Server that some app is using VDI to perform some type of backup at the OS level. If you want to make SQL Server oblivious of that fact, you just stop that service...


    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Tuesday, June 09, 2009 7:50 AM
    Moderator
  • Watch what time the backups are being performed and then at the time tomorrow, run a SQL Trace in Profiler that include the HostName and ClientPRocessID columns in the results.  When the backup events occur in the Trace you will know what server, and what processID are doing the backup.  Login to that server, open Task Manager and add the PID to the view (View->Select Columns) and then see what Process is running the backup.  Someone has configured a VDI based application to perform backups of your SQL Server.  I don't know why you wouldn't want to have backups of your databases being taken, but this is how you can figure out what is taking them.

    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    Tuesday, June 09, 2009 11:44 AM
    Moderator
  • This is how SQL backups work.  This is normal and expected behavior.
    Tuesday, June 09, 2009 3:18 PM
    Moderator
  • That sounds useful, but I don't think will work with my edition (SQL Server Express - no profiler).

    Having said that, there is no mystery here - I am completely in control of this server. The backup events coincide exactly with a scheduled ntbackup job - this is quite clear.

    What is not clear, however, is why ntbackup is initiating SQL server backups, when I haven't asked it to. Similarly, since I haven't asked ntbackup to do this, it is not clear to me how I should configure it, nor where the SQL backup is landing (since the backup file generated by the ntbackup job is not big enough).

    Why I don't want ntbackup to do this? I have a separate SQL server database backup regime, which I also control.

    At the end of the day, my goal is to be in control of what happens on my server and to understand it.

    Tuesday, June 09, 2009 10:40 PM
  • Nick,

    Go grab the free Profiler for SQL Express that was written by a member of the community:

    sqlprofiler - Profiler for Microsoft SQL Server 2005/2008 Express ...


    It can be used to accomplish this task.  The only thing I can tell you is that you have something configured in ntbackup that is causing it to do this backup.  I've never used it so I can't say exactly what.  Like Tibor said, if you want it to not happen, disable the SQL Server VSS Writer service and it will stop it from happening.

    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    Tuesday, June 09, 2009 11:01 PM
    Moderator
  • What version of windows is this happening on?
    Tuesday, June 09, 2009 11:50 PM
  • Nick,

    It is Windows Server 2003 R2.
    Wednesday, June 10, 2009 1:05 AM
  • Jonathon,

    Thanks for that tip - I wasn't aware that there was a profiler for Express - great news.

    I'm a little bit reluctant to disable the VSS writer service - I'd much rather first understand what is going on.

    Cheers,

    Nick
    Wednesday, June 10, 2009 1:05 AM
  • It seems that this is a 'feature' of ntbackup.  It does a sql backup based on the volume you select.  I can't find any way to disable this feature from ntbackup.  Maybe a support call is in order?

    http://support.microsoft.com/kb/903643
    http://support.microsoft.com/kb/828481

    • Marked as answer by Nick Tompson Wednesday, June 10, 2009 3:56 AM
    • Unmarked as answer by Nick Tompson Wednesday, June 10, 2009 3:57 AM
    Wednesday, June 10, 2009 2:28 AM
  • Nick - I agree - this is crazy stuff. Talk about a "side effect"...

    I don't mind that ntbackup is able to trigger SQL server backups - great feature. But that it is not configurable, and doesn't seem to be clearly documented is not really good enough.

    And I still can't figure out where the SQL database backups are actually landing... I've run the restore ntbackup Wizard and confirmed that there is no trace of them in the ntbackup .bak files.

    Microsoft... what do you think? Surely this deserves some attention?

    Nick
    Wednesday, June 10, 2009 3:55 AM
  • Why don't you just disable that service, as already suggested here. You don't want its help, then don't use it:

    http://msdn.microsoft.com/en-us/library/ms175536.aspx
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Wednesday, June 10, 2009 4:48 AM
    Moderator
  • Is master the only database that's being backed up?  from what I understand VSS will backup the .mdf and .ldf so they're in a consistent state on disk.  You should be able to find this in your NT backup.  Is it possible your master database is selected in your nt file backup? this would at least make sense.  try this in SSMS to see where your master database files are:

    select * from sys.master_files
    where db_name(database_id) = 'master'
    Wednesday, June 10, 2009 1:32 PM
  • Nick,

    No - it is backing up every single database on both instances (I have two) of SQL Server express. All of the data files (.mdf and .ldf) are in standard locations (ie c:\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\Data) and these are most definitely not included in the ntbackup script.

    It is all very strange...
    Wednesday, June 10, 2009 11:52 PM
  • Tibor,

    This is hardly the point - as I've said a few times, I want to understand what is going on here, rather than just disabling a service to achieve a change in the symptoms.

    There are some simple questions here that I don't seem to be able to find an answer to:

    * Where is it documented that ntbackup.exe is intended to also perform SQL Server backups (apart from an obscure KB article)?
    * How does a user configure ntbackup.exe to control this behaviour?
    * Where do the SQL server backups go?
    * Why, oh why, is this a good idea?

    Nick
    Wednesday, June 10, 2009 11:55 PM
  • Sorry, I was just trying to help. Perhaps you will get more help in a Windows forum, considering ntbackup is a program that ship with Windows?
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Thursday, June 11, 2009 6:54 AM
    Moderator
  • No worries Tibor - sounds like a good plan.

    Nick
    Thursday, June 11, 2009 11:54 PM
  • Are you sure you do not have a SQL maintenance plan which is causing this backup to happen at the same time as the NTBackup?

    The message you posted is only backing up the master database, which is tiny, 1 page.
    Friday, June 12, 2009 2:06 PM
    Moderator
  • What version of SQL Server are you running?  Please post the results of SELECT @@VERSION.

    Friday, June 12, 2009 2:07 PM
    Moderator
  • Tom,

    SQL Server 2005 Express

    SELECT @@VERSION:
    Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Express Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

    Express doesn't support maintenance plans, so I am quite confident that I don't have any defined.

    Understood that I only posted the master database event - there is one of these events in the log for every defined database on each instance (2 of, both SQL Server 2005 Express).

    All very strange to me...
    Tuesday, June 16, 2009 1:46 AM
  • Tom, I was also seeing this issue with SQL 2005 Express. 
    I also believe the issue is caused by the "SQL Server VSS Writer" service as Tibork above highlighted.  What he didn't go on to state was that 2003 server has it's own version of MSDE Writer to use of VSS backups. 

    By default, SQL writer is not enabled when SQL Server 2005 is installed. In this configuration, the MSDE writer acts as the default writer for all SQL Server instances on the system, including SQL Server 2005 instances. Before enabling the SQL writer, the MSDE writer has to be configured so that it ignores SQL Server 2005 instances. This is done by setting the following registry key:

    Key: HKLM\SYSTEM\CurrentControlSet\Services\VSS\Settings

    Value: “MSDEVersionChecking” DWORD

    If you do not wish to see the erorrs in the event log simply stop the "SQL Server VSS Writer" service and the errors will go away.  Otherwise use this document to first disable the MSDE writer from enumerating SQL Server 2005 databases.  Then you can safely use the above service.

    download.microsoft.com/download/4/7/a/47a548b9.../SQLWriter.doc
    Monday, February 01, 2010 1:53 PM