Solved

SQL 2008 BACKUP/MAINT PLAN NOT WORKING

Posted on 2011-09-07
4
368 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
GRANT, REVOKE, DENY 4 36
affinity mask in sql server 1 22
SQL Server - Check Constraint or NOT NULL? 11 67
Sql query 107 25
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
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.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

912 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

26 Experts available now in Live!

Get 1:1 Help Now