SQL 2005 SP2:
I have several Sharepoint databases, all in full recovery mode except the
large search database which I want to run in simple mode because of the
large amount of transactions and because data recovery is not needed.
The back up log task in SQL Server Managment Studio is said to automatically
exclude databases in simple recovery mode, but it doesen't - anyone having a
workaround?
Other users reported that this problem was introduced in SP2, see
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1336939&SiteID=17
Here's how I reproduce the error:
When I edit a maintenance plan which backs up transaction logs, I can select
"Database(s)":
At the top of the window it says after the yellow exclamation mark:
"Databases with simple recovery will be excluded". Here I select "All user
databases". (I want to use "All user databases" so that any Sharepoint
databases created in the future are automatically included in this task.)
Back at the "Back up Database Task", when using the button "View T-SQL" i
see that the database in simple mode is not included - as expected.
But when the task is run, it fails. That is, it does what it is expected
to - it backs up all logs except from the database in simple mode, but it
returns an error; where I expected it to run with out error.
When looking in the job log in MSSQL\LOG, it shows that the reason of the
error is that it tried to backup the log from the database in simple
recovery mode - which the GUI said was excluded from the task.
"Failed:(0) Database 'SharedServices1_Search_DB' will not be backed up
because it does not have its recovery model set to Full or BulkLogged."
Regards,
Peter
www.skov.comHi Peter, I have confirmed that this issue happens and have filed a Connect
item. Please vote and add any other pertinent information.
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=320914
"Peter" <pafpaf@.hotmail.com> wrote in message
news:urIlQygTIHA.536@.TK2MSFTNGP06.phx.gbl...
> SQL 2005 SP2:
> I have several Sharepoint databases, all in full recovery mode except the
> large search database which I want to run in simple mode because of the
> large amount of transactions and because data recovery is not needed.
> The back up log task in SQL Server Managment Studio is said to
> automatically exclude databases in simple recovery mode, but it doesen't -
> anyone having a workaround?
> Other users reported that this problem was introduced in SP2, see
> http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1336939&SiteID=17
> Here's how I reproduce the error:
> When I edit a maintenance plan which backs up transaction logs, I can
> select "Database(s)":
> At the top of the window it says after the yellow exclamation mark:
> "Databases with simple recovery will be excluded". Here I select "All user
> databases". (I want to use "All user databases" so that any Sharepoint
> databases created in the future are automatically included in this task.)
> Back at the "Back up Database Task", when using the button "View T-SQL" i
> see that the database in simple mode is not included - as expected.
> But when the task is run, it fails. That is, it does what it is expected
> to - it backs up all logs except from the database in simple mode, but it
> returns an error; where I expected it to run with out error.
> When looking in the job log in MSSQL\LOG, it shows that the reason of the
> error is that it tried to backup the log from the database in simple
> recovery mode - which the GUI said was excluded from the task.
> "Failed:(0) Database 'SharedServices1_Search_DB' will not be backed up
> because it does not have its recovery model set to Full or BulkLogged."
> Regards,
> Peter
> www.skov.com
>
>
>|||I also posted a related item:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=320918|||Good idea, I was actually thinking exactly the same today when I had the
problem. :-)
Peter
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23CaJ7%23hTIHA.4880@.TK2MSFTNGP03.phx.gbl...
>I also posted a related item:
> https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=320918
>
...it would be beneficial to be able to examine the T-SQL and possibly amend
or comment out the line(s) that are causing problems
