Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Backup and Housekeeping with Maintenance Plans Expand / Collapse
Author
Message
Posted Wednesday, January 26, 2011 8:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 19, 2013 9:13 AM
Points: 36, Visits: 134
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! If you seperate them out tand one plan fails at least you get the other databases backed up.
Post #1053898
Posted Wednesday, January 26, 2011 11:18 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, September 15, 2014 11:09 AM
Points: 869, Visits: 2,399
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
Posted Wednesday, January 26, 2011 11:53 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 7:04 AM
Points: 1,030, Visits: 2,795
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
Posted Wednesday, January 26, 2011 1:00 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 8, 2014 4:10 PM
Points: 21, Visits: 103
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
Posted Wednesday, January 26, 2011 1:05 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 11, 2013 3:27 PM
Points: 127, Visits: 132
nice article
Thanks


Rahul
Post #1054173
Posted Wednesday, January 26, 2011 1:12 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 7:04 AM
Points: 1,030, Visits: 2,795
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
Posted Wednesday, January 26, 2011 1:12 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 7:04 AM
Points: 1,030, Visits: 2,795
Rahul The Dba (1/26/2011)
nice article
Thanks


Thanks Rahul I'm glad you like it.


Gethyn Ellis

gethynellis.com
Post #1054180
Posted Wednesday, January 26, 2011 3:28 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 8:20 PM
Points: 885, Visits: 2,469
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
PS C:\>(Find-Me).TwitterURL
@wsmelton
PS C:\>(Find-Me).BlogURL
meltondba.wordpress.com
Post #1054272
Posted Thursday, January 27, 2011 2:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 19, 2013 9:13 AM
Points: 36, Visits: 134
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
Posted Thursday, January 27, 2011 2:54 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 7:04 AM
Points: 1,030, Visits: 2,795
Taggs (1/27/2011)
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.


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 »

Add to briefcase ««1234»»»

Permissions Expand / Collapse