August 26, 2011 at 9:33 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?
Backing up across network.
I guess Log Filling up takes care of Full Recovery mode w/out tran log backups
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 9:33 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?
In a differential backup strategy, take an ad-hoc full backup without copy-only flag and delete the backup file.
-- Gianluca Sartori
August 26, 2011 at 9:34 am
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.
That's the SAN software? I've heard great things about it. It's supposed to be fully transaction aware.
Thanks for the feedback.
"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 9:36 am
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.
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 9:36 am
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.
"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 9:37 am
Gianluca Sartori (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?
In a differential backup strategy, take an ad-hoc full backup without copy-only flag and delete the backup file.
Oooh, yeah, breaking the backup chain... there we go. Excellent.
"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 9:37 am
Grant Fritchey (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.
That's the SAN software? I've heard great things about it. It's supposed to be fully transaction aware.
Thanks for the feedback.
I'll let you know how the backup restores go then.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
August 26, 2011 at 9:37 am
Grant Fritchey (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.
That's the SAN software? I've heard great things about it. It's supposed to be fully transaction aware.
Thanks for the feedback.
Supposed to be. It didn't really work out that way. Point in time recovery tests failed for us.
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 9:38 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?
Not testing restores?
-Dan B
August 26, 2011 at 9:39 am
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?
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
August 26, 2011 at 9:41 am
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.
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 9:45 am
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.
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 9:45 am
Grant Fritchey (8/26/2011)
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.
SAN is fibre network (typically, unless you're using iSCSI), usually multi-path, redundant links, high speed. Depending where the remote server is for network backups, could be multi-hop, probably not redundant, higher risk of something dropping somewhere, plus it's typically slower.
Last time I worked with a SAN, the connections to the SAN were dual 4Gb/sec fibre connections. Network backbone was single-path 1Gb/sec ethernet.
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 9:47 am
Grant Fritchey (8/26/2011)
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?
Accidentally striping backup across multiple files when you only mean to use one?
EDIT -> I think there was recently an article about this, but can't remember if it was SSC or not...
-Dan B
That's definitely an issue. But is it a common issue? It's not one I've run into much on the forums.
I've seen it often enough to be considered a common issue.
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 9:48 am
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.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
Viewing 15 posts - 29,341 through 29,355 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply