August 6, 2015 at 9:11 am
We are moving some systems to a service provider that uses CommVault Simpana 10 SP11 for VM backups (VMWare). They will also be using the same for SQL Server backups (full & tlogs) once we let them know the schedules. The VM backups are scheduled nightly at 8:00 PM. On my SQL Server 2012 SP2 VM I have noticed that something kicks off a full backup on all system and user databases at that time. This is the query I used to see the backup history.
SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.expiration_date,
CASE msdb..backupset.type WHEN 'D' THEN 'Database' WHEN 'L' THEN 'Log' END AS backup_type,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name,
msdb.dbo.backupset.description
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
ORDER BY msdb.dbo.backupset.database_name, msdb.dbo.backupset.backup_finish_date;
The vendor says that Simpana uses the VMWare APIs to do the VM backups. Does anyone know if there is something that causes a full backup as part of the VM backups? I would like to disable it since we will be scheduling fulls and tlogs soon anyway. We're not doing differentials, so I'm not concerned about breaking the chain, but it just seems unnecessary.
August 6, 2015 at 9:34 am
If you know when the backups are kicked off, then you know when to look and see what application/host is running the BACKUP DATABASE command. Look with Activity Monitor or write a query with sys.dm_exec_requests, sys.dm_exec_connections, and sys.dm_exec_sessions.
Could you clarify this statement please?
We're not doing differentials, so I'm not concerned about breaking the chain
Differentials have no bearing on the transaction log chain (if that's what you meant). They are specific to the most recent full backup, but just like full backups (except the very first full taken after setting the database to full or bulk-logged recovery mode), differential backups have no effect on the transaction log chain.
-Eddie
Eddie Wuerch
MCM: SQL
August 6, 2015 at 12:24 pm
Thanks for your quick reply, Eddie. It happens everyday at the same time the VM backups kick off, so I'm 99% percent sure that's what is causing the backups. I also know that I haven't scheduled any backups, and the sysadmin on their side said he hasn't scheduled anything. Here are the results from the query above on the master database.
Server database_name backup_start_date backup_finish_date expiration_date backup_type backup_size logical_device_name physical_device_name backupset_name description
------ ------------- ------------------- ------------------- --------------- ----------- ----------- ------------------- ---------------------------------------- -------------- -----------
SQL1 master 2015-07-28 20:02:33 2015-07-28 20:02:44 NULL Database 3564032 NULL {E37C9CE8-EF58-4C00-89C4-1DAA42C68342}3 NULL NULL
SQL1 master 2015-07-29 20:13:14 2015-07-29 20:13:27 NULL Database 4341760 NULL {F45FCB31-2A42-4CFA-9AB0-D55C7F98FF9B}4 NULL NULL
SQL1 master 2015-07-30 20:02:28 2015-07-30 20:02:42 NULL Database 4413952 NULL {98784B2B-AB52-44EC-828C-87EE1DA58F5F}5 NULL NULL
SQL1 master 2015-07-31 20:05:56 2015-07-31 20:06:07 NULL Database 4392448 NULL {D0BBAD4F-7EFA-4E89-B8FB-918045AAD1B7}10 NULL NULL
Regarding the diffs, I meant that we aren't doing differential backups so I don't have to worry about tracking down the mystery full backup so I can restore a differential. I'm assuming the mystery backups aren't COPY_ONLY, and I have no idea where to find them.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply