Put database in warm standby after changing it from read only?

We have a server at a disaster recovery site that was in warm standby. In
order for the server to be fully functional, we had to bring the databases
out of warm standby to install some client software that read the databases
of the standby server. While trying to put the databases back into warm
standby restoring the next log in sequence from the production server, I get
an error saying "Exclusive access could not be obtained because the database
is in use". However, the database is in single-user mode. When trying a
restore log command in SQL, I get an error "preceding restore operation did
not specify WITH NORECOVERY or WITH STANDBY. RESTORE LOG is terminating
abnormally.
Is there any way to get the database(s) back into warm standy mode without
having to set it up again from scratch?
Thanks,
RSNope. Once it is set to "normal" mode you have to restore a full backup
WITH STANDBY.
--
Keith
"RS" <rspen@.yahoo.com> wrote in message
news:%23y9Z2Y7eEHA.2764@.TK2MSFTNGP11.phx.gbl...
> We have a server at a disaster recovery site that was in warm standby. In
> order for the server to be fully functional, we had to bring the databases
> out of warm standby to install some client software that read the
databases
> of the standby server. While trying to put the databases back into warm
> standby restoring the next log in sequence from the production server, I
get
> an error saying "Exclusive access could not be obtained because the
database
> is in use". However, the database is in single-user mode. When trying a
> restore log command in SQL, I get an error "preceding restore operation
did
> not specify WITH NORECOVERY or WITH STANDBY. RESTORE LOG is terminating
> abnormally.
> Is there any way to get the database(s) back into warm standy mode without
> having to set it up again from scratch?
> Thanks,
> RS
>

Push replication between SQL2k and SQL2k

Hi,

I am wanting to set up replication between two SQLServer 2000 databases. DB1 has the data, and DB2 is where I would like to replicate to. I have set up the publisher and distributor on DB1, and have set up DB2 as the subscriber. The replication is set up as transactional.

When the agent runs, it fails with the following error details:
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

Where do I set the user that the agent should be using?

I think that it is also worth noting that the two computers are not on the same domain. There are no firewalls involved either.I Think it uses the SQL Server Agent Sevice Startup Account security

Right click on the Agent in EM & Make sure that the account has access to both Servers.

or

Go into the server Services and look at the SQL Agent Startup Account

There are other areas but try this till someone explains it better than I can|||Aha! I found the answer!

The agent at the distributor is trying to start the agent at the subscriber. The correct permissions must be given on the agent at the subscriber so that the agent at the distributor can execute the app. I am not exactly sure what it requires yet, but this is what I did:

1) Run the dcomcnfg application from the Run box.
2) Select the 'Microsoft SQL Server Replication Remote Dist Agent 8.0'
3) Open the properties of the agent.
4) Select the Security tab.
5) Use custom configurations for all settings in this tab. Add the NETWORK group to each configuration group and give the same level of access as the other accounts in the configuration.

6) Select the Identity tab.
7) Select 'This user' and provide a local machine user name that has permissions to run this app.

8) Click OK to save the changes.

The next problem is that if you are running 2000 with SP3, then the user provided by the agent will not be trusted and will give you another error. Error number 18452 or something similar.

To make the user trusted, you must execute the xp_sqlagent_proxy_account stored procedure on the subscriber server.

xp_sqlagent_proxy_account

{N'GET' | N'SET' | N'DEL ,

N'agent_domain_name', N'agent_username', N'agent_password'

}

You can find more documentation on this stored procedure in MSDN under SQL Server 2000 and Replication Security.

Purposely making mismatched SQL accounts on different databases

This summary is not available. Please click here to view the post.

purpose of master and tempdb databases

