August 26, 2011 at 9:58 am
Grant Fritchey (8/26/2011)
Need the advice of the Thread.Putting together an article on the most common backup errors (apart from not having one) and how to avoid them. I have four so far:
Backing up files instead of creating backups (.mdf, ldf, you know)
Running out of space on disk
Log Filling up
Only having differential backups available.
Are there any glaring ones I'm missing here?
Relying on DBCC TIMEWARP to do the backups for you. 😀
The other folks already hit was I was going to suggest such as breaking the backup chain.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 26, 2011 at 10:11 am
Stefan Krzywicki (8/26/2011)
SQLRNNR (8/26/2011)
Stefan Krzywicki (8/26/2011)
SQLRNNR (8/26/2011)
Stefan Krzywicki (8/26/2011)
Grant Fritchey (8/26/2011)
Need the advice of the Thread.Putting together an article on the most common backup errors (apart from not having one) and how to avoid them. I have four so far:
Backing up files instead of creating backups (.mdf, ldf, you know)
Running out of space on disk
Log Filling up
Only having differential backups available.
Are there any glaring ones I'm missing here?
Creating your backups on the same drive as your log or data files. (Sometimes you have no choice, but man is that a pain)
Not backing up your system databases?
I'm not great with backups, so those might not be problems or might not be common.
BTW, we're using a tool called EMC to do our SQL backups and the person in charge of this says it does a "hot file backup" of the database files. I haven't been able to find anything on EMC on-line that explains their backup system, but is this going to cause problems? From what has been explained to me, it doesn't actually run a SQL backup, but I hope to be learning more about it next week when I go through the restore process with someone who has done it before.
EMC has two main products. Avamar and Networker. Networker is a load of dung but says it can do that. Avamar is a redundant grid system that is supposed to do that as well. I however, prefer to use the native sql backup over Avamar (hands down) because I want to ensure I have those backup files available. Then I let Avamar backup those bak files to the grid after the fact.
I've been told we're using Networker.
What problems did you have with it? Other than Point In Time recovery, were there problems?
Better question would be - did it ever work?
Networker point in time did not work. EMC could never configure it correctly for us. We went with old reliable SQL Backup to disk and then networker off to tape. That broke after an upgrade and EMC could never get networker working again. Thus they migrated us to Avamar at a discount. Avamar is by far and away a much better solution and product. I will NEVER recommend that POS app called Networker.
It worked on our 2000 instance, this is the first time we're trying it on the 2008R2 instance. Even on the 2000 instance, I'm not sure it didn't play a part in the crash that caused the need for a restore in the first place.
Greater success than we had. Maybe they fixed some bugs with it.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 26, 2011 at 10:40 am
skrilla99 (8/26/2011)
Grant Fritchey (8/26/2011)
Need the advice of the Thread.Putting together an article on the most common backup errors (apart from not having one) and how to avoid them. I have four so far:
Backing up files instead of creating backups (.mdf, ldf, you know)
Running out of space on disk
Log Filling up
Only having differential backups available.
Are there any glaring ones I'm missing here?
Not testing restores?
-Dan B
That one is excellent, but I've already written an article on database testing recently.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 26, 2011 at 10:43 am
SQLRNNR (8/26/2011)
Grant Fritchey (8/26/2011)
SQLRNNR (8/26/2011)
Grant Fritchey (8/26/2011)
Need the advice of the Thread.Putting together an article on the most common backup errors (apart from not having one) and how to avoid them. I have four so far:
Backing up files instead of creating backups (.mdf, ldf, you know)
Running out of space on disk
Log Filling up
Only having differential backups available.
Are there any glaring ones I'm missing here?
Backing up across network.
I guess Log Filling up takes care of Full Recovery mode w/out tran log backups
Oh, right, across the network. Gotta ask though, do we really think that's as big a deal as we used to? I only ask because I've been running backups for years to "local" drives that were actually SAN drives that required a network hop to get there. Further, it's never been an issue. Why would simply backing up to a network share be so much worse (and I'm asking that with the full knowledge that I've seen it behave badly, but it was a long time ago). Are we perpetuating old advice on that one? If not, it's a good one.
I would say it is. We just had a big thread on that topic. If not for the potential network disconnect, the slowness of doing it. I don't think SAN backups count - dedicated fiber channel.
Right. It's that lack of hardware knowledge that frequently chews on my behind. Thank you.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 26, 2011 at 10:47 am
Grant Fritchey (8/26/2011)
Need the advice of the Thread.Putting together an article on the most common backup errors (apart from not having one) and how to avoid them. I have four so far:
Backing up files instead of creating backups (.mdf, ldf, you know)
Running out of space on disk
Log Filling up
Only having differential backups available.
Are there any glaring ones I'm missing here?
Offsite backups (the lack thereof)
Not testing the backups
Not keeping enough backups (or writing over yesterday's backup)
Assuming a completed checksum means you have a good backup
... most of the others I can think of have to do with recovery, such as knowing where your backups are kept, etc.
August 26, 2011 at 10:50 am
Grant Fritchey (8/26/2011)
Need the advice of the Thread.Putting together an article on the most common backup errors (apart from not having one) and how to avoid them. I have four so far:
Backing up files instead of creating backups (.mdf, ldf, you know)
Running out of space on disk
Log Filling up
Only having differential backups available.
Are there any glaring ones I'm missing here?
Never restoring backups to check that they are actually OK.
Unexpected (and lost) backups that break the chain (eg NTBackup can do this if it runs on the machine your database service is on; so can someone who takes a backup before an upgrade or fix, then deletes it when he's sure nothing has gone adrift)
Tom
August 26, 2011 at 10:50 am
Brandie Tarvin (8/26/2011)
Grant Fritchey (8/26/2011)
Need the advice of the Thread.Putting together an article on the most common backup errors (apart from not having one) and how to avoid them. I have four so far:
Backing up files instead of creating backups (.mdf, ldf, you know)
Running out of space on disk
Log Filling up
Only having differential backups available.
Are there any glaring ones I'm missing here?
Offsite backups (the lack thereof)
Not testing the backups
Not keeping enough backups (or writing over yesterday's backup)
Assuming a completed checksum means you have a good backup
... most of the others I can think of have to do with recovery, such as knowing where your backups are kept, etc.
Oh, this is becoming more commonplace: Backing up to USB drives (IMHO a bad idea for several reasons).
And, before I forget, not verifying that the backup process succeeded (slightly different from making sure the backup itself is good).
August 26, 2011 at 10:53 am
Can anyone possibly see how an update WHERE <identity column> = <constant value> could possibly update the entire table?
http://www.sqlservercentral.com/Forums/Topic1166215-1292-1.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 26, 2011 at 10:58 am
GilaMonster (8/26/2011)
Can anyone possibly see how an update WHERE <identity column> = <constant value> could possibly update the entire table?http://www.sqlservercentral.com/Forums/Topic1166215-1292-1.aspx
I put in my 2 cents, but Jeff seems to recall a bug that would do exactly this sort of thing. So it's more likely he's right.
August 26, 2011 at 10:59 am
Brandie Tarvin (8/26/2011)
GilaMonster (8/26/2011)
Can anyone possibly see how an update WHERE <identity column> = <constant value> could possibly update the entire table?http://www.sqlservercentral.com/Forums/Topic1166215-1292-1.aspx
I put in my 2 cents, but Jeff seems to recall a bug that would do exactly this sort of thing. So it's more likely he's right.
I'm putting my money on user error of some form (or trigger, actually updating a view or something like that). A fixed bug in SQL 2000 doesn't just come back in 2008 R2.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 26, 2011 at 11:00 am
Brandie Tarvin (8/26/2011)
GilaMonster (8/26/2011)
Can anyone possibly see how an update WHERE <identity column> = <constant value> could possibly update the entire table?http://www.sqlservercentral.com/Forums/Topic1166215-1292-1.aspx
I put in my 2 cents, but Jeff seems to recall a bug that would do exactly this sort of thing. So it's more likely he's right.
Yeah. I'm curious to see more info on that bug.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 26, 2011 at 11:02 am
GilaMonster (8/26/2011)
Brandie Tarvin (8/26/2011)
GilaMonster (8/26/2011)
Can anyone possibly see how an update WHERE <identity column> = <constant value> could possibly update the entire table?http://www.sqlservercentral.com/Forums/Topic1166215-1292-1.aspx
I put in my 2 cents, but Jeff seems to recall a bug that would do exactly this sort of thing. So it's more likely he's right.
I'm putting my money on user error of some form (or trigger, actually updating a view or something like that). A fixed bug in SQL 2000 doesn't just come back in 2008 R2.
I've seen triggers that do that before, way back when I first started. I even wrote one by accident when I was first studying to become a DBA. Taught me a lot about recursion. @=)
August 26, 2011 at 11:07 am
SQLRNNR (8/26/2011)
Grant Fritchey (8/26/2011)
SQLRNNR (8/26/2011)
Grant Fritchey (8/26/2011)
Need the advice of the Thread.Putting together an article on the most common backup errors (apart from not having one) and how to avoid them. I have four so far:
Backing up files instead of creating backups (.mdf, ldf, you know)
Running out of space on disk
Log Filling up
Only having differential backups available.
Are there any glaring ones I'm missing here?
Backing up across network.
I guess Log Filling up takes care of Full Recovery mode w/out tran log backups
Oh, right, across the network. Gotta ask though, do we really think that's as big a deal as we used to? I only ask because I've been running backups for years to "local" drives that were actually SAN drives that required a network hop to get there. Further, it's never been an issue. Why would simply backing up to a network share be so much worse (and I'm asking that with the full knowledge that I've seen it behave badly, but it was a long time ago). Are we perpetuating old advice on that one? If not, it's a good one.
I would say it is. We just had a big thread on that topic. If not for the potential network disconnect, the slowness of doing it. I don't think SAN backups count - dedicated fiber channel.
As long as I have a few Gbits/sec available, I'm happy to back up over network (in fact prefer to do so, since I want my backups at least 50 metres from the server with a good wall between them and it to avoid being caught by something that damages everything in a small area). If the data volume (including transaction logs) is small enough, a 1Gb/sec network is adequate. I don't understand why a fibrechannel connection is any different to any other similar network connection - but I suppose that attitude stems from having worked decades ago with mainframes that used networks for everything to get improved flecibility, speed, and reliability compared to using conventional bicastrip connectors.
Tom
August 26, 2011 at 11:13 am
Another backup potential failure: backing up to unreliable media (backups should be on redundant arrays; if they are to be written to tape for off-site storage, two copies should be made not just one).
Backup retention period too short.
Tom
August 26, 2011 at 11:13 am
SQLRNNR (8/26/2011)
Brandie Tarvin (8/26/2011)
GilaMonster (8/26/2011)
Can anyone possibly see how an update WHERE <identity column> = <constant value> could possibly update the entire table?http://www.sqlservercentral.com/Forums/Topic1166215-1292-1.aspx
I put in my 2 cents, but Jeff seems to recall a bug that would do exactly this sort of thing. So it's more likely he's right.
Yeah. I'm curious to see more info on that bug.
The only one I can find in the 2000 hotfix list is this:
http://support.microsoft.com/kb/285870/EN-US - Inadequate Halloween protection when updating with a self-join. Fixed in 2000 SP1
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 29,356 through 29,370 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply