Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Snapshot Backups and Transaction Logs Expand / Collapse
Author
Message
Posted Wednesday, June 26, 2013 5:13 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 23, 2014 3:10 PM
Points: 175, Visits: 165
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
Post #1467570
Posted Monday, July 1, 2013 1:09 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 10:40 AM
Points: 2,480, Visits: 3,023
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.


** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1468946
Posted Tuesday, July 2, 2013 4:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 23, 2014 3:10 PM
Points: 175, Visits: 165
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?
Post #1469383
Posted Tuesday, July 2, 2013 5:26 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 10:40 AM
Points: 2,480, Visits: 3,023

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.


** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1469408
Posted Tuesday, July 2, 2013 7:59 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 23, 2014 3:10 PM
Points: 175, Visits: 165
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
Post #1469520
Posted Thursday, July 4, 2013 3:56 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 23, 2014 3:10 PM
Points: 175, Visits: 165
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
Post #1470377
Posted Thursday, July 4, 2013 4:11 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 10:40 AM
Points: 2,480, Visits: 3,023
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 . Well done!


** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1470385
Posted Thursday, July 4, 2013 4:32 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 23, 2014 3:10 PM
Points: 175, Visits: 165
Great! Thanks for your help!
Post #1470391
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse