June 26, 2013 at 5:13 am
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
July 1, 2013 at 1:09 am
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.
July 2, 2013 at 4:34 am
Thanks for your reply. The third party toll is Backup Exec which is "SQL Aware". Whether that means it can perform restores with the NORECOVERY command to allow subsequent log restores I'm really not sure.
So yes plenty testing ahead!
I suppose another question at this point would be if I leave Backup Exec to do its snapshot business on the database does this have any impact on a standalone maintenance plan?
If I wanted to run my own Full SQL backup to a different location and pickup the logs as they are this should be ok right? It's not like we would be duplicating backups on the logs which would be a huge mistake.
Finally if I initiate a full backup, what state does the database go into - is it offline or do I need to manually kill any connections before doing the backup?
July 2, 2013 at 5:26 am
If "Backup Exec" is really SQL aware then it should be able to do a complete point-in-time recovery, but maybe you can better ask this on a forum of the Backup Exec manufacturer. Or test it of course 😎
If you create your own maintenance plan it could (and probably will) interfere with the backups of Backup Exec unless you specify the COPY_ONLY option. Each log backup taken after a normal FULL backup has a sequence to this last FULL backup as initial starting point. When you create a backup using the COPY_ONLY option, the backup doesn't interrupt the sequence of the LOG backups.
But after restoring a COPY_ONLY backup it is still possible to continue the restore using the LOG backups. Start with the first LOG backup taken after the COPY_ONLY backup has been taken.
A backup of a database is an ONLINE operation and there's no need to kill connections or bring the database offline. Users usually won't notice anything and can continue to work, unless the server is allready under a lot of stress. Restoring a database over an existing one needs that the existing database is exclusively available for the restore procedure. No connections are allowed untill the restore is finished.
July 2, 2013 at 7:59 am
Each log backup taken after a normal FULL backup has a sequence to this last FULL backup as initial starting point.
Does this mean for the very first log backup after a full backup the Full:last_lsn >= Log:first_lsn
This is an extract for the backupset table: (on closer inspection it seems there are 2 full backups every morning one is_snapshot = true (very small file size) the other false and resembles a normal SQL backup (file size is healthy).
5am DB Backup (is_snapshot) first_lsn = 161382000005802000081 last_lsn = 161382000005812400001
6am DB backup first_lsn =161382000005827200058 last_lsn=161382000005830100001
7am 1st Log backup after full, first_lsn = 161335000002837800001 last_lsn = 161382000005923900001
Does this look ok?
When you create a backup using the COPY_ONLY option, the backup doesn't interrupt the sequence of the LOG backups
How does a full database backup interrupt the sequence? Is the 6am backup in the extract interrupting something?
I think I'm almost getting my head around this...appreciate your help! Thanks
July 4, 2013 at 3:56 am
Ok so I did so more research myself and please correct my assumptions if they are not right:
A full backup does not break log chains (only a log backup or a switch to simple recovery can do this), it will however reset the base backup which would affect any strategy which involves differential backups.
Therefore as you suggested HanShi I could use the WITH COPY_ONLY to make a non interfering full backup.
Each log backup taken after a normal FULL backup has a sequence to this last FULL backup as initial starting point
But provided my log chain is complete - even if I did do a full backup without the COPY_ONLY - surely I could still point to an older full base backup and roll forward remaining logs in the chain...even past the point of the newer full backup?
Thanks
July 4, 2013 at 4:11 am
chris.miller1 (7/4/2013)
Ok so I did so more research myself and please correct my assumptions if they are not right:A full backup does not break log chains (only a log backup or a switch to simple recovery can do this), it will however reset the base backup which would affect any strategy which involves differential backups.
Therefore as you suggested HanShi I could use the WITH COPY_ONLY to make a non interfering full backup.
Each log backup taken after a normal FULL backup has a sequence to this last FULL backup as initial starting point
But provided my log chain is complete - even if I did do a full backup without the COPY_ONLY - surely I could still point to an older full base backup and roll forward remaining logs in the chain...even past the point of the newer full backup?
Thanks
Yes, all your statements above are correct :-D. Well done!
July 4, 2013 at 4:32 am
Great! Thanks for your help!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply