• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 63
  • Last Modified:

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.

example.  
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?

also,
what is a good strategy to use when trying to mix up two backup applications....
0
jamesmetcalf74
Asked:
jamesmetcalf74
  • 2
1 Solution
 
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.
0
 
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

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  
WHERE  (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >=

GETDATE() - 7)  
ORDER BY  
   msdb.dbo.backupset.database_name,
   msdb.dbo.backupset.backup_finish_date
0
 
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.
0
 
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now