can't backup database

I have SQL2000 running on a member 2003server.
Have setup a maintenance plan but our primary database as well as the Main
database are not getting backed up among other things.
Looking at the history log I see many errors:
Check data and index linkage errors out with the message "Repair statement
not processed. Database needs to be in single user mode"
Backup transaction log "Backup can not be performed on this database.
This sub task is ignored"
I don't even have error messages for the backups that aren't happening for
these 2.
All the other databases, Northwind, model, msdb, and pubs are doing fine.
What am I missing here?> Check data and index linkage errors out with the message "Repair statement
> not processed. Database needs to be in single user mode"
Remove the "attempt to repair minor problems" from the integrity check part of the maint plan.
> Backup transaction log "Backup can not be performed on this database.
> This sub task is ignored"
Don't attempt to backup of the log of databases in simple recovery model.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Rockitman" <Rockitman@.discussions.microsoft.com> wrote in message
news:B631B1DA-9D97-4FB6-886C-BD26ABE249CF@.microsoft.com...
>I have SQL2000 running on a member 2003server.
> Have setup a maintenance plan but our primary database as well as the Main
> database are not getting backed up among other things.
> Looking at the history log I see many errors:
> Check data and index linkage errors out with the message "Repair statement
> not processed. Database needs to be in single user mode"
> Backup transaction log "Backup can not be performed on this database.
> This sub task is ignored"
> I don't even have error messages for the backups that aren't happening for
> these 2.
> All the other databases, Northwind, model, msdb, and pubs are doing fine.
> What am I missing here?|||Why do I need to remove the "attempt to repair minor problems"? Why is this
option available if it only screws up the whole maintenance plan?
Why does it say that database needs to be in single user mode'
How does one make a database into single user mode'
And your last statement is ? to me as well: "Don't attempt to backup the
log of the databases in simple recovery model." What the heck does that
mean'
You didn't address why my databases are not even getting backed up nor why
there is no error message as to why they're not getting backed up.
Please forgive me, I am not an SQL guru, only know enough to keep this
shit running, but I've had no problems in running all these things in prior
maintenance plans in the past.
Please be a bit more thourough in your explanations. Thanks man.
"Tibor Karaszi" wrote:
> > Check data and index linkage errors out with the message "Repair statement
> > not processed. Database needs to be in single user mode"
> Remove the "attempt to repair minor problems" from the integrity check part of the maint plan.
>
> > Backup transaction log "Backup can not be performed on this database.
> > This sub task is ignored"
> Don't attempt to backup of the log of databases in simple recovery model.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Rockitman" <Rockitman@.discussions.microsoft.com> wrote in message
> news:B631B1DA-9D97-4FB6-886C-BD26ABE249CF@.microsoft.com...
> >I have SQL2000 running on a member 2003server.
> >
> > Have setup a maintenance plan but our primary database as well as the Main
> > database are not getting backed up among other things.
> >
> > Looking at the history log I see many errors:
> >
> > Check data and index linkage errors out with the message "Repair statement
> > not processed. Database needs to be in single user mode"
> >
> > Backup transaction log "Backup can not be performed on this database.
> > This sub task is ignored"
> >
> > I don't even have error messages for the backups that aren't happening for
> > these 2.
> >
> > All the other databases, Northwind, model, msdb, and pubs are doing fine.
> >
> > What am I missing here?
>|||> Why do I need to remove the "attempt to repair minor problems"? Why is
> this
> option available if it only screws up the whole maintenance plan?
The same reason some laptops, and the spark plugs on '78 datsun b210s, were
prone to catching fire. Sadly, not every scenario is completely tested. At
least in this case the outcome is not potential death, but merely a
suggestion to uncheck a checkbox.
> Why does it say that database needs to be in single user mode'
Because for some repair options that's just what is required. You wouldn't
expect to talk on your cell phone while the tech is trying to fix it?
> How does one make a database into single user mode'
ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
> And your last statement is ? to me as well: "Don't attempt to backup
> the
> log of the databases in simple recovery model." What the heck does that
> mean'
That means if your database is in simple recovery mode, don't back up the
log. It's pretty pointless since the log is useless in this case.
> You didn't address why my databases are not even getting backed up nor
> why
> there is no error message as to why they're not getting backed up.
We don't know the answer to the question. We can merely suggest ways you
can avoid the errors. I think Tibor's suggestions are sound, and you should
try them instead of complaining...
A

Can't automate backup of a database

When I try and do a maintenace plan to backup all databases there is one tha
t
does not get backedup. All the others seem to work fine. So I try to make
a
new maintenance plan to backup only that database and when I try and create
the plan that database is not in the list of databases. I suppose that is
why it is not working. However I can right click the database and back it u
p
manually with no problems.
Anyone know why it does not show in the list of avaliable databases to be
backed up?
Thanks,
EdIs that a system database or user? If system which database is it?
"Ed R." wrote:

> When I try and do a maintenace plan to backup all databases there is one t
hat
> does not get backedup. All the others seem to work fine. So I try to mak
e a
> new maintenance plan to backup only that database and when I try and creat
e
> the plan that database is not in the list of databases. I suppose that is
> why it is not working. However I can right click the database and back it
up
> manually with no problems.
> Anyone know why it does not show in the list of avaliable databases to be
> backed up?
> Thanks,
> Ed|||It is a user database.
"Bob" wrote:
[vbcol=seagreen]
> Is that a system database or user? If system which database is it?
> "Ed R." wrote:
>|||Ed R. wrote:
> It is a user database.
> "Bob" wrote:
>
Is this a FULL backup or a transaction log backup? What recovery model
is the database using?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||The recovery model is simple. When I start a new maintenance task, I drag
the 'backup database task' to the right and then click on it to edit. By
default 'FULL' is selected and greyed out. Then when I try to select the
database from those avaliable it does not show up.
"Tracy McKibben" wrote:

> Ed R. wrote:
> Is this a FULL backup or a transaction log backup? What recovery model
> is the database using?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||Lines: 1
User-Agent: Thunderbird 1.5.0.5 (Windows/20060719)
MIME-Version: 1.0
In-Reply-To: <6BE17578-B64D-496D-96FE-4E4CCAD48E14@.microsoft.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: ncsgate.ncspearson.com 12.106.111.10
Xref: leafnode.mcse.ms microsoft.public.sqlserver.server:10199
Ed R. wrote:
> The recovery model is simple. When I start a new maintenance task, I drag
> the 'backup database task' to the right and then click on it to edit. By
> default 'FULL' is selected and greyed out. Then when I try to select the
> database from those avaliable it does not show up.
>
I guess I don't have any further suggestions... I don't use the
maintenance plan wizards, for this very reason. When one of them
doesn't work, you have no idea why.
You might be interested in this script that I use to do backups:
http://realsqlguy.com/serendipity/a...-Send-In-Backup!.html
It will automatically backup any database on your server, including
transaction logs if appropriate. Schedule it once and forget it.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||A user db doesn't show up in the list only when it's taken offline. Right
click on the database then go to Tasks and click on Bring online then you
should be able to see it.
Another thing, you can't create maintenance plans if you are not a sysadmin
on a server. If you have dbo rights on the database or you are backup
operator you can take the backup from Enterprise Manager but not using
maintenance plans.
If it's none of the above then someone else might need to jump on this issue
to help you out.
Bob
"Tracy McKibben" wrote:

> Ed R. wrote:
> I guess I don't have any further suggestions... I don't use the
> maintenance plan wizards, for this very reason. When one of them
> doesn't work, you have no idea why.
> You might be interested in this script that I use to do backups:
> http://realsqlguy.com/serendipity/a...-Send-In-Backup!.html
> It will automatically backup any database on your server, including
> transaction logs if appropriate. Schedule it once and forget it.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||I would guess it's related to whatever the state is of the
database. There are different database states and statuses
that will keep the database from being displayed in the
maintenance plan screen. Being that it's just the one
database for which a maintenance plan can't be created, I
would suspect it's related to whatever the state or the
other status values are for the database in sys.databases.
-Sue
On Wed, 23 Aug 2006 12:27:01 -0700, Bob
<Bob@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>A user db doesn't show up in the list only when it's taken offline. Right
>click on the database then go to Tasks and click on Bring online then you
>should be able to see it.
>Another thing, you can't create maintenance plans if you are not a sysadmin
>on a server. If you have dbo rights on the database or you are backup
>operator you can take the backup from Enterprise Manager but not using
>maintenance plans.
>If it's none of the above then someone else might need to jump on this issu
e
>to help you out.
>Bob
>"Tracy McKibben" wrote:
>sql

Can't automate backup of a database

When I try and do a maintenace plan to backup all databases there is one that
does not get backedup. All the others seem to work fine. So I try to make a
new maintenance plan to backup only that database and when I try and create
the plan that database is not in the list of databases. I suppose that is
why it is not working. However I can right click the database and back it up
manually with no problems.
Anyone know why it does not show in the list of avaliable databases to be
backed up?
Thanks,
EdIs that a system database or user? If system which database is it?
"Ed R." wrote:
> When I try and do a maintenace plan to backup all databases there is one that
> does not get backedup. All the others seem to work fine. So I try to make a
> new maintenance plan to backup only that database and when I try and create
> the plan that database is not in the list of databases. I suppose that is
> why it is not working. However I can right click the database and back it up
> manually with no problems.
> Anyone know why it does not show in the list of avaliable databases to be
> backed up?
> Thanks,
> Ed|||It is a user database.
"Bob" wrote:
> Is that a system database or user? If system which database is it?
> "Ed R." wrote:
> > When I try and do a maintenace plan to backup all databases there is one that
> > does not get backedup. All the others seem to work fine. So I try to make a
> > new maintenance plan to backup only that database and when I try and create
> > the plan that database is not in the list of databases. I suppose that is
> > why it is not working. However I can right click the database and back it up
> > manually with no problems.
> >
> > Anyone know why it does not show in the list of avaliable databases to be
> > backed up?
> >
> > Thanks,
> > Ed|||Ed R. wrote:
> It is a user database.
> "Bob" wrote:
>
Is this a FULL backup or a transaction log backup? What recovery model
is the database using?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||The recovery model is simple. When I start a new maintenance task, I drag
the 'backup database task' to the right and then click on it to edit. By
default 'FULL' is selected and greyed out. Then when I try to select the
database from those avaliable it does not show up.
"Tracy McKibben" wrote:
> Ed R. wrote:
> > It is a user database.
> >
> > "Bob" wrote:
> >
> Is this a FULL backup or a transaction log backup? What recovery model
> is the database using?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||Ed R. wrote:
> The recovery model is simple. When I start a new maintenance task, I drag
> the 'backup database task' to the right and then click on it to edit. By
> default 'FULL' is selected and greyed out. Then when I try to select the
> database from those avaliable it does not show up.
>
I guess I don't have any further suggestions... I don't use the
maintenance plan wizards, for this very reason. When one of them
doesn't work, you have no idea why.
You might be interested in this script that I use to do backups:
http://realsqlguy.com/serendipity/archives/11-Send-In-Backup!.html
It will automatically backup any database on your server, including
transaction logs if appropriate. Schedule it once and forget it.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||A user db doesn't show up in the list only when it's taken offline. Right
click on the database then go to Tasks and click on Bring online then you
should be able to see it.
Another thing, you can't create maintenance plans if you are not a sysadmin
on a server. If you have dbo rights on the database or you are backup
operator you can take the backup from Enterprise Manager but not using
maintenance plans.
If it's none of the above then someone else might need to jump on this issue
to help you out.
Bob
"Tracy McKibben" wrote:
> Ed R. wrote:
> > The recovery model is simple. When I start a new maintenance task, I drag
> > the 'backup database task' to the right and then click on it to edit. By
> > default 'FULL' is selected and greyed out. Then when I try to select the
> > database from those avaliable it does not show up.
> >
> I guess I don't have any further suggestions... I don't use the
> maintenance plan wizards, for this very reason. When one of them
> doesn't work, you have no idea why.
> You might be interested in this script that I use to do backups:
> http://realsqlguy.com/serendipity/archives/11-Send-In-Backup!.html
> It will automatically backup any database on your server, including
> transaction logs if appropriate. Schedule it once and forget it.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||I would guess it's related to whatever the state is of the
database. There are different database states and statuses
that will keep the database from being displayed in the
maintenance plan screen. Being that it's just the one
database for which a maintenance plan can't be created, I
would suspect it's related to whatever the state or the
other status values are for the database in sys.databases.
-Sue
On Wed, 23 Aug 2006 12:27:01 -0700, Bob
<Bob@.discussions.microsoft.com> wrote:
>A user db doesn't show up in the list only when it's taken offline. Right
>click on the database then go to Tasks and click on Bring online then you
>should be able to see it.
>Another thing, you can't create maintenance plans if you are not a sysadmin
>on a server. If you have dbo rights on the database or you are backup
>operator you can take the backup from Enterprise Manager but not using
>maintenance plans.
>If it's none of the above then someone else might need to jump on this issue
>to help you out.
>Bob
>"Tracy McKibben" wrote:
>> Ed R. wrote:
>> > The recovery model is simple. When I start a new maintenance task, I drag
>> > the 'backup database task' to the right and then click on it to edit. By
>> > default 'FULL' is selected and greyed out. Then when I try to select the
>> > database from those avaliable it does not show up.
>> >
>> I guess I don't have any further suggestions... I don't use the
>> maintenance plan wizards, for this very reason. When one of them
>> doesn't work, you have no idea why.
>> You might be interested in this script that I use to do backups:
>> http://realsqlguy.com/serendipity/archives/11-Send-In-Backup!.html
>> It will automatically backup any database on your server, including
>> transaction logs if appropriate. Schedule it once and forget it.
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com

Cannot specify 'dimension' for: Maintenance plan remove files older than

OS: Windows Server 2003 Standard Edition. MSDE 2000.

SQL Server Enterprise Manager Version 8.0.

The Maintenance Plan Wizard will not allow me to specify a value for the 'Remove Files...' I can enter a number but there is nothing in the drop down e.g. 3 'DAYS' perhaps?

Create then plan and then edit - yields the same result.

Any ideas on why there is no 'dimension' for Remove Files Older than ?

Appreciate any tips, ideas, etc.

Thank.s

Anybody have issues trying to specify a dimension for 'Remove Files...' either during Maintenance Wizard (create) or afterwards - edit properties?

The drop down - where you would expect a list of values like: day, week, month - is completely blank?

|||

I'm experiencing the exact same problem with my MSDE database. I cannot specify anything as to days, weeks, etc. It also will take other information, such as scheduling, directory, extension name, and actually create the jobs correctly, but then when you go back to the Maintenance Plan, you find the information has disappeared. If you try to modify any other aspect of the plan, you get an error, because this information is now missing. This is the only MSDE db I have, all the rest are standard or enterprise and work normally from the same Enterprise Manager.

I have not found any explanation or work-around for this problem.

Cannot specify 'dimension' for: Maintenance plan remove files older than

OS: Windows Server 2003 Standard Edition. MSDE 2000.

SQL Server Enterprise Manager Version 8.0.

The Maintenance Plan Wizard will not allow me to specify a value for the 'Remove Files...' I can enter a number but there is nothing in the drop down e.g. 3 'DAYS' perhaps?

Create then plan and then edit - yields the same result.

Any ideas on why there is no 'dimension' for Remove Files Older than ?

Appreciate any tips, ideas, etc.

Thank.s

Anybody have issues trying to specify a dimension for 'Remove Files...' either during Maintenance Wizard (create) or afterwards - edit properties?

The drop down - where you would expect a list of values like: day, week, month - is completely blank?

|||

I'm experiencing the exact same problem with my MSDE database. I cannot specify anything as to days, weeks, etc. It also will take other information, such as scheduling, directory, extension name, and actually create the jobs correctly, but then when you go back to the Maintenance Plan, you find the information has disappeared. If you try to modify any other aspect of the plan, you get an error, because this information is now missing. This is the only MSDE db I have, all the rest are standard or enterprise and work normally from the same Enterprise Manager.

I have not found any explanation or work-around for this problem.

Cannot show editor for this task in SQL 2005 Management Studio

Hi,

I have setup a db backup maintenance plan with the SQL 2005 Mgmt Studio tool. When I double-clicked the 'Back up Database Task' object I got the following error message. My SQL 2005 client has been patched with SQL 2005 SP2. Does someone know about this problem? Thanks - Joe

Cannot show editor for this task.

Additional information:

Value of '4/17/2007 12:00:00 AM' is not valid for Value'. Value should be between 'MinDate' and 'MaxDate'. Parameter name: Value(System.Windows.Forms)

I haven't heard of this one before. Please file a defect report for this issue at http://connect.microsoft.com/sqlserver. Issues reported on the Connect site go directly into our internal issue tracking system so we don't lose any information. Be sure to note whether the maintenance plan existed before your updated to SP2 or if the plan is new.

Thanks,

Steve

|||I'm getting this as well. Did you get an idea when this might be fixed.|||I'm getting this also. Did you get an idea when this might be fixed.|||

hello I have got the same problem, but in spanish.

The mantenience plan was created before updating to SP2, and now I can't edit the BackUp task.

I tryed diferent MSSMS Clients with the same server, and it always fails, so I assume this is from the server.

Error: (spanish)

No se puede mostrar el editor para esta tarea.

Información adicional:

El valor de '02/05/2007 0:00:00' no es válido para 'Value'. 'Value' debería estar entre 'MinDate' y 'MaxDate'.

Nombre del parámetro: Value (System.Windows.Forms)

|||

Strangely on my side, the day after having this error, everything was ok.

Did not apply any new service pack or correction att all.

Carl

|||

Solved same way, yesterday I created again one of the tasks, and today I can edit all them.

Summary: Solution, wait until tomorrow.

|||

Hello everyone,

Thanks for reporting this issue. The problem is that once the expiration date is reached, the UI cannot be displayed because the date value is earlier than what is allowed. While a solution is being worked out and will be available in a major release, a few work-arounds have been identified:

1. If no action is taken, on the day after the original expiration date, the "backup set will expire" option will be reset to a date that is 14 days from the current date and the task will be editable as usual.

2. If editing the task is required on the day it was set to expire, then one of the following two solutions can be used:

Solution A: The new task can be created in place of the existing task.

Solution B: The Backup Database task can be made editable by changing the “Backup set will expire” property in BIDS and importing the package back to the Maintenance Plan designer. Here are the steps to follow:

Edit your Maintenance Plan in the Server Business Intelligence Development Studio (BIDS)

· Find SQL Server Business Intelligence Development Studio on the Start menu under Microsoft SQL Server 2005.

· From the File menu, create a New Project of type “Integration Services Project”.

· To import your Maintenance Plan into your new project, in the Solution Explorer window, right click on the ‘SSIS Packages’ folder and choose the ‘Add Existing Package’ option.

· In the “Add Copy of Existing Package” dialog

o Set the Package Location to SQL Server

o Provide authentication for your server.

o In the Package Path field , click the browse button to display the packages on your server.

o Navigate to ‘SSIS Package’, then ‘Maintenance Plans’.

o Select your Maintenance Plan and click OK. You see the Maintenance Plan that you just imported under the ‘SSIS Packages’ folder.

· Right-Click the Backup Database Task and select Properties.

· In the list of properties, navigate to the “ExpireDate” option and change the date to be at least one day ahead of the current date.

· Save the Package.

· In the Solution Explorer window, click on the name of the package. In the Properties panel, locate the Full Path to the package’s .dtsx file. Select the path and copy it to the clipboard.

· Exit the Business Intelligence Development Studio.

Re-import the Edited Package

· In Management Studio, click the Connect button and choose “Integration Services…” and connect to your server.

· Under Stored Packages open “MSDB”.

· Right click on the “Maintenance Plans” folder and select “Import Package”.

o Select “File System”.

o In the Package Path text box, paste in the previously copied .dtsx file path.

o Click in the Package Name text box to set the name, and click OK.

· When prompted to overwrite the existing package, say “Yes”.

Given these updates, the Backup Database Task should be editable from the Maintenance Plan designer.

Sorry for the inconvenience.

-Sirish

Cannot show editor for this task in SQL 2005 Management Studio

Hi,

I have setup a db backup maintenance plan with the SQL 2005 Mgmt Studio tool. When I double-clicked the 'Back up Database Task' object I got the following error message. My SQL 2005 client has been patched with SQL 2005 SP2. Does someone know about this problem? Thanks - Joe

Cannot show editor for this task.

Additional information:

Value of '4/17/2007 12:00:00 AM' is not valid for Value'. Value should be between 'MinDate' and 'MaxDate'. Parameter name: Value(System.Windows.Forms)

I haven't heard of this one before. Please file a defect report for this issue at http://connect.microsoft.com/sqlserver. Issues reported on the Connect site go directly into our internal issue tracking system so we don't lose any information. Be sure to note whether the maintenance plan existed before your updated to SP2 or if the plan is new.

Thanks,

Steve

|||I'm getting this as well. Did you get an idea when this might be fixed.|||I'm getting this also. Did you get an idea when this might be fixed.|||

hello I have got the same problem, but in spanish.

The mantenience plan was created before updating to SP2, and now I can't edit the BackUp task.

I tryed diferent MSSMS Clients with the same server, and it always fails, so I assume this is from the server.

Error: (spanish)

No se puede mostrar el editor para esta tarea.

Información adicional:

El valor de '02/05/2007 0:00:00' no es válido para 'Value'. 'Value' debería estar entre 'MinDate' y 'MaxDate'.

Nombre del parámetro: Value (System.Windows.Forms)

|||

Strangely on my side, the day after having this error, everything was ok.

Did not apply any new service pack or correction att all.

Carl

|||

Solved same way, yesterday I created again one of the tasks, and today I can edit all them.

Summary: Solution, wait until tomorrow.

|||

Hello everyone,

Thanks for reporting this issue. The problem is that once the expiration date is reached, the UI cannot be displayed because the date value is earlier than what is allowed. While a solution is being worked out and will be available in a major release, a few work-arounds have been identified:

1. If no action is taken, on the day after the original expiration date, the "backup set will expire" option will be reset to a date that is 14 days from the current date and the task will be editable as usual.

2. If editing the task is required on the day it was set to expire, then one of the following two solutions can be used:

Solution A: The new task can be created in place of the existing task.

Solution B: The Backup Database task can be made editable by changing the “Backup set will expire” property in BIDS and importing the package back to the Maintenance Plan designer. Here are the steps to follow:

Edit your Maintenance Plan in the Server Business Intelligence Development Studio (BIDS)

· Find SQL Server Business Intelligence Development Studio on the Start menu under Microsoft SQL Server 2005.

· From the File menu, create a New Project of type “Integration Services Project”.

· To import your Maintenance Plan into your new project, in the Solution Explorer window, right click on the ‘SSIS Packages’ folder and choose the ‘Add Existing Package’ option.

· In the “Add Copy of Existing Package” dialog

o Set the Package Location to SQL Server

o Provide authentication for your server.

o In the Package Path field , click the browse button to display the packages on your server.

o Navigate to ‘SSIS Package’, then ‘Maintenance Plans’.

o Select your Maintenance Plan and click OK. You see the Maintenance Plan that you just imported under the ‘SSIS Packages’ folder.

· Right-Click the Backup Database Task and select Properties.

· In the list of properties, navigate to the “ExpireDate” option and change the date to be at least one day ahead of the current date.

· Save the Package.

· In the Solution Explorer window, click on the name of the package. In the Properties panel, locate the Full Path to the package’s .dtsx file. Select the path and copy it to the clipboard.

· Exit the Business Intelligence Development Studio.

Re-import the Edited Package

· In Management Studio, click the Connect button and choose “Integration Services…” and connect to your server.

· Under Stored Packages open “MSDB”.

· Right click on the “Maintenance Plans” folder and select “Import Package”.

o Select “File System”.

o In the Package Path text box, paste in the previously copied .dtsx file path.

o Click in the Package Name text box to set the name, and click OK.

· When prompted to overwrite the existing package, say “Yes”.

Given these updates, the Backup Database Task should be editable from the Maintenance Plan designer.

Sorry for the inconvenience.

-Sirish

Cannot show editor for this task in SQL 2005 Management Studio

Hi,

I have setup a db backup maintenance plan with the SQL 2005 Mgmt Studio tool. When I double-clicked the 'Back up Database Task' object I got the following error message. My SQL 2005 client has been patched with SQL 2005 SP2. Does someone know about this problem? Thanks - Joe

Cannot show editor for this task.

Additional information:

Value of '4/17/2007 12:00:00 AM' is not valid for Value'. Value should be between 'MinDate' and 'MaxDate'. Parameter name: Value(System.Windows.Forms)

I haven't heard of this one before. Please file a defect report for this issue at http://connect.microsoft.com/sqlserver. Issues reported on the Connect site go directly into our internal issue tracking system so we don't lose any information. Be sure to note whether the maintenance plan existed before your updated to SP2 or if the plan is new.

Thanks,

Steve

|||I'm getting this as well. Did you get an idea when this might be fixed.|||I'm getting this also. Did you get an idea when this might be fixed.|||

hello I have got the same problem, but in spanish.

The mantenience plan was created before updating to SP2, and now I can't edit the BackUp task.

I tryed diferent MSSMS Clients with the same server, and it always fails, so I assume this is from the server.

Error: (spanish)

No se puede mostrar el editor para esta tarea.

Información adicional:

El valor de '02/05/2007 0:00:00' no es válido para 'Value'. 'Value' debería estar entre 'MinDate' y 'MaxDate'.

Nombre del parámetro: Value (System.Windows.Forms)

|||

Strangely on my side, the day after having this error, everything was ok.

Did not apply any new service pack or correction att all.

Carl

|||

Solved same way, yesterday I created again one of the tasks, and today I can edit all them.

Summary: Solution, wait until tomorrow.

|||

Hello everyone,

Thanks for reporting this issue. The problem is that once the expiration date is reached, the UI cannot be displayed because the date value is earlier than what is allowed. While a solution is being worked out and will be available in a major release, a few work-arounds have been identified:

1. If no action is taken, on the day after the original expiration date, the "backup set will expire" option will be reset to a date that is 14 days from the current date and the task will be editable as usual.

2. If editing the task is required on the day it was set to expire, then one of the following two solutions can be used:

Solution A: The new task can be created in place of the existing task.

Solution B: The Backup Database task can be made editable by changing the “Backup set will expire” property in BIDS and importing the package back to the Maintenance Plan designer. Here are the steps to follow:

Edit your Maintenance Plan in the Server Business Intelligence Development Studio (BIDS)

· Find SQL Server Business Intelligence Development Studio on the Start menu under Microsoft SQL Server 2005.

· From the File menu, create a New Project of type “Integration Services Project”.

· To import your Maintenance Plan into your new project, in the Solution Explorer window, right click on the ‘SSIS Packages’ folder and choose the ‘Add Existing Package’ option.

· In the “Add Copy of Existing Package” dialog

o Set the Package Location to SQL Server

o Provide authentication for your server.

o In the Package Path field , click the browse button to display the packages on your server.

