Solved

SQL 2008 BACKUP/MAINT PLAN NOT WORKING

Posted on 2011-09-07
4
367 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
  • 2
  • 2
4 Comments
 
LVL 29

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 29

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Suggested Solutions

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.
In this article I will describe the Copy Database Wizard 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.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

705 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now