| Author |
Topic |
|
jbruyet1
Starting Member
35 Posts |
 Posted - 2015-05-07 : 11:59:26
|
| Hey all, I'm new to SQL and I'm still trying to find out how things work. I have a backup appliance that I'm using to back up the databases on one of my servers. I was getting quite a few failures daily and support for the appliance said the problem was with a "maintenance plan." I went spelunking, found a couple of maintenance plans and disabled them and my daily failures disappeared. I'm down to a weekly backup failure and I can't find any active maintenance plans that might be causing the failure. The maintenance plans that I found were in Management Studio > Databases > Management > Maintenance Plans. There are three plans in there and all three show "Not Scheduled" in the Schedule field. Are there any obscure, arcane locations where I can look for other maintenance plans that might be interfering with my backups? Thanks, Joe B |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2015-05-07 : 18:28:09
|
I'd take a look at your SQL Agent Jobs to see if one or more of those were handling your backups.
I would rather be the man who bought the Brooklyn Bridge than the one who sold it. -Will Rogers |
 |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2015-05-08 : 01:32:57
|
| What does the backup failure error show? Is there a message in the error logs?Jack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2015-05-08 : 03:22:59
|
| What is the "Backup appliance" please? |
|
|
|
jbruyet1
Starting Member
35 Posts |
Posted - 2015-05-08 : 13:03:26
|
| --> Bustaz Kool, I'll find out what SQL Agent Jobs are and look there. --> jackv, the errors are not very informative: SRV-FNW/FNFA_SQL 3336 Transaction Log FailedSRV-FNW/FNGL_SQL 3337 Transaction Log FailedSRV-FNW/FNAP_SQL 3338 Transaction Log FailedSRV-FNW/FNIN_SQL 3339 Transaction Log Failed--> Kristen, The backup appliance is a Unitrends Recovery 713 |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2015-05-09 : 04:13:15
|
quote:
Originally posted by jbruyet1
The backup appliance is a Unitrends Recovery 713
I had a look at their website, but its mostly Sales Speak!Looks like it is a continuous-copy type hardware solution for "instant business continuity" in a disaster recovery scenario?I'm not understanding why you would disable regular SQL backups / maintenance plans (which would include things like Index Rebuild) in order to facilitate the Unitrends functions.Unless Backups and Database Housekeeping are done in some other way? |
|
|
|
jbruyet1
Starting Member
35 Posts |
Posted - 2015-05-11 : 11:50:48
|
| The way it was explained to me is that when a maintenance plan runs it disrupts the backup "chain" that the Unitrends device is working with. One example I was given is that when SQL does one of its maintenance plans Unitrends sees that something is different in the database from what it was expecting and errors out on the backup. Not being a SQL guy I didn't really understand much of what the rep was saying but this part seemed to make sense to me.As to "Backups and Database Housekeeping," the Unitrends device is backing up the databases but I'm not sure what Database Housekeeping functions are necessary and how they would interfere with Unitrends. Can you recommend a document or a web site where I can find some information on those things? Thanks,Joe B |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2015-05-11 : 12:51:45
|
There are two things that I see here. One is the Disaster Recovery scenario of "Main server is dead, we need to resume on another server ASAP". If the Unitrends box is continuously up-to-date with changes to the database server that sounds fine.The other issue is "Sorry, I accidentally deleted 1,000 customers 10 minutes ago" or "It looks like a software bug has been mangling your Sales Tax on certain invoices for quite some time". For those situations I look to the Log Backups so that I can revert to exactly how the database was 10 minutes/whatever ago. I'm not going to restore the main, production, database to that point (presumably new sales etc. have been added since), I'm going to restore to that point-in-time into a new, temporary, database and then mess around figuring out which customers were deleted and then, once I'm comfortable, I'll use SQL to merge those records back into the Production database (and then DROP the temporary database I restored earlier). I've done this in cases of employee fraud too - painstakingly working back through backups snapshots of the data to work out who-did-what-and-when. Corrupted database too: you find out one day that the production database has a corruption, and so does the backup (well ... it would! backups work by making an identical copy). Solution is to lock users out of the database, take a final Log backup, restore from an old backup (days, weeks or even months ago if necessary) and then replay ALL the Log Backups since. Because the Log file uses a radically different (primarily sequential) system to the database itself (with is random access) it tends not to get corrupted when the database does. Thus chances are good that restoring from "an old FULL backup" and reapplying "All Log backups since" will get a clean database. I suppose what I am describing are a number of "get out of jail free" cards  No idea if Unitrends handles those scenarios, or just the "server is dead" or "building is destroyed" scenarios, but that's what I would want to check.The only other housekeeping activity to perhaps consider is rebuilding of indexes. That tends to cause a monumental number of updates to the database - with Logging turned on its often the single biggest transaction in the day, often many-times more than actual user generated transactions. I'd want to know what sort of impact that might have on the Unitrends box (for example, maybe the volume of changes will cause the Unitrends box to fall behind "real time" and thus a disaster during the index rebuild could leave you exposed. For most companies, unless truly 24/7, Index rebuilds that run in the middle of the night, and Unitrends being 10 minutes behind "real time", won't be an issue. Perhaps something worth being aware of (or just knowing that it is a non-problem ) |
|
|
|
jbruyet1
Starting Member
35 Posts |
Posted - 2015-05-12 : 14:26:33
|
| Sigh, now I know why DBAs are in demand. Thanks,Joe B |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2015-05-12 : 22:43:20
|
| Its like anything where experience counts ... its just a question of knowing "where to kick the tyres" ...If your Unitrends box will provide a solution for the "I accidentally deleted 1,000 customer records" etc then I guess you are fine. If not then you DEFINITELY need LOG backups ALSO.IMHO |
|
|
|
|
What maintenance plan is running?