How do I check and modify backup schedules in MS SQL Server 2005?
The company I work for has a few databases in MS SQL Server 2005 that runs scheduled backups weekly and daily. How do I check to see what the scheduled backup frequency is for one of the databases, and if necessary, change the backup schedule for that particular database? Please walk me through the steps.
The first thing you need to look at is whether the backup is done as part of a maintenance job or not. You can find out by looking in SSMS and when you are connected to the server in the Object Explorer, drill down to ServerName > Management > Maintenance Plans. If there are maintenance plans listed there, open up anything that looks like it relates to a backup. If you can't tell, open them up one at a time and look for a Back Up Database Task (See attached screenshot). This should show you which databases are being backed up. Above that, in the schedule, it will show you when it will run.
If it's not a maintenance plan, drill down to ServerName > SQL Server Agent > Jobs and look for any jobs that look like they relate to a backup. Double click on the job to open the properties and click on the Steps tab on the left. You can open each step to see which databases are being backed up. You can click on the Schedules tab on the left to see when the job runs. MaintenancePlanWindow.bmp
webmonkey8
ASKER
aaronkin - it seems as though, when I followed your step, it's showing me the backup schedule for the ALL the databases on the server, not just that one database I want to find out. In the database backups under "Jobs", in the "Steps" section, I only see the name 'Subplan' under there. I'm not too sure what this is.
I do see the specific database I want to look for under 'MyServer' --> 'Databases' --> 'MyDatabase'. If I view the properties of this, I'm not seeing the backup schedule there as well.
http://www.exforsys.com/tutorials/sql-server-2005/sql-server-2005-maintenance-plan-using-wizard.html
http://www.exforsys.com/tutorials/sql-server-2005/sql-server-2005-maintenance-plan-without-using-wizard.html