I'm trying to create a recurring scheduled job to fully backup the
modeldatabases, 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
[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?
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.
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
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.
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