2 Backup solutions running on sql box

there are two backup solutions in-place for the db's on a 2008 sql server.

there is one provided by commvault and one using standard management studio maintenance plans.

when I run a query that provides the backups for the past week. it documents the backups created by both backup applications.  i would assume that both of their backup sets are seen as one far as sql is concerned.

if commvault creates a full backup  on Tuesday at 7    and smss creates a full backup at Wednesday at 7.

any restore of a differential, regardless of which application created it would run off of the Wednesday at 7 full backup if the restore point was chosen to be later than Wednesday the 7th.   is this thought process correct?

what is a good strategy to use when trying to mix up two backup applications....
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PadawanDBAOperational DBACommented:
Especially with differentials, yes the thought process is correct.  Differentials are as of the point in time of the most recent full backup.  The reason for this is part of the process of a full backup is resetting the differential bitmap, which keeps track of all modified extents since the last full backup.  Differentials use this bitmap to identify what extents need to be backed up - and consequently how they maintain a smaller size than full backups.  You'll also notice that if you take a full backup and then only take differentials, the size of the differential backup will increase to roughly the equivalent size of a full backup.  Personally, I think the transaction log backups are going to be the part that causes the most pain, honestly, as you would have to restore those in a linear way from both sets of backups.  For example:

<<<last full backup 11pm previous day>>>
CommVault            - 12am
SQL Server Native - 2am
CommVault            - 4am
SQL Server Native - 6am
CommVault            - 8am

You want to restore to 3am, you will have to restore:

<<<last full backup 11pm previous day>>>
CommVault            - 12am
SQL Server Native - 2am
CommVault            - 4am <point in time>

And that is assuming CommVault's transaction log backups aren't wrapped with any weird metadata that prevents SQL Server from treating them as native transaction log backups.  In which case it would get even more convoluted - maybe even impossible.  Even in the best case, it's two places to get your backups from and that's just a pain in the rear.

To your question of mixing two backup applications.  In the absence of a SQL Server specific backup solution (I don't trust those backup exec/DPM/etc. applications of the world), I rely on native backups to backup to a local drive.  Then I backup the local drive with whatever 3rd party backup utility is deployed in the environment.  That way, recent restores can be done from local storage, but if I need backups that are older than the short term retention on local storage, it's still one place to get them and one way to restore them.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jamesmetcalf74Author Commented:
This is probably a dumb question, but do commvault and smss use the same engine to backup the databases?
again, I would assume they do since both of their backups show up in the history from the below query

   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
   CASE msdb..backupset.type  
       WHEN 'D' THEN 'Database'  
       WHEN 'L' THEN 'Log'  
   END AS backup_type,  
   msdb.dbo.backupset.name AS backupset_name,
FROM   msdb.dbo.backupmediafamily  
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id =

WHERE  (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >=

GETDATE() - 7)  
PadawanDBAOperational DBACommented:
3rd party applications generally leverage a set of APIs exposed by SQL Server.  I am not sure of the specifications on this, but I'm pretty sure that is what CommVault would be leveraging.  It allows 3rd party applications to register the backups (as you are seeing from your query) and free up VLFs for reuse and all that jazz.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Why do you need to use 2 solutions for SQL Server database backups? You can see the mess that is for restore (taking some backups from here plus more from there and then mix them up for restore).

CommVault is payed? And what's the advantage from using CommVault?

So, the answer for the what is a good strategy to use when trying to mix up two backup applications.... is don't use that strategy. Isn't good.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.