|
|
You're in the
sql server new users
group:Recovery model for system databases
sql server new users:
Hi,
I've inherited a SQL Server and one of the changes I made was put all the user databases in full recovery mode. Current backups are set for a full database backup at midnight every night and a log backup shortly thereafter. My question is: - I was told that the system databases should be left in simple mode, but after doing some reading, they seem pretty important. Is this correct? What's the usual setup for this? Thanks in advance.
Hi,
Thank you both for your reply. Yes, I have already addressed the issue of only 1 log backup a day. Coming for an Oracle world, the transaction log is different in SQL Server, but I realized pretty quick that with the setup they currently have, they do have the potential to lose up to 24 hours of data. When asked if that was acceptable, the answer of course was no. So I am in the process of changing that to a more reasonable time. Thanks and Happy Holidays! Isabelle [quoted text, click to view] "Andrew J. Kelly" wrote:
> Simple mode is fine for system db's as they don't often change. But being > important or not should not be the criteria for what recovery mode the user > dbs are in. Placing them in Full with a single nightly full backup and an > immediate log backup there after is not a very wise backup strategy. Log > backups are to minimize the amount of data lost in the event of a problem. > If you only backup the log once a night you have almost 24 hours of > potential data loss. Especially if the log is right after the FULL backup. > If you decide that a database can not afford to loose no more than 15 > minutes worth of activity then your log backup interval should be no more > than 15 minutes apart during any time the database is active. > > -- > Andrew J. Kelly SQL MVP > > > "isabelle" <isabelle@discussions.microsoft.com> wrote in message > news:010BCEE2-BE76-432D-AEEE-CA0ECEE25982@microsoft.com... > > Hi, > > > > I've inherited a SQL Server and one of the changes I made was put all the > > user databases in full recovery mode. Current backups are set for a full > > database backup at midnight every night and a log backup shortly > > thereafter. > > My question is: > > - I was told that the system databases should be left in simple mode, but > > after doing some reading, they seem pretty important. Is this correct? > > What's the usual setup for this? > > > > Thanks in advance. > > Isabelle > >
Hi,
Yes, system databases are very important, but as they don't change as frequently as user databases I'm doing daily full backup of system databases. It proved to be enough for me. -- Danijel Novak [quoted text, click to view] "isabelle" <isabelle@discussions.microsoft.com> wrote in message news:010BCEE2-BE76-432D-AEEE-CA0ECEE25982@microsoft.com... > Hi, > > I've inherited a SQL Server and one of the changes I made was put all the > user databases in full recovery mode. Current backups are set for a full > database backup at midnight every night and a log backup shortly > thereafter. > My question is: > - I was told that the system databases should be left in simple mode, but > after doing some reading, they seem pretty important. Is this correct? > What's the usual setup for this? > > Thanks in advance. > Isabelle
Simple mode is fine for system db's as they don't often change. But being
important or not should not be the criteria for what recovery mode the user dbs are in. Placing them in Full with a single nightly full backup and an immediate log backup there after is not a very wise backup strategy. Log backups are to minimize the amount of data lost in the event of a problem. If you only backup the log once a night you have almost 24 hours of potential data loss. Especially if the log is right after the FULL backup. If you decide that a database can not afford to loose no more than 15 minutes worth of activity then your log backup interval should be no more than 15 minutes apart during any time the database is active. -- Andrew J. Kelly SQL MVP [quoted text, click to view] "isabelle" <isabelle@discussions.microsoft.com> wrote in message news:010BCEE2-BE76-432D-AEEE-CA0ECEE25982@microsoft.com... > Hi, > > I've inherited a SQL Server and one of the changes I made was put all the > user databases in full recovery mode. Current backups are set for a full > database backup at midnight every night and a log backup shortly > thereafter. > My question is: > - I was told that the system databases should be left in simple mode, but > after doing some reading, they seem pretty important. Is this correct? > What's the usual setup for this? > > Thanks in advance. > Isabelle
[quoted text, click to view]
> We won't hold that against you. ;) Thanks for the gracious understanding :-) and for your reply. Yeah, I'm still not sure how I feel about my Oracle databases going away and being replaced by SQL :-( I've been an Oracle DBA for over 7 years and it is a very different ball game with SQL. Still trying to get used to it and keep an open mind. It seems like I'm in the right direction with the system databases and changing the log backups is a must. This is a great forum and has been very helpful! Isabelle [quoted text, click to view] "Mike Hodgson" wrote:
> > > isabelle wrote: > > >Coming for an Oracle world... > > > > > We won't hold that against you. ;) > > Yeah, nightly full database backups with hourly, half-hourly or quarter-hourly log backups is a very common thing to do in terms of a SQL Server backup strategy. As for the system databases, /model/ will almost never change, /tempdb/ you don't care about (it gets recreated every time you start SQL Server anyway), /master/ will only change when logins or databases change (eg. new DB) or when you change server-wide config (like the memory thresholds or processor affinity, etc.) and /msdb/ will change with SQLAgent related stuff (like jobs, operators, alerts, log shipping config, backup/restore history, DB maint config, etc.). I conveniently ignored /distribution/ but that's only relevant if your server is a distributor in a replication topology. > > -- > *mike hodgson*
Isabelle,
Keep in mind that there are lots of people here that are going thru or have already been thru what you are now doing. So if you come to the conclusion that SQL Server can't do something that you think it should this is the place to ask. Chances are it will do everything you need it to do just maybe a little different way than with Oracle. -- Andrew J. Kelly SQL MVP [quoted text, click to view] "isabelle" <isabelle@discussions.microsoft.com> wrote in message news:4000AB92-8FD8-4700-9F66-A650918B3D62@microsoft.com... >> We won't hold that against you. ;) > > Thanks for the gracious understanding :-) and for your reply. Yeah, I'm > still not sure how I feel about my Oracle databases going away and being > replaced by SQL > :-( I've been an Oracle DBA for over 7 years and it is a very different > ball game with SQL. Still trying to get used to it and keep an open mind. > It seems like I'm in the right direction with the system databases and > changing the log backups is a must. > > This is a great forum and has been very helpful! > > Isabelle > > "Mike Hodgson" wrote: > >> >> >> isabelle wrote: >> >> >Coming for an Oracle world... >> > >> > >> We won't hold that against you. ;) >> >> Yeah, nightly full database backups with hourly, half-hourly or >> quarter-hourly log backups is a very common thing to do in terms of a SQL >> Server backup strategy. As for the system databases, /model/ will almost >> never change, /tempdb/ you don't care about (it gets recreated every time >> you start SQL Server anyway), /master/ will only change when logins or >> databases change (eg. new DB) or when you change server-wide config (like >> the memory thresholds or processor affinity, etc.) and /msdb/ will change >> with SQLAgent related stuff (like jobs, operators, alerts, log shipping >> config, backup/restore history, DB maint config, etc.). I conveniently >> ignored /distribution/ but that's only relevant if your server is a >> distributor in a replication topology. >> >> -- >> *mike hodgson* >> blog: http://sqlnerd.blogspot
[quoted text, click to view] isabelle wrote: We won't hold that against you. ;)>Coming for an Oracle world... > > Yeah, nightly full database backups with hourly, half-hourly or quarter-hourly log backups is a very common thing to do in terms of a SQL Server backup strategy. As for the system databases, /model/ will almost never change, /tempdb/ you don't care about (it gets recreated every time you start SQL Server anyway), /master/ will only change when logins or databases change (eg. new DB) or when you change server-wide config (like the memory thresholds or processor affinity, etc.) and /msdb/ will change with SQLAgent related stuff (like jobs, operators, alerts, log shipping config, backup/restore history, DB maint config, etc.). I conveniently ignored /distribution/ but that's only relevant if your server is a distributor in a replication topology. -- *mike hodgson*
Don't see what you're looking for? Try a search.
|
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |