DB backups not secheduled

Question

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.

have you checked out through select * from msdb.dbo.backupset; — Md Haidar Ali Khan, Nov 09 '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 09 '15 at 10:22
@JavaMain, Are you quite sure you are looking for the file in the right folder? — Md Haidar Ali Khan, Nov 09 '15 at 10:29
it's duplicate question http://dba.stackexchange.com/questions/31776/sql-server-2012-database-backup-successfully-report-but-no-backup-file — Md Haidar Ali Khan, Nov 09 '15 at 10:32
Its not a duplicate . I do not want these backups as i did not create them . — Java Main, Nov 09 '15 at 10:35
@JavaMain, can you please check the questions i had, that will help us to understand the issue little better, thanks — KASQLDBA, Nov 09 '15 at 10:39
Run this query to get backup details for where the backups are being backed up SELECT physical_device_name, backup_start_date, backup_finish_date, backup_size/1024.0 AS BackupSizeKB FROM msdb.dbo.backupset b JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id WHERE database_name = 'Dbname_here' ORDER BY backup_finish_date DESC — KASQLDBA, Nov 09 '15 at 10:45

score 1 · Answer 1 · answered Nov 10 '15 at 10:45

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 .

answered Nov 10 '15 at 10:45

Java Main

247
4
13

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

score 0 · Answer 2 · edited Apr 13 '17 at 12:42

0

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

You have an SQL Agent job scheduled somewhere to take those backups:
Either a Maintenance plan exist which is taking those backups.
May be there are AD-HOC backups being taken via SSMS:
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;

edited Apr 13 '17 at 12:42

Community

1

answered Nov 09 '15 at 10:56

KASQLDBA

7,159
6
27
53

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 09 '15 at 11:19
I added 1 more picture – Java Main Nov 09 '15 at 11:28
Is default trace enabled or disabled, run SELECT * FROM sys.configurations WHERE configuration_id= 1568; ? – KASQLDBA Nov 09 '15 at 11:29
Name =default trace enabled value =1 – Java Main Nov 09 '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 09 '15 at 12:32
Yes it is mentioned . device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{2822A6EF-2B21-4F59-CAB6-0B6BAC23A5EE}3'}) – Java Main Nov 09 '15 at 12:44
Ok, so here you go. This is the reason you see backups of other DB's. Read http://blogs.msdn.com/b/psssql/archive/2009/03/03/how-it-works-sql-server-vdi-vss-backup-resources.aspx for further details. So you need to check with system admins in case you do not want to run this windows backups. Once they disable you may proceed with native backups. Please read the link on my point 4 as well for proper explanation – KASQLDBA Nov 09 '15 at 13:32
Ok thank you for the information. Now hot get rid of these backups ? – Java Main Nov 09 '15 at 14:42
@JavaMain, get to the team who initiated this. Get the sys or server admin team look into this and get this disabled as per their understanding on why was it initially introduced and then implement native backups accordingly. – KASQLDBA Nov 09 '15 at 15:19

DB backups not secheduled

2 Answers2

Linked