Backup and Housekeeping with Maintenance Plans

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Gethyn Ellis » Backup and Housekeeping with Maintenance...

32 posts, Page 2 of 4

««1 2 3 4 »»»

Backup and Housekeeping with Maintenance Plans

Rate Topic

Display Mode

Topic Options

Author

Message

Taggs

Posted Wednesday, January 26, 2011 8:23 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, May 20, 2013 7:45 AM
Points: 36, Visits: 127

jasona.work

Just something to think about. Consider seperating out a database backup maintenance plan. If the plan fails you may end up with no backups at all! Crazy

If you seperate them out tand one plan fails at least you get the other databases backed up.

Post #1053898

Nadrek

Posted Wednesday, January 26, 2011 11:18 AM

Say Hey Kid

Group: General Forum Members
Last Login: Monday, May 20, 2013 12:28 PM
Points: 675, Visits: 2,031

A good basic article about of two of the four major backup problems I commonly see:
1) Not having backups at all
2) Not having transaction log backups at all (unlimited log file growth)
3) Not cleaning up after your backups (unlimited backup size growth)
4) Not restoring your backups as a test
4a) Not doing PITR restores as a test

I'd at least put a note about T-Log backups in your article; many accidental DBA's overlook the absolute necessity of the for Full and Bulk-Logged recovery model databases.

Post #1054079

GRE (Gethyn Ellis)

Posted Wednesday, January 26, 2011 11:53 AM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 9:55 AM
Points: 1,024, Visits: 2,768

Nadrek, Thanks for your comment, this article was meant to be about maintenance/housekeeping of backup files. The points you raise are good points and although its not a 'how to...' article I do have a blog post here http://www.gethynellis.com/2010/10/sql-server-recovery-models.html that looks at the SQL Server recovery models and what you need to do in each.

Gethyn Ellis

gethynellis.com

Post #1054117

bcb

Posted Wednesday, January 26, 2011 1:00 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, April 25, 2013 12:15 PM
Points: 20, Visits: 91

Nice, Gethyn. But i like to run DBCC Checkdb as first step... If checkdb fails, the job quits and you don't end up backing up a corrupt database.

Post #1054168

Rahul The Dba

Posted Wednesday, January 26, 2011 1:05 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 10:32 AM
Points: 127, Visits: 122

nice article
Thanks

Rahul Tongue

Post #1054173

GRE (Gethyn Ellis)

Posted Wednesday, January 26, 2011 1:12 PM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 9:55 AM
Points: 1,024, Visits: 2,768

bcb (1/26/2011)

Nice, Gethyn. But i like to run DBCC Checkdb as first step... If checkdb fails, the job quits and you don't end up backing up a corrupt database.

Another good point.

Gethyn Ellis

gethynellis.com

Post #1054178

GRE (Gethyn Ellis)

Posted Wednesday, January 26, 2011 1:12 PM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 9:55 AM
Points: 1,024, Visits: 2,768

Rahul The Dba (1/26/2011)

nice article
Thanks

Thanks Rahul I'm glad you like it.

Gethyn Ellis

gethynellis.com

Post #1054180

Shawn Melton

Posted Wednesday, January 26, 2011 3:28 PM

Right there with Babe

Group: General Forum Members
Last Login: Saturday, May 11, 2013 8:56 PM
Points: 754, Visits: 1,891

Good information...

Another point to make, I had to learn at current employer, is timezone on your SQL Servers. I imagine most DBAs deal with servers within their on timezone but we actually have our servers set to UTC since we go across multiple timezones within the US. Something to be aware of if you may do consulting work or something.

I have also heard of guys who setup the maintenance plan but leave the schedule blank. They then use a SQL Agent job and in one of the steps call the maintenance plan when they want it. That way when you are doing a lot of things maybe altering data, you can call that backup maintenance plan more than once to cover yourself. Then also if you have a mix of things you do on your DB that require T-SQL but other things setup easier in maintenance plan, it can give you more flow control within a SQL Agent job. I had never thought about doing it like that when I heard it.

--------------------------------------------------------------
Shawn Melton
@wshawnmelton

Post #1054272

Taggs

Posted Thursday, January 27, 2011 2:40 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, May 20, 2013 7:45 AM
Points: 36, Visits: 127

Grasshopper :

Surely if your Database is corrupt it is all the more reason to back it up? At least if you have a backup you have something to restore and then try and fix. If the database corrupts further and you don't have a backup to restore you may find yourself in trouble.

Post #1054435

GRE (Gethyn Ellis)

Posted Thursday, January 27, 2011 2:54 AM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 9:55 AM
Points: 1,024, Visits: 2,768

Taggs (1/27/2011)

You may run into problems trying to retore a corrupt database. You whould backup your databases regulary and perform checkdb regulary to find any corruption as soon as possible

Gethyn Ellis

gethynellis.com

Post #1054439

« Prev Topic | Next Topic »

32 posts, Page 2 of 4

««1 2 3 4 »»»

Permissions