backup system databases

Question

Post reply

backup system databases

Richard Banks

SSC Journeyman

Points: 80
More actions
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

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

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

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 1

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

lakshmi2np Old Hand Points: 373 More actions · Answer 2

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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

chetanr.jain Hall of Fame Points: 3938 More actions · Answer 4

Obviously..

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

Richard Banks SSC Journeyman Points: 80 More actions · Answer 5

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

Arjun SreeVastsva SSCertifiable Points: 7141 More actions · Answer 6

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.......

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

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://www.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

Richard Banks SSC Journeyman Points: 80 More actions · Answer 8

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

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 9

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

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

Paul White SSC Guru Points: 150467 More actions · Answer 12

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