o Navigate to ‘SSIS Package’, then ‘Maintenance Plans’.

o Select your Maintenance Plan and click OK. You see the Maintenance Plan that you just imported under the ‘SSIS Packages’ folder.

· Right-Click the Backup Database Task and select Properties.

· In the list of properties, navigate to the “ExpireDate” option and change the date to be at least one day ahead of the current date.

· Save the Package.

· In the Solution Explorer window, click on the name of the package. In the Properties panel, locate the Full Path to the package’s .dtsx file. Select the path and copy it to the clipboard.

· Exit the Business Intelligence Development Studio.

Re-import the Edited Package

· In Management Studio, click the Connect button and choose “Integration Services…” and connect to your server.

· Under Stored Packages open “MSDB”.

· Right click on the “Maintenance Plans” folder and select “Import Package”.

o Select “File System”.

o In the Package Path text box, paste in the previously copied .dtsx file path.

o Click in the Package Name text box to set the name, and click OK.

· When prompted to overwrite the existing package, say “Yes”.

Given these updates, the Backup Database Task should be editable from the Maintenance Plan designer.

Sorry for the inconvenience.

-Sirish

|||

You can select the backup mainplan in the designer and press F4 to open the properties of the maintenanceplan in the designer.In the properties you can change the expiry date, this allows you to open the plan again for edit.

Cannot save my Maintenance plan

I use sa to login to sql 2005. I can save Maintenance plan using wizard, however, when I try to save maintenance plan using the maintenance plan design screen. I received error message "Apply to target server failed for job ...". When I check the job, there is no step in the job.

In the Maintenance wizard, there is no maintenance clean up task. I want to create a task to delete my backup file if it is older than certain days.

Any suggestions are welcome. Thanks in advance.

Hi

Dont use the wizard.

You can use the cleanup maintenacne task to cleanup backups files older than x days.

Cheers

|||

Only the wizard let me save my maintenance plan. I cannot save my maintenance plan if I use the design screen.

There is no Maintenance clean up task in the wizard.

|||

What error do you get when you save from designer ?

Gops Dwarak

|||

Oh I missed to see the error message in your mail.

This is the difference in behavior i noticed when created though wizard and through Designer

When you create maintenance plan with a name of job that is already existing through wizard , maintenance plan gets created successfully with a step added to the existing job. Expected behavior should have been to throw valid error saying "Job with this name already exists". When similar scenario is tried from Designer then error is thrown mentioning "Create failed for job 'job name' ". I have opened a bug to be fixed for next release.

Can you check whether job with plan name exists before creating the plan through designer ?

Also, this issue looks like more of SQL Server Agent issue. Can you look at the agent log file(through log viewer) when you try to perform this operation and see what message is logged in there. That may help to narrow down the issue

|||

I checked my sql server log and agent log. Both had no error message recorded.

I noticed if there is job name existed, the Designer thrown error 'Create failed for job...'. I manually deleted the job and maintenance and created from fresh. I got error message 'Apply to target server failed for job 'job name''. Acturally the maintenance plan is saved and job is saved. However, there is no job steps in the job.

I don't think it is SQL agent issue since I have run the job yet. The error occurred at the save process. I guess maybe a security issue. I am using sa to login and sa is dbo of msdb. I can use the wizard to save with no problem.

Thanks for any suggestions.

|||Any suggestions? Thanks.|||

Hi renching

I am afraid i canoot help you much more.

My experience with the new maintenance plans are that they suck.

When i try upgrade 2000 plans to 2005 they just give me errors, i hate the wizard it is nowhere near as user friendly as easy as in 2000.

Basically all i can advise is to do what i did, redo all plans from scratch in 2005.

But you say you have to use the wizard so i cannot help you there cos the wizard cant do what i want it to do, so i avoid it.

As far as exisitng jobs are concerned , i know i had to use sp_delete_maintenance_plan and sp_delete_maintenance_plan_job to properly clear out exisitng jobs and plans.\

Good luck.

Thanx

|||

Thanks. I wish the error message gave more information. Our application is built on SQL 2005. We have no conversions. All jobs are new. I got a lot problem with SQL 2005.

Thanks anyway. I hope someone will help me out here.

|||My SSIS is installed, however, it is disabled. Will this cause the problem?|||Try logging to SQL Server Management Studio on the local server as 'sa' with a server name of '.', then create, schedule, and save the new maintenence plan.|||

renching wrote:

My SSIS is installed, however, it is disabled. Will this cause the problem?

We shouldn't have a dependency on the SSIS service. Try installing SQL Server 2005 Service Pack 2.

http://www.microsoft.com/sql/sp2.mspx

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

