August 7, 2013 at 10:21 pm
Comments posted to this topic are about the item 10 Myths about Backups in SQL Server
Regards
Shashank Srivastava
MCITP - SQL SERVER 2008
INDIA
Follow me @ http://shashanksrivastavasqldba.blogspot.com/
August 8, 2013 at 2:59 am
Hi Shashank,
Thanks for sharing, some of those are very helpful points to know for those new to SQL server but I think you could expand on Myth 6 a little to help people understand the process better.
For example;
I have had to deal with a few situations from time-to-time where an inexperienced DBA has taken FULL backups of a database (to restore into a test environment) and has then deleted the backup file they created.
Of course once this has been done any subsequent DIFF backups are based off that ad-hoc FULL backup and without it are useless. In one case where we then needed to restore the database to a point in time on a Thursday we found all the DIFF backups from Monday to Wednesday were useless as a FULL backup had been taken on Monday morning and then discarded.
Fortunately we had all the T-Log backups going back to the last scheduled FULL backup and were able to use those; but the restore operation took longer than it would have done if we had been able to use the latest DIFF backup - and of course we were under pressure until we established what had happened and that we could restore the DB as required :w00t:
So whilst any ad-hoc FULL or DIFF backups won't disrupt the Log Chain any sensible DBA's wishing to take an ad-hoc backup for whatever reason should use the COPY ONLY option to avoid disrupting the established backup/restore processes.
Regards
Les
August 8, 2013 at 3:59 am
Thanks Les for your inputs. As this was just an article where I just gave them an intro about myths that prevails in SQL regarding backup thus I didn't wanted to elaborate on every myth. But let me assure you in future I will write articles in which I am going to elaborate all the myths so that they can understand it in a much better way.
Regards
Shashank Srivastava
MCITP - SQL SERVER 2008
INDIA
Follow me @ http://shashanksrivastavasqldba.blogspot.com/
August 8, 2013 at 6:34 am
Thanks for a very good article.
On myth 10 - you cannot backup a mirrored database - I think you meant to say that the myth is that you can backup a mirrored database. The fact is that you cannot.
This changes in SQL 2012 though. If you use Always On Availability Groups a secondary replica can be backed up.
http://technet.microsoft.com/en-us/library/hh245119.aspx
I wish this feature had been available in 2008 R2 as I needed to set up remote site backups for over 500 databases. I ended up log shipping all 500 databases to a remote site and used a bit of a hack to back them up. Though it's not officially supported it works. The process is..
- ensure the secondary database is in standby mode (i.e. readable)
- disable the restore job
- create a "dummy" database with the same # and types of files
- offline both databases
- copy the secondary database files over top of the dummy database files.
- bring both databases back online
- enable the restore job
- backup the "dummy" database
At this point you can backup the dummy database. If you want to be able to roll forward log backups, make sure you also save a copy of the corresponding undo file (*.tuf) with your backup.
August 8, 2013 at 7:35 am
Nice article but while reading it I could only thing about one thing.
Why do you call these Myth's?
A Myth is something that people believe is true that is not.
I do not know anybody that believes most of the things you discuss.
August 8, 2013 at 8:19 am
Nice article. One thing though. You list this:
Myth 6: Full and differential backup breaks the log chain
This is a misnomer. A full backup restarts the log chain. So if you make a one-off backup for dev/testing purposes and do not keep it in your backup/restore strategy it most certainly will break your log chain.
It was the entire reason copy only backups were created.
Joie Andrew
"Since 1982"
August 8, 2013 at 8:47 am
PHYData DBA (8/8/2013)
Nice article but while reading it I could only thing about one thing.Why do you call these Myth's?
A Myth is something that people believe is true that is not.
I do not know anybody that believes most of the things you discuss.
+ 1
Most of the Myths discussed in the article looks like they are made up for the sake of the article. I have not met with anybody (neither personally nor in community) with these kind of myths in his mind.
August 8, 2013 at 8:53 am
Joie Andrew (8/8/2013)
Nice article. One thing though. You list this:Myth 6: Full and differential backup breaks the log chain
This is a misnomer. A full backup restarts the log chain. So if you make a one-off backup for dev/testing purposes and do not keep it in your backup/restore strategy it most certainly will break your log chain.
It was the entire reason copy only backups were created.
I don't think this is true for full and transaction log backups. If I take a full backup, then some transaction backups, then another full backup, and a another set of transaction log backups, I can restore the first full backup and then all the transaction logs. I guess it breaks the chain of differential backups though?
August 8, 2013 at 9:04 am
Edited out 😎
August 8, 2013 at 9:18 am
This happens every day somewhere.... Some days it happens and the ad-hoc backup that was created has been deleted....
Backup chain totally busted at that point.
I dont think thats entirely true but I'll have to test it to see.
In the scenario you describe, restoring a differential backup taken after an adhoc full backup would not work, but restoring log backups should work just fine. Otherwise log shipping would break the first time another full backup was taken after the secondary database was created.
August 8, 2013 at 9:26 am
Hi,
Thanks for your inputs and you are right when you define what is myth. It's good that you have not met people who doesn't talk about it but I have some many people especially freshers and beginners who generally ask these questions.
Regards
Shashank Srivastava
MCITP - SQL SERVER 2008
INDIA
Follow me @ http://shashanksrivastavasqldba.blogspot.com/
August 8, 2013 at 9:27 am
PHYData DBA (8/8/2013)
stobe (8/8/2013)
Joie Andrew (8/8/2013)
Nice article. One thing though. You list this:Myth 6: Full and differential backup breaks the log chain
This is a misnomer. A full backup restarts the log chain. So if you make a one-off backup for dev/testing purposes and do not keep it in your backup/restore strategy it most certainly will break your log chain.
It was the entire reason copy only backups were created.
I don't think this is true for full and transaction log backups. If I take a full backup, then some transaction backups, then another full backup, and a another set of transaction log backups, I can restore the first full backup and then all the transaction logs. I guess it breaks the chain of differential backups though?
The Scenario that happens every day is this. The production database is being backed up by a job or some backup solution to a share local or offsite dedicated to storing backup files.
A user connects to the SQL server and runs a normal Full backup and stores this backup to a different location for restoration there, no copy of this backup is stored in the regular location.
Tran log backups continue to run all day as if no full backup had been created.
An outage occurs that requires the DBA to restore the database to a point in time after the backup that was not stored in the usual location was created. This can not be done normally with the regular restore process because the Backup chain was broken and the tran log files do not match the full backup that is in the regular location.
This happens every day somewhere.... Some days it happens and the ad-hoc backup that was created has been deleted....
Backup chain totally busted at that point.
If I read what you are stating correctly I think you are perpetuating the myth. Paul Randall did quite a few blog posts connected to this;
The example I listed in my first post fits the type of pattern you describe (ad-hoc FULL backup) but the log chain is not broken, its the DIFF backups that become useless and you just have to use all your TLog backups from the last FULL backup you have available (assuming you still have those!!). Of course if someone has switched the DB into SIMPLE recovery model for whatever reason then the log chain will definately have been broken and you get to have *that* meeting without coffee...
Regards
Les
August 8, 2013 at 9:28 am
PHYData DBA (8/8/2013)
stobe (8/8/2013)
Joie Andrew (8/8/2013)
Nice article. One thing though. You list this:Myth 6: Full and differential backup breaks the log chain
This is a misnomer. A full backup restarts the log chain. So if you make a one-off backup for dev/testing purposes and do not keep it in your backup/restore strategy it most certainly will break your log chain.
It was the entire reason copy only backups were created.
I don't think this is true for full and transaction log backups. If I take a full backup, then some transaction backups, then another full backup, and a another set of transaction log backups, I can restore the first full backup and then all the transaction logs. I guess it breaks the chain of differential backups though?
The Scenario that happens every day is this. The production database is being backed up by a job or some backup solution to a share local or offsite dedicated to storing backup files.
A user connects to the SQL server and runs a normal Full backup and stores this backup to a different location for restoration there, no copy of this backup is stored in the regular location.
Tran log backups continue to run all day as if no full backup had been created.
An outage occurs that requires the DBA to restore the database to a point in time after the backup that was not stored in the usual location was created. This can not be done normally with the regular restore process because the Backup chain was broken and the tran log files do not match the full backup that is in the regular location.
This happens every day somewhere.... Some days it happens and the ad-hoc backup that was created has been deleted....
Backup chain totally busted at that point.
Hmmm, the thread at http://www.sqlservercentral.com/Forums/Topic956447-357-1.aspx seems to contradict that.
August 8, 2013 at 9:31 am
Hi,
I would suggest you to please go by the article http://technet.microsoft.com/en-us/magazine/gg132708.aspx as it will help in understanding how backup works. A full backup is required to start the log chain, it never breaks the log chain and you need to take a full backup whenever log chain is broken so that it can be started again.
Regards
Shashank Srivastava
MCITP - SQL SERVER 2008
INDIA
Follow me @ http://shashanksrivastavasqldba.blogspot.com/
August 8, 2013 at 9:32 am
Hi,
Try it yourself on a dummy system and see the result. For more info you can read http://technet.microsoft.com/en-us/magazine/gg132708.aspx
Regards
Shashank Srivastava
MCITP - SQL SERVER 2008
INDIA
Follow me @ http://shashanksrivastavasqldba.blogspot.com/
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply