<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
Backups
»
What system databases do I need to backup?
21 posts, Page 2 of 3
««
1
2
3
»»
What system databases do I need to backup?
Rate Topic
Display Mode
Topic Options
Author
Message
Nadrek
Nadrek
Posted Tuesday, June 26, 2012 8:18 AM
Say Hey Kid
Group: General Forum Members
Last Login: 2 days ago @ 12:28 PM
Points: 675,
Visits: 2,031
I generally structure backups to handle all SIMPLE recovery model databases one way, all FULL and BULK_LOGGED recovery model databases another way, Master (which gets Full backups whenever SIMPLE databases get Differentials) by itself, and then special handling for special databases (rare). Tempdb is always excluded.
Unless you're very, very space or time constrained, back up everything except tempdb and databases you know you deliberately don't want to (they're restored from another server's backups on a frequent schedule and any changes have no value, etc.)
On most of my servers, there's several tiny databases (including master, msdb, and model), a few small ones, and perhaps one large one, which most often is the most critical. Thus, any exceptional rules for the large one that require more frequent backups have only a marginal cost to apply them to all other like databases (backing everything up more frequently takes little more time or space than backing only the big one up more frequently), especially if it's in the FULL recovery model, since T-Log backups don't back up the same data over and over like Full or Differential backups do.
Post #1321265
UconnDBA
UconnDBA
Posted Tuesday, June 26, 2012 8:52 AM
Valued Member
Group: General Forum Members
Last Login: Today @ 1:00 PM
Points: 53,
Visits: 310
Unless there is absolutely another reason to do otherwise, which i have to this point i have yet to find in the 10+ years have been doing SQL...
- One maintenance plan to do Full Backups on ALL databases on a daily basis. This
will do system and user databases. When a new database is added it will be
automatically backed up and to the same place as the others. Have a plan as
to how many days to keep and use maintenance plan to delete anything older
than that.
- One maintenance plan to do recurring Translog backups (say every hour). Set
this to ALL databases. It will skip databases in SIMPLE mode. Keep as many days
as the Full Backups.
- Another maintenance plan to do other tasks (say weekly), like check database
integrity, clean up history (very important. have seen performance hits from
leaving years of history data), reorg indexes, statistics.
Be sure to monitor that these jobs are running and are successful by using databasemail and having a way (system monitor) to alert if the SQLAgent service is not running.
Keep it simple until absolutely have to do otherwise.
John
Post #1321292
Lynn Pettis
Lynn Pettis
Posted Tuesday, June 26, 2012 9:01 AM
SSC-Insane
Group: General Forum Members
Last Login: Today @ 2:28 PM
Points: 21,611,
Visits: 27,442
UconnDBA (6/26/2012)
Unless there is absolutely another reason to do otherwise, which i have to this point i have yet to find in the 10+ years have been doing SQL...
- One maintenance plan to do Full Backups on ALL databases on a daily basis. This
will do system and user databases. When a new database is added it will be
automatically backed up and to the same place as the others. Have a plan as
to how many days to keep and use maintenance plan to delete anything older
than that.
- One maintenance plan to do recurring Translog backups (say every hour). Set
this to ALL databases. It will skip databases in SIMPLE mode. Keep as many days
as the Full Backups.
- Another maintenance plan to do other tasks (say weekly), like check database
integrity, clean up history (very important. have seen performance hits from
leaving years of history data), reorg indexes, statistics.
Be sure to monitor that these jobs are running and are successful by using databasemail and having a way (system monitor) to alert if the SQLAgent service is not running.
Keep it simple until absolutely have to do otherwise.
John
Maintenabce plans are good for small databases or when you find yourself an Accidental DBA having to get something into place. I used them when I first started working with SQL Server but after a bit I found them limiting. I started writing my own processes so that I could have more control over the processing, particularly the rebuilding or reorganization of indexes. I also wanted more control over scheduling the backups of databases and not having them all in one job (or maintenance plan), especially when I had databases with different recovery models and recovery requirements.
Lynn Pettis
For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here
or
when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here
and
here
Managing Transaction Logs
SQL Musings from the Desert
Fountain Valley SQL
(My Mirror Blog)
Post #1321301
D. Clark
D. Clark
Posted Tuesday, June 26, 2012 12:41 PM
SSC Rookie
Group: General Forum Members
Last Login: Wednesday, May 15, 2013 9:29 AM
Points: 29,
Visits: 119
Lynn Pettis (6/26/2012)
Maintenabce plans are good for small databases or when you find yourself an Accidental DBA having to get something into place. I used them when I first started working with SQL Server but after a bit I found them limiting. I started writing my own processes so that I could have more control over the processing, particularly the rebuilding or reorganization of indexes. I also wanted more control over scheduling the backups of databases and not having them all in one job (or maintenance plan), especially when I had databases with different recovery models and recovery requirements.
I couldn't agree more. The devs before me used maintenance plans, but I am actually planning on getting away from them. My biggest gripe so far is that if I modify a job associated with a maintenance plan (like adding a step to invoke a script to compress and copy backups) then I went back and modified the maintenance plan, I would find that the maintenance plan would restore the job to how it was before I made my changes to the job. Maybe there is a better way to do this, but right now I am just trying to stabilize everything. Plus, it just works.
Post #1321471
Nadrek
Nadrek
Posted Tuesday, June 26, 2012 3:43 PM
Say Hey Kid
Group: General Forum Members
Last Login: 2 days ago @ 12:28 PM
Points: 675,
Visits: 2,031
D. Clark (6/26/2012)
I couldn't agree more. The devs before me used maintenance plans, but I am actually planning on getting away from them. My biggest gripe so far is that if I modify a job associated with a maintenance plan (like adding a step to invoke a script to compress and copy backups) then I went back and modified the maintenance plan, I would find that the maintenance plan would restore the job to how it was before I made my changes to the job. Maybe there is a better way to do this, but right now I am just trying to stabilize everything. Plus, it just works.
Also agreed; personally, I like a slightly customized version of
Ola Hallengren's backup scripts
.
As with all backups, the real trick is to successfully and regularly test restoring them (preferably to a different database instance).
Post #1321585
Michael Valentine Jones
Michael Valentine Jones
Posted Tuesday, June 26, 2012 3:53 PM
SSCrazy
Group: General Forum Members
Last Login: Today @ 2:22 PM
Points: 2,944,
Visits: 10,508
D. Clark (6/26/2012)
Lynn Pettis (6/26/2012)
Maintenabce plans are good for small databases or when you find yourself an Accidental DBA having to get something into place. I used them when I first started working with SQL Server but after a bit I found them limiting. I started writing my own processes so that I could have more control over the processing, particularly the rebuilding or reorganization of indexes. I also wanted more control over scheduling the backups of databases and not having them all in one job (or maintenance plan), especially when I had databases with different recovery models and recovery requirements.
I couldn't agree more. The devs before me used maintenance plans, but I am actually planning on getting away from them. My biggest gripe so far is that if I modify a job associated with a maintenance plan (like adding a step to invoke a script to compress and copy backups) then I went back and modified the maintenance plan, I would find that the maintenance plan would restore the job to how it was before I made my changes to the job. Maybe there is a better way to do this, but right now I am just trying to stabilize everything. Plus, it just works.
You should not modify a mantenance plan job directly, except to change the notifications. Everything else should be done in the maintenance plan.
Post #1321593
D. Clark
D. Clark
Posted Wednesday, June 27, 2012 6:15 AM
SSC Rookie
Group: General Forum Members
Last Login: Wednesday, May 15, 2013 9:29 AM
Points: 29,
Visits: 119
Michael Valentine Jones (6/26/2012)
You should not modify a mantenance plan job directly, except to change the notifications. Everything else should be done in the maintenance plan.
I can agree with that, but that's why I want to move away from them. I feel like they are too restrictive. It's true that they are a great help to get things going quickly, but it just seems easier to just use jobs if you want finer control over what happens. In fact, I wouldn't be surprised if I eventually come to the conclusion that even jobs are too restrictive or troublesome, and I just start using pure T-SQL scripts in the future.
However, I did look for a solution to my problem, and it seems that I could just add a T-SQL statement task to run my powershell script that I added to the maintenance plan job. I would just need to invoke the script with the xp_cmdshell stored procedure.
Post #1321814
Robert Davis
Robert Davis
Posted Wednesday, June 27, 2012 7:35 AM
SSCommitted
Group: General Forum Members
Last Login: Sunday, May 19, 2013 8:09 AM
Points: 1,558,
Visits: 1,396
Print this out and hang it on your wall: BACKUP ALL THE DATABASES
You can backup the resource database's files through a simple robocopy script. Tempdb is the only database that you shouldn't back up.
My blog: SQL Soldier
Twitter:
@SQLSoldier
Microsoft Certified Master: SQL Server 2008
Sr. Product Consultant and Chief SQL Server Evangelist @ Idera
My book: Pro SQL Server 2008 Mirroring
Post #1321866
Divine Flame
Divine Flame
Posted Friday, February 08, 2013 3:29 AM
Ten Centuries
Group: General Forum Members
Last Login: Friday, May 17, 2013 2:00 PM
Points: 1,076,
Visits: 1,914
Hi All,
I know it’s an old thread but seemed a right place to ask this question. I want to confirm that what databases I need to restore back if we decide to rollback an upgrade.
For example:
1. We took backup of all the databases.
2. We installed SQL Server 2008 R2 Service Pack 2.
In case we want to rollback this upgrade we can use “Add or Remove Programs” to uninstall this upgrade.
I want to confirm that, once we have uninstalled the SP2 successfully using control panel, do we need to restore the backups of system databases master, model, msdb & mssqlsystemresource again? Or is it not required & it is done by the uninstall process itself?
Any insight on this is appreciated
PS: I can create a new thread if you think that would be more appropriate.
- Divine Flame
Post #1417565
george sibbald
george sibbald
Posted Friday, February 08, 2013 3:48 AM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 12:30 PM
Points: 5,269,
Visits: 11,205
For SQL2008 onwards the uninstall process takes care of it, no need to restore databases. SQL2005 its uninstall\reinstall so you would need to restore backups (master and msdb at least to get your configuration back)
this is a sql2005 thread so perhaps not the best place to ask the question, but hey ho.
---------------------------------------------------------------------
Post #1417573
« Prev Topic
|
Next Topic »
21 posts, Page 2 of 3
««
1
2
3
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
