Change to behavior in SQL 2005 SP2?  
Author Message
dankc





PostPosted: Wed Feb 21 10:00:38 CST 2007 Top

SQL Server >> Change to behavior in SQL 2005 SP2?

I just applied SQL 2005 SP2 to our SQL 2005 server. We have a maintenance
plan that does a transaction log backup on all databases every hour. Prior
to SP2 (running build 2153), the maintenance plan would silently skip
databases in simple recovery mode. Immediately after applying the service
pack though, the next scheduled job reported an error for every database in
simple recovery mode.

Is this an expected change in behavior with SP2? I did not see anything in
the release notes about this.

SQL Server241  
 
 
Aaron





PostPosted: Wed Feb 21 10:00:38 CST 2007 Top

SQL Server >> Change to behavior in SQL 2005 SP2? I've heard various rumblings that you're better off re-creating you
maintenance plans under SP2. The underlying mechanics have been overhauled,
and that just seems like the safest route.




>I just applied SQL 2005 SP2 to our SQL 2005 server. We have a maintenance
>plan that does a transaction log backup on all databases every hour. Prior
>to SP2 (running build 2153), the maintenance plan would silently skip
>databases in simple recovery mode. Immediately after applying the service
>pack though, the next scheduled job reported an error for every database in
>simple recovery mode.
>
> Is this an expected change in behavior with SP2? I did not see anything
> in the release notes about this.
>


 
 
Michael





PostPosted: Wed Feb 21 10:04:44 CST 2007 Top

SQL Server >> Change to behavior in SQL 2005 SP2? I actually tried this, but it didn't seem to help. The plan is very simple:
one Backup Database task, set to transaction log and configured to backup
all user databases.



> I've heard various rumblings that you're better off re-creating you
> maintenance plans under SP2. The underlying mechanics have been
> overhauled, and that just seems like the safest route.
>
>


>>I just applied SQL 2005 SP2 to our SQL 2005 server. We have a maintenance
>>plan that does a transaction log backup on all databases every hour.
>>Prior to SP2 (running build 2153), the maintenance plan would silently
>>skip databases in simple recovery mode. Immediately after applying the
>>service pack though, the next scheduled job reported an error for every
>>database in simple recovery mode.
>>
>> Is this an expected change in behavior with SP2? I did not see anything
>> in the release notes about this.
>>
>
>


 
 
Chris





PostPosted: Thu Feb 22 10:32:13 CST 2007 Top

SQL Server >> Change to behavior in SQL 2005 SP2? Michael,

Could this fix be what changed this:-
http://support.microsoft.com/kb/921003/ ?

We found that you could not create a maintenance plan unless the DB was in
Full recovery mode. If you created a maintenance plan and then the DB was
changed to the Bulk-logged recovery mode the log backup job ran but did not
do anything. This patch fixed both situations and could have caused yours!

Chris



>I actually tried this, but it didn't seem to help. The plan is very
>simple: one Backup Database task, set to transaction log and configured to
>backup all user databases.
>


>> I've heard various rumblings that you're better off re-creating you
>> maintenance plans under SP2. The underlying mechanics have been
>> overhauled, and that just seems like the safest route.
>>
>>


>>>I just applied SQL 2005 SP2 to our SQL 2005 server. We have a
>>>maintenance plan that does a transaction log backup on all databases
>>>every hour. Prior to SP2 (running build 2153), the maintenance plan would
>>>silently skip databases in simple recovery mode. Immediately after
>>>applying the service pack though, the next scheduled job reported an
>>>error for every database in simple recovery mode.
>>>
>>> Is this an expected change in behavior with SP2? I did not see anything
>>> in the release notes about this.
>>>
>>
>>
>
>


 
 
Michael





PostPosted: Fri Feb 23 11:04:49 CST 2007 Top

SQL Server >> Change to behavior in SQL 2005 SP2? It does seem possible, although that referse to bulk-logged recovery mode
and not simple.

I suppose my only recourse is to call PSS and see if they have a response
other than "only backup databases in full recovery mode."

I prefer to select "All Databases" in the maintenance plan so I don't have
to remember to add new databases to the plan as they are added.




> Michael,
>
> Could this fix be what changed this:-
> http://support.microsoft.com/kb/921003/ ?
>
> We found that you could not create a maintenance plan unless the DB was in
> Full recovery mode. If you created a maintenance plan and then the DB was
> changed to the Bulk-logged recovery mode the log backup job ran but did
> not do anything. This patch fixed both situations and could have caused
> yours!
>
> Chris
>


>>I actually tried this, but it didn't seem to help. The plan is very
>>simple: one Backup Database task, set to transaction log and configured to
>>backup all user databases.
>>


>>> I've heard various rumblings that you're better off re-creating you
>>> maintenance plans under SP2. The underlying mechanics have been
>>> overhauled, and that just seems like the safest route.
>>>
>>>


>>>>I just applied SQL 2005 SP2 to our SQL 2005 server. We have a
>>>>maintenance plan that does a transaction log backup on all databases
>>>>every hour. Prior to SP2 (running build 2153), the maintenance plan
>>>>would silently skip databases in simple recovery mode. Immediately
>>>>after applying the service pack though, the next scheduled job reported
>>>>an error for every database in simple recovery mode.
>>>>
>>>> Is this an expected change in behavior with SP2? I did not see
>>>> anything in the release notes about this.
>>>>
>>>
>>>
>>
>>
>
>


 
 
Michael





PostPosted: Fri Feb 23 11:08:23 CST 2007 Top

SQL Server >> Change to behavior in SQL 2005 SP2? On second thought maybe I can write a script to only do BACKUP LOG on
databases in FULL recovery mode instead of relying on the maintenance plan
to generate the script.



> It does seem possible, although that referse to bulk-logged recovery mode
> and not simple.
>
> I suppose my only recourse is to call PSS and see if they have a response
> other than "only backup databases in full recovery mode."
>
> I prefer to select "All Databases" in the maintenance plan so I don't have
> to remember to add new databases to the plan as they are added.
>
>


>> Michael,
>>
>> Could this fix be what changed this:-
>> http://support.microsoft.com/kb/921003/ ?
>>
>> We found that you could not create a maintenance plan unless the DB was
>> in Full recovery mode. If you created a maintenance plan and then the DB
>> was changed to the Bulk-logged recovery mode the log backup job ran but
>> did not do anything. This patch fixed both situations and could have
>> caused yours!
>>
>> Chris
>>


>>>I actually tried this, but it didn't seem to help. The plan is very
>>>simple: one Backup Database task, set to transaction log and configured
>>>to backup all user databases.
>>>


>>>> I've heard various rumblings that you're better off re-creating you
>>>> maintenance plans under SP2. The underlying mechanics have been
>>>> overhauled, and that just seems like the safest route.
>>>>
>>>>


>>>>>I just applied SQL 2005 SP2 to our SQL 2005 server. We have a
>>>>>maintenance plan that does a transaction log backup on all databases
>>>>>every hour. Prior to SP2 (running build 2153), the maintenance plan
>>>>>would silently skip databases in simple recovery mode. Immediately
>>>>>after applying the service pack though, the next scheduled job reported
>>>>>an error for every database in simple recovery mode.
>>>>>
>>>>> Is this an expected change in behavior with SP2? I did not see
>>>>> anything in the release notes about this.
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>


 
 
Chris





PostPosted: Fri Feb 23 11:40:59 CST 2007 Top

SQL Server >> Change to behavior in SQL 2005 SP2? Michael,

The wizard still gives you the choice of All User databases, All System
database and All databases. I would not expect the system ones to change so
create 2 sets of plans, 1 to cover the system ones and one to cover the user
ones.

Chris



> It does seem possible, although that referse to bulk-logged recovery mode
> and not simple.
>
> I suppose my only recourse is to call PSS and see if they have a response
> other than "only backup databases in full recovery mode."
>
> I prefer to select "All Databases" in the maintenance plan so I don't have
> to remember to add new databases to the plan as they are added.
>
>


>> Michael,
>>
>> Could this fix be what changed this:-
>> http://support.microsoft.com/kb/921003/ ?
>>
>> We found that you could not create a maintenance plan unless the DB was
>> in Full recovery mode. If you created a maintenance plan and then the DB
>> was changed to the Bulk-logged recovery mode the log backup job ran but
>> did not do anything. This patch fixed both situations and could have
>> caused yours!
>>
>> Chris
>>


>>>I actually tried this, but it didn't seem to help. The plan is very
>>>simple: one Backup Database task, set to transaction log and configured
>>>to backup all user databases.
>>>


>>>> I've heard various rumblings that you're better off re-creating you
>>>> maintenance plans under SP2. The underlying mechanics have been
>>>> overhauled, and that just seems like the safest route.
>>>>
>>>>


>>>>>I just applied SQL 2005 SP2 to our SQL 2005 server. We have a
>>>>>maintenance plan that does a transaction log backup on all databases
>>>>>every hour. Prior to SP2 (running build 2153), the maintenance plan
>>>>>would silently skip databases in simple recovery mode. Immediately
>>>>>after applying the service pack though, the next scheduled job reported
>>>>>an error for every database in simple recovery mode.
>>>>>
>>>>> Is this an expected change in behavior with SP2? I did not see
>>>>> anything in the release notes about this.
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>


 
 
Tracy





PostPosted: Fri Feb 23 12:19:31 CST 2007 Top

SQL Server >> Change to behavior in SQL 2005 SP2? On Feb 23, 11:04 am, "Michael D'Angelo"

> It does seem possible, although that referse to bulk-logged recovery mode
> and not simple.
>
> I suppose my only recourse is to call PSS and see if they have a response
> other than "only backup databases in full recovery mode."
>
> I prefer to select "All Databases" in the maintenance plan so I don't have
> to remember to add new databases to the plan as they are added.
>

Have a look at this script of mine... It will automate all of your
backups, including the logs, and will pick up new databases
automatically...

http://realsqlguy.blogspot.com/2007/02/automating-database-backups.html


 
 
Michael





PostPosted: Mon Feb 26 14:15:36 CST 2007 Top

SQL Server >> Change to behavior in SQL 2005 SP2? Actually, even selecting "All user databases" causes the error. I was
hoping not to have to select specific databases.



> Michael,
>
> The wizard still gives you the choice of All User databases, All System
> database and All databases. I would not expect the system ones to change
> so create 2 sets of plans, 1 to cover the system ones and one to cover the
> user ones.
>
> Chris
>


>> It does seem possible, although that referse to bulk-logged recovery mode
>> and not simple.
>>
>> I suppose my only recourse is to call PSS and see if they have a response
>> other than "only backup databases in full recovery mode."
>>
>> I prefer to select "All Databases" in the maintenance plan so I don't
>> have to remember to add new databases to the plan as they are added.
>>
>>


>>> Michael,
>>>
>>> Could this fix be what changed this:-
>>> http://support.microsoft.com/kb/921003/ ?
>>>
>>> We found that you could not create a maintenance plan unless the DB was
>>> in Full recovery mode. If you created a maintenance plan and then the DB
>>> was changed to the Bulk-logged recovery mode the log backup job ran but
>>> did not do anything. This patch fixed both situations and could have
>>> caused yours!
>>>
>>> Chris
>>>


>>>>I actually tried this, but it didn't seem to help. The plan is very
>>>>simple: one Backup Database task, set to transaction log and configured
>>>>to backup all user databases.
>>>>


>>>>> I've heard various rumblings that you're better off re-creating you
>>>>> maintenance plans under SP2. The underlying mechanics have been
>>>>> overhauled, and that just seems like the safest route.
>>>>>
>>>>>


>>>>>>I just applied SQL 2005 SP2 to our SQL 2005 server. We have a
>>>>>>maintenance plan that does a transaction log backup on all databases
>>>>>>every hour. Prior to SP2 (running build 2153), the maintenance plan
>>>>>>would silently skip databases in simple recovery mode. Immediately
>>>>>>after applying the service pack though, the next scheduled job
>>>>>>reported an error for every database in simple recovery mode.
>>>>>>
>>>>>> Is this an expected change in behavior with SP2? I did not see
>>>>>> anything in the release notes about this.
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>


 
 
Michael





PostPosted: Mon Feb 26 14:15:51 CST 2007 Top

SQL Server >> Change to behavior in SQL 2005 SP2? Thanks I'll take a look!



> On Feb 23, 11:04 am, "Michael D'Angelo"

>> It does seem possible, although that referse to bulk-logged recovery mode
>> and not simple.
>>
>> I suppose my only recourse is to call PSS and see if they have a response
>> other than "only backup databases in full recovery mode."
>>
>> I prefer to select "All Databases" in the maintenance plan so I don't
>> have
>> to remember to add new databases to the plan as they are added.
>>
>
> Have a look at this script of mine... It will automate all of your
> backups, including the logs, and will pick up new databases
> automatically...
>
> http://realsqlguy.blogspot.com/2007/02/automating-database-backups.html
>
>