0

We have a SQL Server which started to take backups by itself every now and then. It started for a few days ago.

When it fails an error is logged saying that source is "Microsoft SQL Server Native Client 10.0" It also says:

"SQLSTATE: 42000, Native Error: 3201 Error state: 7, Severity: 16 Error message: Cannot open backup device '{94C30FE5-1C62-45C7-8E28-4F67BCF89210}682'."

How can this happen? Can I find out what part of the code which does this?

It is not a part of any jobs or similar, the time for the backup is different from day to day, it does not try to take a backup every day but most of them. If it fails it retries a lot of times and after a veil its stops.

Edit: The first clue to find out why these backups were taken I found from Kevin3NF advise, were I could confirm that it was the SQL Server VSS Writer which were taken the backups. Then John Eisbrener commented that if it is VSS writer it is probably when one takes a backup of the entire OS. I then found that someone has had a similar problem: Why SQL Server Express 2012 take automatically backup in "Virtual Device" without virtual device configuration? We have now confirmed that it happens when our new 3rd party tool is taking a backup of the entire OS.

  • Can you please elaborate on how you are linking Source: Microsoft SQL Server Native Client 10.0 this with backups – TheGameiswar Apr 3 '17 at 11:57
  • Looks like a VDI or VSS backup from the use of a guid which means it isn't SQL Server doing it, but another application which doesn't even need to reside on the server. – Sean Gallardy Apr 3 '17 at 12:26
  • I just found that it is the VVS Writer which is logged at the application. – Erik Dahlen Apr 3 '17 at 12:28
  • I suspect you need to talk to your VMWare/Hyper-V Admin. Chances are good this is a OS-level backup which fires off the VSS Writer. I would suggest only backing up OS-level drives via your VM backup solution and then let SQL Server manage backups of the databases themselves. – John Eisbrener Apr 3 '17 at 13:23
  • After talking to my colleague it seems to be the reason. (They implemented a new way to take backups on the OS-level.) Thanks for the advice :) – Erik Dahlen Apr 3 '17 at 13:40
2

Most likely it is a 3rd party backup solution such as NetBackup that your sysadmins have pointed to your instance.

Try reading the default trace to see if the application name column gives you the specifics:

-- read all available traces.
-- including current
DECLARE @current VARCHAR(500);
DECLARE @start VARCHAR(500);
DECLARE @indx INT;

SELECT @current = path
FROM sys.traces
WHERE is_default = 1;

--remove the rollover number from the filename 
--(i.e., log_157.trc becomes log.trc)
SET @current = REVERSE(@current)
SELECT @indx = PATINDEX('%\%', @current)
SET @current = REVERSE(@current)
SET @start = LEFT(@current, LEN(@current) - @indx) + '\log.trc';

-- CHANGE FILTER AS NEEDED
SELECT 
    TextData,
    ApplicationName, 
    StartTime,
    CASE EventClass
        WHEN 46 THEN 'Object:Created'
        WHEN 47 THEN 'Object:Deleted'
        WHEN 164 THEN 'Object:Altered'
        ELSE 'Other'
    END as EventClass, 
    DatabaseName, 
    ObjectName,
    ObjectType, -- https://msdn.microsoft.com/en-us/library/ms180953.aspx
    HostName, 
    LoginName
FROM
    ::fn_trace_gettable(@start, DEFAULT)
WHERE 1=1
    AND DatabaseID <> 2 --eliminate tempdb
    AND DatabaseName = 'model'--Like '%master%'
    AND Textdata like 'Backup Database%'
ORDER BY StartTime DESC
0

select * from msdb.dbo.backupset - this contains user_name and machine_name fields which will help you find who or what the backup is being taken from.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.