chris.miller1 (6/26/2013)
Here goes my first post in here...I have recently slipped into the "Accidental DBA" role and my first task is ensuring that the SQL Server is backed up properly and that we can restore successfully when (not if!) the time comes.Now after digging around the system views and in particular the dbo.Backupset table it has become apparent that one of our databases is being backed up every day with the "is_snapshot" flag set to 1. This DB is our crucial live system which is in FULL recovery and in addition to the daily backup has hourly Transaction log backups.
My questions are as follows:
a) Is the primary backup really a snapshot - table suggests it is.
b) This is likely carried out by a third party tool - there are NO SQL maintenance plans at all! Is this common...I suspect there is a well fought battle somewhere in this area between network admins and DBAs.
c) If I wanted to restore this system to say 13:05:06 just before a major error will I be able to do this in the normal FULL > Logs > Tail Log restore process?
I'm cautious of SQL being backed up by third-party snapshots and would love to hear some thoughts in general but in particular your answers to c) above!
Thanks
If you read the following link http://msdn.microsoft.com/en-us/library/ms189548(v=sql.90).aspx, you'll notice the primary backup is
a) more a file-snapshot and not a SQL database-snapshot and
b) by a third-party tool.
As for c) I guess you should restore the FULL (and DIFF, if applicable) backups using the third party tools and then continue the restore using SQL for the transaction (and TAIL) log. But the third party tool must have the logic available to leave the database in NO RECOVERY, because else a LOG restore will not possible. Only with testing such a restore (to a different SQL instance) will give you insight in the possibilities.
As a DBA I prefer to be in full control of the backups of the databases because I will also be held responsible in case of emergency. I prefer to set up my own maintenance plan (look at http://ola.hallengren.com/) and test a restore action, including point-in-time recovery. I think it depends of the current third party tool used, if this backups will interfere with your own maintenance plan. Only testing will give you the answer. When it does interfere, you'll need to discuss with the OS administrators to disable this third-party tool.