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

Databases in read-only

We have a client with several databases in read-only mode. When we try and take the dbs out of read-only, we get the path to the mdf files may be incorrect, which they are not. SQL 2000, SP3a. Has anybody seen this? The files themselves are not read-only, I gave the network services account full control and still nothing. The issue started when the client lost power, lasst week, the dbs came up suspect. Detach/Attach brought them back up in single user.

Any hep is appreciated.

What was the exact sequence of events after the power failure last week? What were the error log errors when the databases came back as suspect? Why did you/they attempt to detach/attach?

Can you post the T-SQL you're using, and the errors you get back?

Thanks

|||It turned out to be a permissions issue. The account used to start sql server didn't have the correct permissions on the data/log/mssql directories. They had changed domains and I don't think they new exactly what they were doing. I suspect the account was a domain admin before the domain change.

I used detach/attach because sp_resetstatus didn't take them out of suspect mode.

Thanks

Database with Single user mode

Hi,
I have a problem, where in a particular database is in single user mode (after it was restored). Now somebody in the network has logged onto this database as "sa", because of which am not able to loggin, even though I know "sa" username/pwd. Is there a way of killing the connected user session, so that I can login? Also, would it be possible to find, from which machine it was logged on earlier?
Thanks very much
GYKGYK
The problems with letting people have SA access huh. If
someone is logged on using sa, you should try to find out
what they are doing, rather than just kill them. Or are
you one of those places where everyone knows and uses the
sa account?
Either using sp_who or viewing process info through
enterprise manager you can see Host Name, ie the PC they
are logged on from. How easy that is to resolve depends on
how accessable that info is in your company.
Regards
John|||Hi John,
Thanks for the response. This server is a development server, and unfortunately every user has sa access.
Now that the database is in single user mode, and somebody else has already logged in, I am not able to login. So there is no question of using sp_who system proc. I basically, want to know if there is any method by which one can login into a database with single user mode, when other use has already logged in.
Or let me frame my question like this..
what if a user has logged into a single user mode database and go home. Does this mean other users can't login at all? I guess there should be some alternative..
Thanks
GYK
-- John Bandettini wrote: --
GYK
The problems with letting people have SA access huh. If
someone is logged on using sa, you should try to find out
what they are doing, rather than just kill them. Or are
you one of those places where everyone knows and uses the
sa account?
Either using sp_who or viewing process info through
enterprise manager you can see Host Name, ie the PC they
are logged on from. How easy that is to resolve depends on
how accessable that info is in your company.
Regards
John|||The question is whether the server is in single user (-m) or the database.
If the server: You shouldn't run the server in this mode normally. Only when
restoring the master database.
If the database: Kick out the user (KILL).
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"GYK" <anonymous@.discussions.microsoft.com> wrote in message
news:427A3F1F-685C-4F42-8194-D7127D3454CF@.microsoft.com...
> Hi John,
> Thanks for the response. This server is a development server, and
unfortunately every user has sa access.
> Now that the database is in single user mode, and somebody else has
already logged in, I am not able to login. So there is no question of using
sp_who system proc. I basically, want to know if there is any method by
which one can login into a database with single user mode, when other use
has already logged in.
> Or let me frame my question like this..
> what if a user has logged into a single user mode database and go home.
Does this mean other users can't login at all? I guess there should be some
alternative..
> Thanks
> GYK
> -- John Bandettini wrote: --
> GYK
> The problems with letting people have SA access huh. If
> someone is logged on using sa, you should try to find out
> what they are doing, rather than just kill them. Or are
> you one of those places where everyone knows and uses the
> sa account?
> Either using sp_who or viewing process info through
> enterprise manager you can see Host Name, ie the PC they
> are logged on from. How easy that is to resolve depends on
> how accessable that info is in your company.
> Regards
> John
>|||You can still get the info, run sp_who from master
database or use enterprise manager. You are getting
process information for the entire server, you don't need
to be in the user database.
Have you tried to issue a kill? In enterprise manager
double click the process id, see if you get the option to
kill. An alternative might be to get your NT/Windows admin
to kill the network connection based on the host id.
Hope this helps
John