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 ««123»»

What system databases do I need to backup? Expand / Collapse
Author
Message
Posted Tuesday, June 26, 2012 8: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: Today @ 8:00 AM
Points: 892, Visits: 2,473
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
Posted Tuesday, June 26, 2012 8:52 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 5:02 AM
Points: 68, Visits: 423
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
Posted Tuesday, June 26, 2012 9:01 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 1:42 PM
Points: 20,856, Visits: 32,870
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
Posted Tuesday, June 26, 2012 12:41 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, November 7, 2014 10:15 AM
Points: 38, Visits: 168
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
Posted Tuesday, June 26, 2012 3:43 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: Today @ 8:00 AM
Points: 892, Visits: 2,473
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
Posted Tuesday, June 26, 2012 3:53 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 4:38 AM
Points: 3,110, Visits: 11,528
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
Posted Wednesday, June 27, 2012 6:15 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, November 7, 2014 10:15 AM
Points: 38, Visits: 168
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
Posted Wednesday, June 27, 2012 7:35 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 8:16 AM
Points: 1,618, Visits: 1,554
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
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #1321866
Posted Friday, February 8, 2013 3:29 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 9:02 AM
Points: 1,344, Visits: 2,646
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.



Sujeet Singh
Post #1417565
Posted Friday, February 8, 2013 3:48 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:38 AM
Points: 5,888, Visits: 13,060
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 »

Add to briefcase ««123»»

Permissions Expand / Collapse