[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 377
  • Last Modified:

SQL 2008 BACKUP/MAINT PLAN NOT WORKING

I have a server with SQL 2008 installed | I thought that I had a maintenance plan working that was to remove the backup files for the databases and transactions after 2 days, but appears to not be working.

Please advise on the proper steps to auto-remove the backup files after 2 days.
0
yostnet
Asked:
yostnet
  • 2
  • 2
2 Solutions
 
Rich WeisslerProfessional Troublemaker^h^h^h^h^hshooterCommented:
I assume you built the maintenance plan using the maintenance plan tool, and you have a Maintenance Cleanup Task.  When I build mine, I have the backup jobs put the backups for each database in a separate folder, and I always "Include first-level subfolders" in my cleanup task.

In addition, under the Cleanup job, I always end up with two cleanup tasks.  One to remove the trn files (transaction log backups), and one to clean up the bak files (full backups).  I made a mistake once, and included a period ('.') in the file extension box, so it was attempting to remove *..bak files.... which was failing.

Second, I link my tasks in the one job with a blue 'on completion' rather than 'on success' arrow.

Finally, make absolutely certain your SQL Agent is running.  None of the jobs will run without it.
0
 
yostnetAuthor Commented:
the plan is already created (by someone else) | how can I make the changes to it |  very confusing compared to SQL2000.
0
 
Rich WeisslerProfessional Troublemaker^h^h^h^h^hshooterCommented:
Yes, it certainly is.  The Maintenance Plans started using SSIS (the successor to DTS) in SQL 2005.

The good news is that you can confirm that the SQL Agent is running the same way you could in SQL 2000.

To edit the maintenance plan, in the Management Studio, drill down to management, then maintenance plan... and there will be one or more maintenance plans under that.  You should be able to double click the maintenance plan which will open the maintenance plan editor.  (It's a subset of the full SSIS.)  By default, there will be several new windows... one contains a set of tool objects that can be part of the plan, I usually see that on the left side of the screen.  On the right top, will be a list of schedules associated with the plan, and below the schedules will be the steps.  Assuming the plan wasn't built with all one schedule, there will be several schedules -- which if the Maintenance Plan Wizard was used, won't be named very helpfully.  You can start at the first schedule and look at the steps which appear in the lower window.  Fortunately the steps are usually named somewhat usefully.  One of those will hopefully jump out as your Cleanup job.

Of course, maintenance doesn't have to be done by maintenance plan (much less the maintenance plan wizard).  If there aren't any Maintenance Plans listed, you can check in the Management Studio... this time under SQL Agent, Job Monitor.  (Right above the Job Monitor should be a folder which also has the jobs....)  You can look through the listings of the jobs to find the various tasks scheduled to run on the server.  The maintenance plan creates jobs that appear here -- but there can easily be additional jobs as well.
0
 
yostnetAuthor Commented:
thx
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now