1

I have done backup plan to backup 1 DB . When look in the log i can see that the system has taken backup of all databases (master , reportserver ...etc) . However on the drive where data are backed up , i can see only my DB for which i have done the bakup plan .

Questions:

1) why i am seeing in the log that there were many DB backed up .

2) If 1) is true where are these backups ?

Any comment will help.

enter image description here

enter image description here

enter image description here

enter image description here

9
  • have you checked out through select * from msdb.dbo.backupset; Nov 9 '15 at 10:07
  • I checked it is shown that the backups were taken . But these backups should not be created as i did not plan them . Do you know why are they careated ?
    – Java Main
    Nov 9 '15 at 10:22
  • 1. Check for SQL Agent jobs - Are there any additional backup jobs u see, 2. Look for maint'ce plans-- Are there any additional plans u see for those additional DB's being backed up? 3. Read the complete log and check if it says backup is being done to a virtual device?
    – KASQLDBA
    Nov 9 '15 at 10:27
  • @JavaMain, Are you quite sure you are looking for the file in the right folder? Nov 9 '15 at 10:29
  • it's duplicate question dba.stackexchange.com/questions/31776/… Nov 9 '15 at 10:32
1

The problem was that i was doing a classic backup using windows server utility (nothing to have with DB backup) for some folder . I was doing backup for only 1 folder in the root of the C:\ drive .

But somehow when this backup starts it freezes the I/O on each database, then it resumes the I/O of all databases and finally all DB are backed up .

enter image description here

3
  • The freezing comes from VSS snapshot that server backup is doing.
    – vonPryz
    Nov 10 '15 at 11:22
  • But why these backups i did not plan them ?
    – Java Main
    Nov 10 '15 at 11:58
  • 1
    The Windows server backup will back up all files on your system, unless you configure excludes. It recognizes Sql Server's files and will use VSS snapshot to get a consistent state for backup. Solution: ditch the maintenance plan, use Ola's scripts and config the Win server backup to exclude the DB data and log dirs.
    – vonPryz
    Nov 10 '15 at 12:46
0

Probable reasons i might think of, why you see those DB's being backed up are:

  1. You have an SQL Agent job scheduled somewhere to take those backups:

  2. Either a Maintenance plan exist which is taking those backups.

  3. May be there are AD-HOC backups being taken via SSMS:

  4. This may be the reason you might be seeing those DB's getting backed up due to VSS (Volume Shadow copy service).

In order to check this, please run the query below from here to find how are you're DB's getting backed up.This will give you the application name which will help the cause on how these backups are occurring.

SET NOCOUNT ON;
--this version reads all trace files
declare @path  nvarchar(100)
set @path = (select top 1 [path] from sys.traces where is_default = 1)
set @path = (select reverse(right(reverse(@path), (Len(@path) - (PATIndex('%[_]%', reverse(@path)))))) + '.trc')


SELECT  STE.name AS EventClassName,
       ST.StartTime ,
        ST.LoginName ,
        ST.HostName ,
        ST.ApplicationName ,
        ST.TextData
FROM    sys.fn_trace_gettable((@path), DEFAULT) ST
INNER JOIN sys.trace_events STE ON ST.EventClass = STE.trace_event_id
WHERE   TextData LIKE '%backup%' AND SPID <> @@SPID
ORDER BY StartTime DESC;
9
  • Thank you for your support . I checked all points you mentioned but nothing is found corresponding. The request once executed returns : C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\log_327.trc' is not a recognizable trace file.
    – Java Main
    Nov 9 '15 at 11:19
  • I added 1 more picture
    – Java Main
    Nov 9 '15 at 11:28
  • Is default trace enabled or disabled, run SELECT * FROM sys.configurations WHERE configuration_id= 1568; ?
    – KASQLDBA
    Nov 9 '15 at 11:29
  • Name =default trace enabled value =1
    – Java Main
    Nov 9 '15 at 11:36
  • The screenshot where you showed the backup details in sql server logs, should you expand further should indicate the device information where those databases are backed up. Read the log completely for all those DB's and check if that mentions TYPE='VIRTUAL_DEVICE' ?
    – KASQLDBA
    Nov 9 '15 at 12:32

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.