Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
  1. I'm trying to create a recurring scheduled job to fully backup the master, msdb and model databases, I'm using the Maintenance Plans for SQL Server 2008 R2, in the "Specific Databases to choose from the Backup DB Task Window" I wasn't sure if I should choose [System Databases] or [These databases: master, model, msdb]? Aren't they the same if I specifically check mark the specific databases or just choose the radio button for all system databases?

  2. If I choose to create them under the SQL Agent, would that be the same results? as I just need to create a daily or weekly backups for them.

share|improve this question

2 Answers 2

up vote 0 down vote accepted

1 - Create a new maintenance plan and choose system databases for the backup database task. That will handle all system databases. The ones you listed will be backed up along with others possibly (distribution DB if replication is running on the server for example). It will take away some of the guess work on your end

2- I'm not sure I follow on this one. If you create the maintenance plan that will run under the SQL Service Agent account. I would suggest doing nightly backups rather than weekly. You would presumably get the same results creating an agent job to do the backups but you would have to write the T-SQL code to do the backups and wouldn't get the benefits of setting up automatic cleaning of old backup files among other things.

share|improve this answer
    
Distribution is a "user" database –  Cougar9000 Mar 5 at 20:05
    
The distribution database shows under the "System Databases" node in SSMS –  TechDawg270 Mar 5 at 20:09
1  
I am aware of that. Distribution will not get backed up if you select "system databases". Just an FYI –  Cougar9000 Mar 5 at 20:16
    
I just tested and you are correct –  TechDawg270 Mar 5 at 20:27
    
@TechDawg270 Thank you very much for your reply, I will change that to choose all "System Databases". I dont see any "Distribution" database anywhere, that means I dont have any replication running on my server. –  Shayma Ahmad Mar 5 at 20:33

If you just want to back up the system databases, you should use the "System Databases" group in the drop down menu as opposed to individually selecting the system databases.

If you have not looked at the Ola Hallengren SQL Maintenance solution, I would recommend taking a peek as opposed to using the SQL Server maintenance plans.

More info about maintenance plans: TN Maintenance plans

share|improve this answer
    
I appreciate you providing me these links, I will definetely look at them. I created it first by selecting these 3 system databases individually and it worked too as it automatically created subfolders for each in the destination location for all my backups. Thanks again. –  Shayma Ahmad Mar 5 at 20:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.