Hi all,
COuld you pls explain the purpose of master database and tempdb. Can anyone compare this with an oracle database.
Thanks and regards,
Retna
The system databases are well documented in Books Online. Below is a good start:
http://msdn.microsoft.com/library/de...r_da2_1lcx.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Retna" <anonymous@.discussions.microsoft.com> wrote in message
news:A6BD97A2-FE72-4801-B52C-4E9FA44D8DF6@.microsoft.com...
> Hi all,
> COuld you pls explain the purpose of master database and tempdb. Can anyone compare this with an oracle
database.
> Thanks and regards,
> Retna
|||Hi,
Add on to the article mentioned by Tiber , for comparison with Oracle,
SQL Server Oracle
-- --
Master Database System Table Space
Tempdb Temporary tablespace (Where all sort, group, temp
tables operations resides)
So each Oracle database will have a System Table space and Temp table space
during installation itself. This is same as
Master and Tempdb databases in SQL server
Thanks
Hari
MCDBA
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O0SFiQaMEHA.1556@.TK2MSFTNGP10.phx.gbl...
> The system databases are well documented in Books Online. Below is a good
start:
>
http://msdn.microsoft.com/library/de...us/architec/8_
ar_da2_1lcx.asp[vbcol=seagreen]
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Retna" <anonymous@.discussions.microsoft.com> wrote in message
> news:A6BD97A2-FE72-4801-B52C-4E9FA44D8DF6@.microsoft.com...
anyone compare this with an oracle
> database.
>
|||Thanks all.
|||You'll find a lot of info in Books Online and googling. Needless to say,
it's a bit more complicated than this, but...
master is used by SQL Server to store meta data assoicated with the server.
You COULD put user objects here but it is not normally a best practice.
tempdb is recreated from scratch each time the server starts. It's used to
store work tables created by SQL for processing a query and temp tables (#,
##, etc...) you create will go here.
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Retna" <anonymous@.discussions.microsoft.com> wrote in message
news:858D968F-7C80-4BA4-8E53-F3CF07B53AC1@.microsoft.com...
> Thanks all.
>
sql

purpose of master and tempdb databases

Hi all
COuld you pls explain the purpose of master database and tempdb. Can anyone compare this with an oracle database
Thanks and regards
RetnaThe system databases are well documented in Books Online. Below is a good start:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_1lcx.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Retna" <anonymous@.discussions.microsoft.com> wrote in message
news:A6BD97A2-FE72-4801-B52C-4E9FA44D8DF6@.microsoft.com...
> Hi all,
> COuld you pls explain the purpose of master database and tempdb. Can anyone compare this with an oracle
database.
> Thanks and regards,
> Retna|||Hi,
Add on to the article mentioned by Tiber , for comparison with Oracle,
SQL Server Oracle
-- --
Master Database System Table Space
Tempdb Temporary tablespace (Where all sort, group, temp
tables operations resides)
So each Oracle database will have a System Table space and Temp table space
during installation itself. This is same as
Master and Tempdb databases in SQL server
Thanks
Hari
MCDBA
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O0SFiQaMEHA.1556@.TK2MSFTNGP10.phx.gbl...
> The system databases are well documented in Books Online. Below is a good
start:
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_
ar_da2_1lcx.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Retna" <anonymous@.discussions.microsoft.com> wrote in message
> news:A6BD97A2-FE72-4801-B52C-4E9FA44D8DF6@.microsoft.com...
> > Hi all,
> > COuld you pls explain the purpose of master database and tempdb. Can
anyone compare this with an oracle
> database.
> > Thanks and regards,
> > Retna
>|||Thanks all|||You'll find a lot of info in Books Online and googling. Needless to say,
it's a bit more complicated than this, but...
master is used by SQL Server to store meta data assoicated with the server.
You COULD put user objects here but it is not normally a best practice.
tempdb is recreated from scratch each time the server starts. It's used to
store work tables created by SQL for processing a query and temp tables (#,
##, etc...) you create will go here.
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Retna" <anonymous@.discussions.microsoft.com> wrote in message
news:858D968F-7C80-4BA4-8E53-F3CF07B53AC1@.microsoft.com...
> Thanks all.
>

purpose of master and tempdb databases

Hi all,
COuld you pls explain the purpose of master database and tempdb. Can anyone
compare this with an oracle database.
Thanks and regards,
RetnaThe system databases are well documented in Books Online. Below is a good st
art:
http://msdn.microsoft.com/library/d...r />
_1lcx.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Retna" <anonymous@.discussions.microsoft.com> wrote in message
news:A6BD97A2-FE72-4801-B52C-4E9FA44D8DF6@.microsoft.com...
> Hi all,
> COuld you pls explain the purpose of master database and tempdb. Can anyone compa
re this with an oracle
database.
> Thanks and regards,
> Retna|||Hi,
Add on to the article mentioned by Tiber , for comparison with Oracle,
SQL Server Oracle
-- --
Master Database System Table Space
Tempdb Temporary tablespace (Where all sort, group, temp
tables operations resides)
So each Oracle database will have a System Table space and Temp table space
during installation itself. This is same as
Master and Tempdb databases in SQL server
Thanks
Hari
MCDBA
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O0SFiQaMEHA.1556@.TK2MSFTNGP10.phx.gbl...
> The system databases are well documented in Books Online. Below is a good
start:
>
http://msdn.microsoft.com/library/d...-us/architec/8_
ar_da2_1lcx.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Retna" <anonymous@.discussions.microsoft.com> wrote in message
> news:A6BD97A2-FE72-4801-B52C-4E9FA44D8DF6@.microsoft.com...
anyone compare this with an oracle[vbcol=seagreen]
> database.
>|||Thanks all.|||You'll find a lot of info in Books Online and googling. Needless to say,
it's a bit more complicated than this, but...
master is used by SQL Server to store meta data assoicated with the server.
You COULD put user objects here but it is not normally a best practice.
tempdb is recreated from scratch each time the server starts. It's used to
store work tables created by SQL for processing a query and temp tables (#,
##, etc...) you create will go here.
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Retna" <anonymous@.discussions.microsoft.com> wrote in message
news:858D968F-7C80-4BA4-8E53-F3CF07B53AC1@.microsoft.com...
> Thanks all.
>

pulling progress data

I'm trying to pull data from about 30 progress databases using DTS and
scheduling the jobs to run monthly. I'd like to pull data between
specific dates, but for some reason, I can't figure out how to filter
the data on the progress side.

I want to run a query that will pull all data from
prior-month/8/current-year
and
current-month/15/current-year

I'll also have to account for when it's january, make it december of
the year before.

Any ideas?
thanks,
M@.Well, I figured out part of it...

WHERE ("create-date" >
to_char(CONCAT(CONCAT(to_char("MONTH"(add_months("sysdate"(), - 1))),
'/8/'), LTRIM(to_char("YEAR"("sysdate"())),
' '))))

This whole nasty mess would give you 5/8/2006 if you ran it in June of
2006.

Now I have to figure out the other part of this mess is what the hell
happens when it's January :)

M@.

mattcushing@.gmail.com wrote:
> I'm trying to pull data from about 30 progress databases using DTS and
> scheduling the jobs to run monthly. I'd like to pull data between
> specific dates, but for some reason, I can't figure out how to filter
> the data on the progress side.
> I want to run a query that will pull all data from
> prior-month/8/current-year
> and
> current-month/15/current-year
> I'll also have to account for when it's january, make it december of
> the year before.
> Any ideas?
> thanks,
> M@.

Publishing database hangs on first article

When adding a databases to publications, the process gets stuck / goes into
endless loop on the first article of the database. This only happens on
databases that were previously replicated, others run through without the
slightest problem. We also checked the transactions issued with sp_who2 /
trace, it looks like the transaction goes into a loop of selecting and
updating. We have checked all the replication related system tables in an
attempt to properly remove any traces of previous publication, but to no
avail. Can anyone help? Thanks in advance
What build of SQL Server are you on? run select @.@.version to determine
this. Is it a build greater than 8.00.818?
You can try manually removing and recreating your publication as per KB:
How to manually remove a replication in SQL Server 2000 - ID: 324401
(available at http://support.microsoft.com)
Fany Vargas
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their
Microsoft software to better protect against viruses and security
vulnerabilities. The easiest way to do this is to visit the following
websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx

Publisher and Subscriber with different service packs

We have several SQL Server 2000 databases servers. We would like to do
snapshot replication to a central server. The central server would be sp3a
and most of the servers are sp3a. We still have a couple of machines at sp2
and will be upgrading shortly.. My question is: Can snapshot replication
work between Servers with different service packs. Thanks... in advance
tiny
Snapshot replication is the most forgiving replication type when it comes to
differencing service pack levels.
You should have no problems.
"Tiny13" <Tiny13@.discussions.microsoft.com> wrote in message
news:A8607009-C020-4125-B01E-EF2DB5F334E7@.microsoft.com...
> We have several SQL Server 2000 databases servers. We would like to do
> snapshot replication to a central server. The central server would be
> sp3a
> and most of the servers are sp3a. We still have a couple of machines at
> sp2
> and will be upgrading shortly.. My question is: Can snapshot replication
> work between Servers with different service packs. Thanks... in advance
> tiny

Publication not enabled to use an independent agent

I've created a snapshot replication between 2 databases on 2 different
servers to run once a day. The snapshot has worked but when the
distribution job runs it fails within a few seconds. I've looked at the
job history and the following error message is recorded:
Publication 'EmailDB' is not enabled to use an independent agent. The
step failed.
I think this may be because I've defined the publication so that
anonymous pull subscriptions are not allowed. If not does anyone know
what the problem could be. If it is how do I alter the publication so
that they are allowed - the option doesn't appear to be changeable with
the publication properties window in EM.
TIA
Laurence Breeze
drop your subscriptions and try this
sp_changepublication 'pubs4','independent_agent','true'
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Laurence Breeze" <laurence_breeze@.yahoo.co.uk> wrote in message
news:4289AD77.5070305@.yahoo.co.uk...
> I've created a snapshot replication between 2 databases on 2 different
> servers to run once a day. The snapshot has worked but when the
> distribution job runs it fails within a few seconds. I've looked at the
> job history and the following error message is recorded:
> Publication 'EmailDB' is not enabled to use an independent agent. The
> step failed.
> I think this may be because I've defined the publication so that
> anonymous pull subscriptions are not allowed. If not does anyone know
> what the problem could be. If it is how do I alter the publication so
> that they are allowed - the option doesn't appear to be changeable with
> the publication properties window in EM.
> TIA
> Laurence Breeze
>

Public role granted to user in master database.

In one of our sql server 2000 databases, there is an
application user that has public granted to it in the
master database. Can this be a security issue? There are
no permissions granted on objects just the public role.
I'm awaiting feedback from the vendor to find out why the
user is there. I just want to make sure this user cannot
do any harm to the master database.Not sure what you are saying. Every database has a role named public, you ca
nnot remove this...
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Erin" <Erin_Regotti@.grainger.com> wrote in message news:a28701c3eb2d$f399e270$a601280a@.phx.gbl...

quote:

---

> In one of our sql server 2000 databases, there is an
> application user that has public granted to it in the
> master database. Can this be a security issue? There are
> no permissions granted on objects just the public role.
> I'm awaiting feedback from the vendor to find out why the
> user is there. I just want to make sure this user cannot
> do any harm to the master database.

---

|||And everybody has public access to the master database (via the guest
account, I believe).
Russell Fields
"Erin" <Erin_Regotti@.grainger.com> wrote in message
news:a28701c3eb2d$f399e270$a601280a@.phx.gbl...

quote:

---

> In one of our sql server 2000 databases, there is an
> application user that has public granted to it in the
> master database. Can this be a security issue? There are
> no permissions granted on objects just the public role.
> I'm awaiting feedback from the vendor to find out why the
> user is there. I just want to make sure this user cannot
> do any harm to the master database.

---

Public role granted to user in master database.

In one of our sql server 2000 databases, there is an
application user that has public granted to it in the
master database. Can this be a security issue? There are
no permissions granted on objects just the public role.
I'm awaiting feedback from the vendor to find out why the
user is there. I just want to make sure this user cannot
do any harm to the master database.Not sure what you are saying. Every database has a role named public, you cannot remove this...
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Erin" <Erin_Regotti@.grainger.com> wrote in message news:a28701c3eb2d$f399e270$a601280a@.phx.gbl...
> In one of our sql server 2000 databases, there is an
> application user that has public granted to it in the
> master database. Can this be a security issue? There are
> no permissions granted on objects just the public role.
> I'm awaiting feedback from the vendor to find out why the
> user is there. I just want to make sure this user cannot
> do any harm to the master database.|||And everybody has public access to the master database (via the guest
account, I believe).
Russell Fields
"Erin" <Erin_Regotti@.grainger.com> wrote in message
news:a28701c3eb2d$f399e270$a601280a@.phx.gbl...
> In one of our sql server 2000 databases, there is an
> application user that has public granted to it in the
> master database. Can this be a security issue? There are
> no permissions granted on objects just the public role.
> I'm awaiting feedback from the vendor to find out why the
> user is there. I just want to make sure this user cannot
> do any harm to the master database.

Public role and guest security concern in SQL 2000 SP4

Hi all,

I have setup a new SQL 2000 SP4 and internal auditor query about revoke permission from Public role and remove guest from all databases.

1. Can I revoke all default permissions (select on system tables in all DBs) from "Public" role? I am concern any error after such action.

2. I found that guest account in DB -- master, tempdb and msdb. According to Microsoft documents. The account should not remove and can't from master and tempdb. How about msdb?

Thanks,

Regards,

Edwin

1. You may of course get errors from users trying to access system tables without being specifically granted access. You can resolve these issues by granting access to those users.

2. guest cannot be actually dropped - it can only be denied access to the database (hasdbaccess will show as 0). The msdb database is used by replication and SQL agent, among other components. You should check on the respective forums to see the impact of disabling guest access to the msdb database: SQL Server Replication and SQL Server Tools General.

Thanks
Laurentiu

|||

Thanks Laurentiu.

1. I have a search on web, some people mentioned that if revoke the default privilege from "Public" role on DBs (inclu. select system table, execute stored proc.). Microsoft wouldn't support my issues on this SQL in future. Is it true?

Thanks!

Edwin

|||

Microsoft may not be able to provide support if the user directly modifies system tables. Since all you are doing is changing permissions, your product will still be supported. Keep in mind that some features may require "public" to function properly. I can't think of any off the top of my head so once you come up with this locked down configuration you will have to do some verification testing to ensure that the functionality that you require works.

|||

As long as the changes that you make are made using documented features (revoking a permission using REVOKE statement is a documented feature), then you don't have to worry about invalidating your support options.

You should avoid making changes using undocumented techniques, such as, for example, directly updating system tables.

Thanks
Laurentiu