Solved

SQL 2008 BACKUP/MAINT PLAN NOT WORKING

Posted on 2011-09-07
4
372 Views
Last Modified: 2012-06-27
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
Comment
Question by:yostnet
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 30

Accepted Solution

by:
Rich Weissler earned 500 total points
ID: 36498904
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
 

Author Comment

by:yostnet
ID: 36499174
the plan is already created (by someone else) | how can I make the changes to it |  very confusing compared to SQL2000.
0
 
LVL 30

Assisted Solution

by:Rich Weissler
Rich Weissler earned 500 total points
ID: 36499750
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
 

Author Closing Comment

by:yostnet
ID: 36709752
thx
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question