Post reply

backup system databases

  • July 19, 2010 at 3:33 am

    #148087

    Hi

    Should you backup the system databases along with your own databases and if so can someone give me a brief explanation of why i need to do this.

    Thanks

  • July 19, 2010 at 3:49 am

    #1194774

    Yes, you need to....

    Your configurations, logins and jobs are stored in the master and msdb databases.

    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

  • July 19, 2010 at 3:50 am

    #1194775

    Great!

    Your master database contains metadata about the entire server like the list of databases, list of logins etc.

    This database gets regularly updates based on the server activities.

    Your msdb database contains information about the jobs, schedules, packages and metadata about all these located in it. If this database is lost then metadata about these objects are lost.

    Your model database is a template database on the server. SO if there are any setting that you want to be common in all the database you create, those setting can me made to the model database. so that going forward all the newly created databases possess the setting of the model database.

    To retain all these matadata about the server and some server level objects, It is important to backup the system databases.

    I think this explanation is good enough.

    Thanks,

    Lakshmi

  • July 19, 2010 at 3:50 am

    #1194776

    Yes! Absolutely.

    What happens if the drive that the master database file is on fails? Without backups you;ve lost all your logins, linked servers, server-level security, etc. If something happens to MSDB, you've lost all your jobs, job history, backup history, etc.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 19, 2010 at 4:01 am

    #1194782

    Obviously..

    You should backup your database either be it User defined or system defined and that too on the different drives.

  • July 19, 2010 at 4:29 am

    #1194797

    Cheers guys that makes total sense. Im going to be taking a full backup of our defined databases once a day. Should i backup up the transaction log as well or is this included in the full backup?

    Thanks

  • July 19, 2010 at 4:35 am

    #1194802

    Great!

    Your master database contains metadata about the entire server like the list of databases, list of logins etc.

    This database gets regularly updates based on the server activities.

    Your msdb database contains information about the jobs, schedules, packages and metadata about all these located in it. If this database is lost then metadata about these objects are lost.

    Your model database is a template database on the server. SO if there are any setting that you want to be common in all the database you create, those setting can me made to the model database. so that going forward all the newly created databases possess the setting of the model database.

    To retain all these matadata about the server and some server level objects, It is important to backup the system databases.

    I think this explanation is good enough.

    Thanks,

    Lakshmi

    Good Explanation By Lakshmi.......

  • July 19, 2010 at 4:40 am

    #1194808

    Richard Banks (7/19/2010)

    Cheers guys that makes total sense. Im going to be taking a full backup of our defined databases once a day. Should i backup up the transaction log as well or is this included in the full backup?

    If they're in full recovery, full backup is not sufficient. Take a look through this article - http://qa.sqlservercentral.com/articles/64582/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 19, 2010 at 4:54 am

    #1194818

    thanks for that. It all seems reasonably straight forward. The database im currently looking at is in simple recovery mode, which according to the article is bad. I will change it to full, set a full backup for once a day and a transaction log backup very 1 hour. With this in mind only 1 hours data will be lost. Am i correct? Sorry im a bit new to backups

    Thanks

  • July 19, 2010 at 5:07 am

    #1194824

    Full Backup included the Databases as well as the log.

    You can backup the log if you need to be able to restore to a certain point in time.

    Read up on the whole backup process here: msdn.microsoft.com/en-us/library/ms175477.aspx

    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

  • July 19, 2010 at 5:15 am

    #1194829

    Henrico Bekker (7/19/2010)

    Full Backup included the Databases as well as the log.

    Just enough of the log to be able to restore the database in a consistent state. Full backup does not truncate the log, only log backups do that.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 19, 2010 at 5:18 am

    #1194831

    Richard Banks (7/19/2010)

    The database im currently looking at is in simple recovery mode, which according to the article is bad.

    Well depends. If something happens to the database will it be sufficient to restore to the last full backup, losing up to a full day's data?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 19, 2010 at 5:28 am

    #1194834

    Richard Banks (7/19/2010)

    ...I will change it to full, set a full backup for once a day and a transaction log backup very 1 hour. With this in mind only 1 hours data will be lost. Am i correct?

    Yes. All other things being equal, your maximum (committed) data-loss exposure is one hour.

    Many production systems take log back ups more frequently than that, but it does depend on your circumstances and priorities.

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply