Databases with simple recovery is NOT excluded when backing up logs

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

Databases with simple recovery is NOT excluded when backing up logs

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...36939&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.
"Failed0) 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/SQLSe...=3209
14
"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...36939&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.
> "Failed0) 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/SQLSe...=3209
18|||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 mess
age
news:%23CaJ7%23hTIHA.4880@.TK2MSFTNGP03.phx.gbl...
>I also posted a related item:
> https://connect.microsoft.com/SQLSe...=32
0918
>
...it would be beneficial to be able to examine the T-SQL and possibly amen
d
or comment out the line(s) that are causing problems

'DATABASEPROPERTYEX' is not a recognized function name

Hello,
i create one script that give me the output with the
Databases that have the full recovery model. In the middle
of the script i needed to use this
function 'DATABASEPROPERTYEX' but when i run the script on
Servers that haves SQL 7.0, sql generate the following
error.
Server: Msg 195, Level 15, State 10, Line 13
'DATABASEPROPERTYEX' is not a recognized function name.
I know that this function doesn't exists on SQL 7.0,
instead of this one SQL 7.0 has the 'DATABASEPROPERTY'
function but i cant get what i want.
Help... please.
Best Regards
You could make use of sp_dboption in SQL Server 7.0. See SQL Server Books
Online for more information.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:55d901c42cfd$f15257b0$a101280a@.phx.gbl...
Hello,
i create one script that give me the output with the
Databases that have the full recovery model. In the middle
of the script i needed to use this
function 'DATABASEPROPERTYEX' but when i run the script on
Servers that haves SQL 7.0, sql generate the following
error.
Server: Msg 195, Level 15, State 10, Line 13
'DATABASEPROPERTYEX' is not a recognized function name.
I know that this function doesn't exists on SQL 7.0,
instead of this one SQL 7.0 has the 'DATABASEPROPERTY'
function but i cant get what i want.
Help... please.
Best Regards

'DATABASEPROPERTYEX' is not a recognized function name

Hello,
i create one script that give me the output with the
Databases that have the full recovery model. In the middle
of the script i needed to use this
function 'DATABASEPROPERTYEX' but when i run the script on
Servers that haves SQL 7.0, sql generate the following
error.
Server: Msg 195, Level 15, State 10, Line 13
'DATABASEPROPERTYEX' is not a recognized function name.
I know that this function doesn't exists on SQL 7.0,
instead of this one SQL 7.0 has the 'DATABASEPROPERTY'
function but i cant get what i want.
Help... please.
Best RegardsYou could make use of sp_dboption in SQL Server 7.0. See SQL Server Books
Online for more information.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:55d901c42cfd$f15257b0$a101280a@.phx.gbl...
Hello,
i create one script that give me the output with the
Databases that have the full recovery model. In the middle
of the script i needed to use this
function 'DATABASEPROPERTYEX' but when i run the script on
Servers that haves SQL 7.0, sql generate the following
error.
Server: Msg 195, Level 15, State 10, Line 13
'DATABASEPROPERTYEX' is not a recognized function name.
I know that this function doesn't exists on SQL 7.0,
instead of this one SQL 7.0 has the 'DATABASEPROPERTY'
function but i cant get what i want.
Help... please.
Best Regards

'DATABASEPROPERTYEX' is not a recognized function name

Hello,
i create one script that give me the output with the
Databases that have the full recovery model. In the middle
of the script i needed to use this
function 'DATABASEPROPERTYEX' but when i run the script on
Servers that haves SQL 7.0, sql generate the following
error.
Server: Msg 195, Level 15, State 10, Line 13
'DATABASEPROPERTYEX' is not a recognized function name.
I know that this function doesn't exists on SQL 7.0,
instead of this one SQL 7.0 has the 'DATABASEPROPERTY'
function but i cant get what i want.
Help... please.
Best RegardsYou could make use of sp_dboption in SQL Server 7.0. See SQL Server Books
Online for more information.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:55d901c42cfd$f15257b0$a101280a@.phx.gbl...
Hello,
i create one script that give me the output with the
Databases that have the full recovery model. In the middle
of the script i needed to use this
function 'DATABASEPROPERTYEX' but when i run the script on
Servers that haves SQL 7.0, sql generate the following
error.
Server: Msg 195, Level 15, State 10, Line 13
'DATABASEPROPERTYEX' is not a recognized function name.
I know that this function doesn't exists on SQL 7.0,
instead of this one SQL 7.0 has the 'DATABASEPROPERTY'
function but i cant get what i want.
Help... please.
Best Regards

Database/Log placement

I know that Database 101 required that you place your log files and
your databases on different phyiscal disks for the purpose of disaster
recovery. Does this still hold true if a SAN (EMC) is in the picture?
A client site configured their initial log file drive too
small, and they have proposed moving the logs to the database drive
which has plenty of free space.
They are aware that they will be losing some performance as the log
file drive was on its own spindle for the sequential writes.
TIA,
DaveAn EMC device will be able to protect you against physical failure of the
drives or LUN but can do nothing about the health of the filesystem on which
the database files reside. A corrupted filesystem can get you as badly as a
failed LUN. Having your logs on a different filesystem allows you to
restore to the point of failure by using a BACKUP LOG WITH NO_TRUNCATE if
the LUN with the physical database files goes away. That said, we live in
the real world and many of my client have to keep data files and log files
on the same filesystem. Just keep good backups.
Christian
"Dave" <dderocha@.hotmail.com> wrote in message
news:7e4fb551.0402050821.76b46122@.posting.google.com...
> I know that Database 101 required that you place your log files and
> your databases on different phyiscal disks for the purpose of disaster
> recovery. Does this still hold true if a SAN (EMC) is in the picture?
> A client site configured their initial log file drive too
> small, and they have proposed moving the logs to the database drive
> which has plenty of free space.
> They are aware that they will be losing some performance as the log
> file drive was on its own spindle for the sequential writes.
> TIA,
> Dave