Cannot save my Maintenance plan

I use sa to login to sql 2005. I can save Maintenance plan using wizard, however, when I try to save maintenance plan using the maintenance plan design screen. I received error message "Apply to target server failed for job ...". When I check the job, there is no step in the job.

In the Maintenance wizard, there is no maintenance clean up task. I want to create a task to delete my backup file if it is older than certain days.

Any suggestions are welcome. Thanks in advance.

Hi

Dont use the wizard.

You can use the cleanup maintenacne task to cleanup backups files older than x days.

Cheers

|||

Only the wizard let me save my maintenance plan. I cannot save my maintenance plan if I use the design screen.

There is no Maintenance clean up task in the wizard.

|||

What error do you get when you save from designer ?

Gops Dwarak

|||

Oh I missed to see the error message in your mail.

This is the difference in behavior i noticed when created though wizard and through Designer

When you create maintenance plan with a name of job that is already existing through wizard , maintenance plan gets created successfully with a step added to the existing job. Expected behavior should have been to throw valid error saying "Job with this name already exists". When similar scenario is tried from Designer then error is thrown mentioning "Create failed for job 'job name' ". I have opened a bug to be fixed for next release.

Can you check whether job with plan name exists before creating the plan through designer ?

Also, this issue looks like more of SQL Server Agent issue. Can you look at the agent log file(through log viewer) when you try to perform this operation and see what message is logged in there. That may help to narrow down the issue

|||

I checked my sql server log and agent log. Both had no error message recorded.

I noticed if there is job name existed, the Designer thrown error 'Create failed for job...'. I manually deleted the job and maintenance and created from fresh. I got error message 'Apply to target server failed for job 'job name''. Acturally the maintenance plan is saved and job is saved. However, there is no job steps in the job.

I don't think it is SQL agent issue since I have run the job yet. The error occurred at the save process. I guess maybe a security issue. I am using sa to login and sa is dbo of msdb. I can use the wizard to save with no problem.

Thanks for any suggestions.

|||Any suggestions? Thanks.|||

Hi renching

I am afraid i canoot help you much more.

My experience with the new maintenance plans are that they suck.

When i try upgrade 2000 plans to 2005 they just give me errors, i hate the wizard it is nowhere near as user friendly as easy as in 2000.

Basically all i can advise is to do what i did, redo all plans from scratch in 2005.

But you say you have to use the wizard so i cannot help you there cos the wizard cant do what i want it to do, so i avoid it.

As far as exisitng jobs are concerned , i know i had to use sp_delete_maintenance_plan and sp_delete_maintenance_plan_job to properly clear out exisitng jobs and plans.\

Good luck.

Thanx

|||

Thanks. I wish the error message gave more information. Our application is built on SQL 2005. We have no conversions. All jobs are new. I got a lot problem with SQL 2005.

Thanks anyway. I hope someone will help me out here.

|||My SSIS is installed, however, it is disabled. Will this cause the problem?|||Try logging to SQL Server Management Studio on the local server as 'sa' with a server name of '.', then create, schedule, and save the new maintenence plan.|||

renching wrote:

My SSIS is installed, however, it is disabled. Will this cause the problem?

We shouldn't have a dependency on the SSIS service. Try installing SQL Server 2005 Service Pack 2.

http://www.microsoft.com/sql/sp2.mspx

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

Cannot save maintenance plans

I am running SQL 2005 standard SP2 and I cannot save maintenance plans. Using the wizard I create the plan but it always fails with the following error messages:

An exception occurred while executing a Transact_SQL statement or batch

Invalid column name 'from_msx'.

Invalid column name 'has_targets'. (Microsoft SQL Server , Error: 207)

Invalid column name 'msx_job_id'. (Microsoft SQL Server, Error: 207)

Thanks

Todd

The column names you mention were added in SP2. This indicates that there is a mix of SP2 and earlier functionality involved in this problem.

Bring all elements up to SP2. If you have done so already, inspect installler logs to see if sp2 setup ran without error.

Good luck,

jkh

Cannot save Maintenance plan

I use sa to login to sql 2005. I can save Maintenance plan using wizard. However, when I try to save maintenance plan using the maintenance plan design screen, I received error message "Apply to target server failed for job ...". When I check the job, there is no step in the job.

In the Maintenance wizard, there is no maintenance clean up task. I want to create a task to delete my backup file if it is older than certain days.

Any suggestions are welcome. Thanks in advance.

have you considered using the script task?|||

Although Maintenance Plans are built on the SSIS infrastrcucture, they do have some aspects that we do not cover. Best to start in the SQL Server Tools forum. I'll move this thread for now.

Thanks

Donald