Improve company productivity with a Business Account.Sign Up

x
?
Solved

SQL 2008 BACKUP/MAINT PLAN NOT WORKING

Posted on 2011-09-07
4
Medium Priority
?
380 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 31

Accepted Solution

by:
Rich Weissler earned 2000 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 31

Assisted Solution

by:Rich Weissler
Rich Weissler earned 2000 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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

584 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