Backups occuring twice at night, Native SQL Job backups scheduled for 8:00 PM

Question

Backups occuring twice at night, Native SQL Job backups scheduled for 8:00 PM

Welsh Corgi

SSC Guru

Points: 116520
More actions
April 3, 2014 at 11:55 am

#306054

I encountered a weird situation with my Nightly Backups (Full).
I have a job that executes a maintenance plan and it is scheduled to backup at 8:00 PM but I'm also getting backups at 10 PM.
I can't find a Job that does backups at 10 PM.
There is another maintenance plan to backup the same databases but the Job is disabled and it was scheduled to backup at 11:00 PM.
There is a 3rd party utility backing up the database (small) every 15 seconds but I exclude them by specifying physical_device_name to exclude Hex names.
I'm stumped on this one.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jim_K SSCrazy Points: 2418 More actions · Answer 1

Hmm,

You wouldn't happen to be in a VM environment? I know there are some tools out there, such as Veeam, that will snapshot the database and record the fact in the SQL backup tables. IIRC, there is some evidence in the windows and sql log where you will see VSS spun up and/or the database reporting itself as frozen.

Jim

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

San snapshots can also cause an extra backup entry in your backuphistory. Are you doing SAN snapshots by chance?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 3

Run this and see if you can find the source of that backup

SELECT bs.database_name AS DB_Name

, bs.type AS BackupType

, bs.backup_start_date AS Backup_StartDate

, bs.backup_finish_date AS Backup_EndDate

, bs.description AS BackupDescription

, bs.[user_name] AS UserNameTakingBackup

, bs.server_name AS ServerName

, bs.machine_name AS MachineName

, bmf.physical_device_name AS DeviceName

FROM msdb.dbo.backupset bs JOIN msdb.dbo.backupmediafamily bmf

ON bs.media_set_id = bmf.media_set_id

--WHERE bs.database_name = 'YourDBName'

order by bs.backup_start_date, bs.database_name desc

--

SQLBuddy

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

They are doing the COMMVault Backups.

They are also doing snapshot of the Server every hour.

I failed to mention that this is a Great Plains Server which was managed by someone else in Finance who is no longer here.

The naming convention of the Backup Directory are not consistent with what I use.

Thanks everyone for you input. 🙂

Thanks for the script. I was using the following script to return backup history.

SELECT

s.database_name,

CASE s.type

WHEN 'D' THEN 'Full'

WHEN 'I' THEN 'Differential'

WHEN 'L' THEN 'Transaction Log'

END AS BackupType,

CAST(DATEDIFF(second, s.backup_start_date,

s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,

s.backup_start_date,

s.backup_finish_date,

CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,

CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,

m.physical_device_name,

CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,

s.server_name,

s.recovery_model

FROM msdb.dbo.backupset s

INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id

--WHERE s.database_name = DB_NAME() -- Remove this line for all the database

--WHERE s.database_name = 'DataArchiveFL '

--WHERE physical_device_name LIKE 'E:\%'

--AND backup_start_date > '2014-03-29'

--AND backup_start_date < '2014-04-03'

--ORDER BY backup_start_date DESC, backup_finish_date

ORDER BY database_name, backup_start_date DESC

--ORDER BY s.database_name,backup_start_date DESC, backup_finish_date

GO

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 5

SQLRNNR (4/3/2014)
San snapshots can also cause an extra backup entry in your backuphistory. Are you doing SAN snapshots by chance?

I have to check with the Infrastructure Manager.

Thanks.:-)

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

Jim_K (4/3/2014)
Hmm,
You wouldn't happen to be in a VM environment? I know there are some tools out there, such as Veeam, that will snapshot the database and record the fact in the SQL backup tables. IIRC, there is some evidence in the windows and sql log where you will see VSS spun up and/or the database reporting itself as frozen.
Jim

Yes it is a VM Backup. It is only occurring on the Great Plains Server.

Thanks.:-)

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/