October 9, 2007 at 10:09 am
Hello,
we had a vendor install a database on mssql2005 but they required the db compatibility to be set to SQL Server 7.
I set up a maint plan to automate the nightly db backup process. It backs up all the databases (9.0 and 8.0) successfully, but fails to do so for the 7.0???
Is this a known issue? Any suggestions?
Error log snipit ...
NEW COMPONENT OUTPUT
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3042
Report was generated on "xxx".
Maintenance Plan: xxx_BKUP_ALLDB_DAILY_11PM
Duration: 00:00:23
Status: Warning: One or more tasks failed..
Details:
Back Up Database (Full) (xxx)
Backup Database on Local server connection
Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.
Databases: All databases
Type: Full
Append existing
Task start: 2007-10-08T23:00:01.
Task end: 2007-10-08T23:00:01.
Failed: (0) Database 'yyy' is not valid to be included in the maintenance plan.
Thanks.
Jeff
Many thanks. Jeff
October 9, 2007 at 3:50 pm
Looks like a bug. I'd report this to PSS. I'll ping some people, but not sure if they'll respond.
October 9, 2007 at 3:51 pm
Sent a note to a few people over at MS.
For a workaround, there are bunches of scripts here on the site that do backups of all databases.
October 10, 2007 at 7:39 am
This is a known issue and you need to create a job to run 7.0 compatible databases.
I'm not sure why, but this was a design decision to not handle 7.0 databases. It's possible there are some options in the dialog that weren't supported in 7.0, so it's set to skip things.
I'd recommend you look for a script here. We have some good ones that will perform the backups and handle things like offline/other databases, etc.
October 10, 2007 at 11:40 am
Thanks Steve. Much appreciated.
I did an extensive search over google, MS forums/MS bug fixes/etc ... and I only found 1 reference/posting to similar case on another forum … but the user had had no replies.
Here are the steps if you want to reproduce the MSSQL7 backup error:
1. Invoke either "New Maintenance Plan or "Maintenance Plan Wizard" option via SS Management Studio consol.
2. Give the Plan a name.
3. Define a schedule ie: single schedule/recurring/daily/11PM.
4. Next screen: Select Maintenance Tasks: included Backup Database (Full) and Maintenance Cleanup Task.
5. Next screen "Define Database Backup (Task) Full" > under Databases, from the drop down I selected "All databases". (Note: what is interesting here is that on other tests, if you select “These Databases” option instead to allow explicit db selection, all MSSQL7 databases are not listed/available for selection). Other options on this screen were: specified a PATH (local) for "Create a backup file for every database". Define a PATH and “bak” extension for option “Backup file extension”.
6. Next screen is Define Maintenance Cleanup Tasks: backup files; search folder and delete based on file extension; Delete files based on age … I set to 1 day.
If this is a known issue, have you heard if there plans to roll out a “fix” at MS or that MS will list in BOL that this is in fact a deprecated feature? In my case, the vendor is refusing to test/move his db from 7.0 to 8.0/9.0 compatibility unless MS specifically states the need too or that 7.0 interfere's with mssql2005 other features ie: maint plan.
Should I forward this or could you forward this on to the powers that be? Re: stats … does MS need to know that it affects a larger group of users before acting?
Could you provide any links to specific scripts in mind as a work around from this site? It would be greatly appreciated.
Many thanks again for your time and responses.
Jeff
Many thanks. Jeff
March 27, 2009 at 10:12 am
To revive an old thread, just to add this is still an issue.
I know people don't normally see 7.0 databases anymore, but here I am, seeing 20+ of them
I need to get by it with a custom script. Not sure why MSFT decides to skip 7.0 (since the command is exactly the same)
NEW COMPONENT OUTPUT
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3042
Report was generated on "SERVER".
Maintenance Plan: Backup
Duration: 00:00:04
Status: Warning: One or more tasks failed..
Details:
Back Up Database (Full)
Backup Database on Local server connection
Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.
Databases: All user databases
Type: Full
Append existing
Task start: 2009-03-27T11:48:13.
Task end: 2009-03-27T11:48:14.
Failed:(0) Database 'DB' is not valid to be included in the maintenance plan.
March 27, 2009 at 12:01 pm
Hello Jerry,
to my knowledge, this issue has not been resolved by Microsoft ... albeit, i haven't tried it with mssql2005's SP3 ... ?
my workaround:
a. i built a seperate Maint Plan/Job using the "Execute T-SQL Statement task"
b. then, i used following code:
BACKUP DATABASE [xxx] TO DISK = 'H:\xxx\databaseName.bak' WITH NOFORMAT, NOINIT, NAME = 'databaseName', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
hope this helps.
Jeff
Many thanks. Jeff
March 27, 2009 at 12:30 pm
It is not a bug.
The documentation specifically states that databases in 7.0 compatibility mode are not supported by SQL Server maintanance plans.
March 27, 2009 at 1:02 pm
Hi Michael,
do you have a referernce/link to that documentation? I would love to pass along to our vendor.
thanks. jeff
Many thanks. Jeff
March 27, 2009 at 5:27 pm
Jeff (3/27/2009)
Hi Michael,do you have a referernce/link to that documentation? I would love to pass along to our vendor.
thanks. jeff
SQL Server 2005 Books Online
http://msdn.microsoft.com/en-us/library/ms187658(SQL.90).aspx
Maintenance Plans
"...Maintenance plans only run against databases set to compatibility level 80 or higher. The maintenance plan designer in SQL Server Management Studio does not display databases set to compatibility level 70 or lower..."
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply