Avatar of webmonkey8
 asked on

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.

Microsoft SQL Server 2005

Avatar of undefined
Last Comment

8/22/2022 - Mon


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.

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.  
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.