Databases, logins and jobs on standby server

I have restored my production server master database to the standby server.
I then ran a series of scripts I found at KB246133:
http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q246133#4
I found that my standby server showed itself as a remote server. I created
linked servers between standby and production; I executed sp_dropserver
'(production server name)' and then sp_addserver (standby server name).
SP_dropserver resulted in a message that said that logins existed for this
server. When I look in the sysservers table on the standby server I have
two entries: one for the production server and one for the standby server.
I've also created a backup device on both the production and standby server
which are the same. The backup file from production is copied to standby so
that the restore can be done on the standby server.
My scripts to copy the backup file and restore the database work fine from
QA on the standby server. However, when I try to execute them as part of a
job, I get failures. The step which copies the database over says:
Could not relay results of procedure 'CopyDatabase_DukeAccount' from remote
server 'NCNSV1010'. [SQLSTATE 42000] (Error 7221) [SQLSTATE 01000] (Error
7312). The step failed.
I am confused why SQL thinks that NCNSV1010 is the remote server since it is
actually the local server that this job is executing on. Additionally, the
backup file IS actually copied over successfully.
The step which restores the database to the standby server, which is the
local server, from the backup device says:
RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013)
Cannot open backup device
'd:\mssql\backup\dukeaccount\DukeAccount_Full.bak'. Device error or device
off-line. See the SQL Server error log for more details. [SQLSTATE 42000]
(Error 3201). The step failed.
However, when I open the backup device to view contents, the just copied
backup file is visible in it.
Both servers are running W2K SP4, SQL2K SP2 (with security patch) Standard
Edition.
Does anyone have any idea what is wrong here? Do I need to drop the logins
for the (production server name) that exist on the standby server? If I do
that, will I then have to run the process again to transfer the logins? I
can't seem to drop the production server from the sysservers table if they
have logins. I could manually drop it from the table, but I don't know what
repurcussions that may have.
I need to figure this out because I must automate this process to run daily,
then the T-Logs many times a day, and I'll be doing this for quite a few
databases on these machines.
Thanks in advance.
Deborah> I found that my standby server showed itself as a remote server. I
created
> linked servers between standby and production; I executed sp_dropserver
> '(production server name)' and then sp_addserver (standby server name).
Seems you forgot to specify the ,local option to sp_addserver.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Deborah Bohannon" <dbohannon@.nationalDONOTSENDHEREcareanetwork.com> wrote
in message news:eb2bk2FsDHA.1884@.TK2MSFTNGP10.phx.gbl...
> I have restored my production server master database to the standby
server.
> I then ran a series of scripts I found at KB246133:
> http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q246133#4
> I found that my standby server showed itself as a remote server. I
created
> linked servers between standby and production; I executed sp_dropserver
> '(production server name)' and then sp_addserver (standby server name).
> SP_dropserver resulted in a message that said that logins existed for this
> server. When I look in the sysservers table on the standby server I have
> two entries: one for the production server and one for the standby
server.
> I've also created a backup device on both the production and standby
server
> which are the same. The backup file from production is copied to standby
so
> that the restore can be done on the standby server.
> My scripts to copy the backup file and restore the database work fine from
> QA on the standby server. However, when I try to execute them as part of
a
> job, I get failures. The step which copies the database over says:
> Could not relay results of procedure 'CopyDatabase_DukeAccount' from
remote
> server 'NCNSV1010'. [SQLSTATE 42000] (Error 7221) [SQLSTATE 01000]
(Error
> 7312). The step failed.
> I am confused why SQL thinks that NCNSV1010 is the remote server since it
is
> actually the local server that this job is executing on. Additionally,
the
> backup file IS actually copied over successfully.
> The step which restores the database to the standby server, which is the
> local server, from the backup device says:
> RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013)
> Cannot open backup device
> 'd:\mssql\backup\dukeaccount\DukeAccount_Full.bak'. Device error or device
> off-line. See the SQL Server error log for more details. [SQLSTATE 42000]
> (Error 3201). The step failed.
> However, when I open the backup device to view contents, the just copied
> backup file is visible in it.
> Both servers are running W2K SP4, SQL2K SP2 (with security patch) Standard
> Edition.
> Does anyone have any idea what is wrong here? Do I need to drop the
logins
> for the (production server name) that exist on the standby server? If I
do
> that, will I then have to run the process again to transfer the logins? I
> can't seem to drop the production server from the sysservers table if they
> have logins. I could manually drop it from the table, but I don't know
what
> repurcussions that may have.
> I need to figure this out because I must automate this process to run
daily,
> then the T-Logs many times a day, and I'll be doing this for quite a few
> databases on these machines.
> Thanks in advance.
> Deborah
>
>|||Tibor,
You are absolutely right, I hadn't specified local.
Thank you!
Deborah
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:Om179$YsDHA.1996@.TK2MSFTNGP09.phx.gbl...
> > I found that my standby server showed itself as a remote server. I
> created
> > linked servers between standby and production; I executed sp_dropserver
> > '(production server name)' and then sp_addserver (standby server name).
> Seems you forgot to specify the ,local option to sp_addserver.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Deborah Bohannon" <dbohannon@.nationalDONOTSENDHEREcareanetwork.com> wrote
> in message news:eb2bk2FsDHA.1884@.TK2MSFTNGP10.phx.gbl...
> > I have restored my production server master database to the standby
> server.
> > I then ran a series of scripts I found at KB246133:
> > http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q246133#4
> >
> > I found that my standby server showed itself as a remote server. I
> created
> > linked servers between standby and production; I executed sp_dropserver
> > '(production server name)' and then sp_addserver (standby server name).
> > SP_dropserver resulted in a message that said that logins existed for
this
> > server. When I look in the sysservers table on the standby server I
have
> > two entries: one for the production server and one for the standby
> server.
> >
> > I've also created a backup device on both the production and standby
> server
> > which are the same. The backup file from production is copied to
standby
> so
> > that the restore can be done on the standby server.
> >
> > My scripts to copy the backup file and restore the database work fine
from
> > QA on the standby server. However, when I try to execute them as part
of
> a
> > job, I get failures. The step which copies the database over says:
> >
> > Could not relay results of procedure 'CopyDatabase_DukeAccount' from
> remote
> > server 'NCNSV1010'. [SQLSTATE 42000] (Error 7221) [SQLSTATE 01000]
> (Error
> > 7312). The step failed.
> >
> > I am confused why SQL thinks that NCNSV1010 is the remote server since
it
> is
> > actually the local server that this job is executing on. Additionally,
> the
> > backup file IS actually copied over successfully.
> >
> > The step which restores the database to the standby server, which is the
> > local server, from the backup device says:
> >
> > RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error
3013)
> > Cannot open backup device
> > 'd:\mssql\backup\dukeaccount\DukeAccount_Full.bak'. Device error or
device
> > off-line. See the SQL Server error log for more details. [SQLSTATE
42000]
> > (Error 3201). The step failed.
> >
> > However, when I open the backup device to view contents, the just copied
> > backup file is visible in it.
> >
> > Both servers are running W2K SP4, SQL2K SP2 (with security patch)
Standard
> > Edition.
> >
> > Does anyone have any idea what is wrong here? Do I need to drop the
> logins
> > for the (production server name) that exist on the standby server? If I
> do
> > that, will I then have to run the process again to transfer the logins?
I
> > can't seem to drop the production server from the sysservers table if
they
> > have logins. I could manually drop it from the table, but I don't know
> what
> > repurcussions that may have.
> >
> > I need to figure this out because I must automate this process to run
> daily,
> > then the T-Logs many times a day, and I'll be doing this for quite a few
> > databases on these machines.
> >
> > Thanks in advance.
> > Deborah
> >
> >
> >
>

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