Logshipping question

Win 2K Adv Server SQL2K sp3a
I have implemented logshipping from server A to server B monitor
server C following the instructions
Everything works fine, every 15 minutes the Backup, Copy and Restore
jobs run
But the destination database is in Loading io Read Only
What is wrong ?
TIA
Jan Waumans<jw@.jw.com> wrote in message
news:8ef6601rfk0kg6i68vb539q1d6ap43op3v@.
4ax.com...
> Win 2K Adv Server SQL2K sp3a
> I have implemented logshipping from server A to server B monitor
> server C following the instructions
> Everything works fine, every 15 minutes the Backup, Copy and Restore
> jobs run
> But the destination database is in Loading io Read Only
> What is wrong ?
When the logs are restored onto server B they are restored WITH NO RECOVERY.
In other words the logs are not rolled forward to make the database
consistent. It is waiting for the next set of logs. If the database was
not read-only then if people made changes to the Server B database you would
not be able to read the next shipped logs.
If you want to have an updatable copy of your database where changes are
replicated every 15 minutes you might wnat to take a peek at replication.|||Bob,
I use replication since 6.0
The reason I want to go to logshipping is that replication is too
difficult to maintain for recovery sites (no schema changes except
with the unusable additional column in 2K mode, only 1 logreader per
published database, ...)
Since in Win2K you can restore a DB in read-only state allowing
additional log restores, I figure this is what logshipping should do
How the destination db could be usable anyway?
Our setup as disaster recovery does not need an access to the
destination db's in normal circumstances, but we need to be able to
switch over in case of emergency
MS clearly states that logshipping is a better solution for disaster
recovery than replication
I must do something wrong
JW
On Thu, 25 Mar 2004 20:42:59 -0000, "Bob Simms"
<bob_simms@.somewhere.com> wrote:

><jw@.jw.com> wrote in message
> news:8ef6601rfk0kg6i68vb539q1d6ap43op3v@.
4ax.com...
>When the logs are restored onto server B they are restored WITH NO RECOVERY
.
>In other words the logs are not rolled forward to make the database
>consistent. It is waiting for the next set of logs. If the database was
>not read-only then if people made changes to the Server B database you woul
d
>not be able to read the next shipped logs.
>If you want to have an updatable copy of your database where changes are
>replicated every 15 minutes you might wnat to take a peek at replication.
>|||log shipping is better than replication in case you want to have the second
server for stand-by. You can read DBs on stand-by server as long as the log
restore is not running. In your case there is nothing wrong when you see
the DB in single user mode.
<jw@.jw.com> wrote in message
news:2dh660tsgo13lv90e53dquf1c03jcren8m@.
4ax.com...
> Bob,
> I use replication since 6.0
> The reason I want to go to logshipping is that replication is too
> difficult to maintain for recovery sites (no schema changes except
> with the unusable additional column in 2K mode, only 1 logreader per
> published database, ...)
> Since in Win2K you can restore a DB in read-only state allowing
> additional log restores, I figure this is what logshipping should do
> How the destination db could be usable anyway?
> Our setup as disaster recovery does not need an access to the
> destination db's in normal circumstances, but we need to be able to
> switch over in case of emergency
> MS clearly states that logshipping is a better solution for disaster
> recovery than replication
> I must do something wrong
> JW
>
> On Thu, 25 Mar 2004 20:42:59 -0000, "Bob Simms"
> <bob_simms@.somewhere.com> wrote:
>
RECOVERY.
would
>|||Study the docs in books on line referring to Log Shipping... There is a
procedure you must go through to make the log ship databases Primary, in the
case of failure.. There is a DTS job and some exports involved... You'd be
well served to test this and ensure you have a good plan...
But until you run recovery on the secondary ( part of the process) it will
show as loading...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<jw@.jw.com> wrote in message
news:8ef6601rfk0kg6i68vb539q1d6ap43op3v@.
4ax.com...
> Win 2K Adv Server SQL2K sp3a
> I have implemented logshipping from server A to server B monitor
> server C following the instructions
> Everything works fine, every 15 minutes the Backup, Copy and Restore
> jobs run
> But the destination database is in Loading io Read Only
> What is wrong ?
> TIA
> Jan Waumans

LogReader unable to connect to the publisher

I have a SQL2K box acting as Distributor and SQL7 box as a publisher. The
LogReader on the Distributor is unable to connect to the Publisher. I get the
following Msg from the Log Reader:
The process could not execute 'sp_MSadd_repl_commands27hp' on
'SQL2KServerName\InstanceName'
The two servers are on different untrusted domains. I am however able to
connect both ways through QA.
SQL2K box has SP4 and SQL7 box has SP3. Don't want to go to SP4 on SQL7
unless necessary and because it is a production server.
can you uncheck the Administrative Link Password requirement. Connect to
your distributor in EM, select Tools, Replication, Configure Distributor,
Publishers and Subscribers, and click on the Publishers tab. On the problem
publisher click on the browse button to the right of the publisher and you
will find this option at the bottom of the dialog box.
"Adam" <Adam@.discussions.microsoft.com> wrote in message
news:74DD5392-6B69-43B7-A591-4D80901F97D8@.microsoft.com...
>I have a SQL2K box acting as Distributor and SQL7 box as a publisher. The
> LogReader on the Distributor is unable to connect to the Publisher. I get
> the
> following Msg from the Log Reader:
> The process could not execute 'sp_MSadd_repl_commands27hp' on
> 'SQL2KServerName\InstanceName'
> The two servers are on different untrusted domains. I am however able to
> connect both ways through QA.
> SQL2K box has SP4 and SQL7 box has SP3. Don't want to go to SP4 on SQL7
> unless necessary and because it is a production server.
|||Thank you Hiary for the response. It turned out that the Distributor was
unable to resolve Publisher's IP address by name. Somehow, replacing the IP
address in the parameters for the log reader didn't work either. However,
after modifying the host file (for IP to name lookup) for distributor
resolved the problem.
-A
"Hilary Cotter" wrote:

> can you uncheck the Administrative Link Password requirement. Connect to
> your distributor in EM, select Tools, Replication, Configure Distributor,
> Publishers and Subscribers, and click on the Publishers tab. On the problem
> publisher click on the browse button to the right of the publisher and you
> will find this option at the bottom of the dialog box.
> "Adam" <Adam@.discussions.microsoft.com> wrote in message
> news:74DD5392-6B69-43B7-A591-4D80901F97D8@.microsoft.com...
>
>

Logins, Users, Roles, Schemas

Hi everyone,
I have just migrating my database From SQL2K to SQL2005. Users, Roles
and Schemas were all migrated. No issues there. Logins, on the other
hand, did not get migrated. Thus, I believe making Users, Roles, Schemas
orphans, correct?
If I re-create the login and assigning the login to the database, it
will give an error saying that the user already exists. So, I delete the
User, then it prompts me that I have a schema associated with it, so I
delete the schema, then create the user. Roles have not been touched.
Must I also update the roles? and I'm not sure what schemas are in
comparison to SQL 2000.
Please advise of a better way to deal with users, logins, roles and
schemas.
Thanks,
Audrey
*** Sent via Developersdex http://www.codecomments.com ***Perhaps these resources will help you.
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission Issues
When a Database Is Moved Between SQL Servers
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Audrey Ng" <odd26uk@.yahoo.co.uk> wrote in message
news:egfRrmTzGHA.4580@.TK2MSFTNGP05.phx.gbl...
> Hi everyone,
> I have just migrating my database From SQL2K to SQL2005. Users, Roles
> and Schemas were all migrated. No issues there. Logins, on the other
> hand, did not get migrated. Thus, I believe making Users, Roles, Schemas
> orphans, correct?
> If I re-create the login and assigning the login to the database, it
> will give an error saying that the user already exists. So, I delete the
> User, then it prompts me that I have a schema associated with it, so I
> delete the schema, then create the user. Roles have not been touched.
> Must I also update the roles? and I'm not sure what schemas are in
> comparison to SQL 2000.
> Please advise of a better way to deal with users, logins, roles and
> schemas.
> Thanks,
> Audrey
>
> *** Sent via Developersdex http://www.codecomments.com ***|||Thanks for these articles. I have a slightly different issue. We moved a SQL
2000 database from server "A" to server "B" and there are now orphaned
users. They are actually old admins, etc., that we don't even want any more.
How do you delete these from a database? Is it as simple as just deleting
the logins from the database?
I haven't found anything on this topic and I would like some assurance that
it should work without croaking the database.
Thanks,
Ray
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23afucvTzGHA.2208@.TK2MSFTNGP03.phx.gbl...
> Perhaps these resources will help you.
> http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
> Passwords Between SQL Servers
> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
> Restore
> http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to users
> http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
> Errors After Restoring Dump
> http://www.support.microsoft.com/?id=240872 How to Resolve Permission
> Issues When a Database Is Moved Between SQL Servers
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Audrey Ng" <odd26uk@.yahoo.co.uk> wrote in message
> news:egfRrmTzGHA.4580@.TK2MSFTNGP05.phx.gbl...
>|||Yes, if they are truly 'orphans', you can just delete them.
However, if they 'own' any objects, the deletion will fail. You will then
have to find those objects, and change the ownership to [dbo] before try
ing
the deletion again.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Ray" <no@.repliesplease.com> wrote in message
news:%23EFW%23FczGHA.3704@.TK2MSFTNGP02.phx.gbl...
> Thanks for these articles. I have a slightly different issue. We moved a
> SQL 2000 database from server "A" to server "B" and there are now orphaned
> users. They are actually old admins, etc., that we don't even want any
> more.
> How do you delete these from a database? Is it as simple as just deleting
> the logins from the database?
> I haven't found anything on this topic and I would like some assurance
> that it should work without croaking the database.
> Thanks,
> Ray
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:%23afucvTzGHA.2208@.TK2MSFTNGP03.phx.gbl...
>|||Do sp_change_users_login 'report' to get a list of orphans before you decide
to do anything.
Also, check out this kb for more info.
http://support.microsoft.com/kb/274188
-oj
"Ray" <no@.repliesplease.com> wrote in message
news:%23EFW%23FczGHA.3704@.TK2MSFTNGP02.phx.gbl...
> Thanks for these articles. I have a slightly different issue. We moved a
> SQL 2000 database from server "A" to server "B" and there are now orphaned
> users. They are actually old admins, etc., that we don't even want any
> more.
> How do you delete these from a database? Is it as simple as just deleting
> the logins from the database?
> I haven't found anything on this topic and I would like some assurance
> that it should work without croaking the database.
> Thanks,
> Ray
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:%23afucvTzGHA.2208@.TK2MSFTNGP03.phx.gbl...
>|||Thanks to the both of you.
Ray
"oj" <nospam_ojngo@.home.com> wrote in message
news:eSR$4wgzGHA.4648@.TK2MSFTNGP04.phx.gbl...
> Do sp_change_users_login 'report' to get a list of orphans before you
> decide to do anything.
> Also, check out this kb for more info.
> http://support.microsoft.com/kb/274188
>
> --
> -oj
>
> "Ray" <no@.repliesplease.com> wrote in message
> news:%23EFW%23FczGHA.3704@.TK2MSFTNGP02.phx.gbl...
>

logins

Hi,
How do I transfer all logins from sql2k server to a new sql2005 box?
ThanksYou can use many approaches:
- Copy Database Wizard
- A SQL 2000 DTS package
- A SQL 2005 SSIS package
Also you can use the following script
http://solidqualitylearning.com/blo...02/25/1618.aspx
Regards
Antonio Soto
Solid Quality Learning
http://www.sqlu.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"mecn" <mecn2002@.yahoo.com> escribi en el mensaje
news:umAey80UGHA.1688@.TK2MSFTNGP11.phx.gbl...
> Hi,
> How do I transfer all logins from sql2k server to a new sql2005 box?
> Thanks
>|||Thanks, All I need to do is transfer to master.logins table
"Antonio Soto" <antoniosotorodriguez@.gmail.com> wrote in message
news:uwyt2H3UGHA.4300@.TK2MSFTNGP14.phx.gbl...
> You can use many approaches:
> - Copy Database Wizard
> - A SQL 2000 DTS package
> - A SQL 2005 SSIS package
> Also you can use the following script
> http://solidqualitylearning.com/blo...02/25/1618.aspx
> Regards
>
> --
> Antonio Soto
> Solid Quality Learning
> http://www.sqlu.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
> "mecn" <mecn2002@.yahoo.com> escribi en el mensaje
> news:umAey80UGHA.1688@.TK2MSFTNGP11.phx.gbl...
>|||thanks
"Antonio Soto" <antoniosotorodriguez@.gmail.com> wrote in message
news:uwyt2H3UGHA.4300@.TK2MSFTNGP14.phx.gbl...
> You can use many approaches:
> - Copy Database Wizard
> - A SQL 2000 DTS package
> - A SQL 2005 SSIS package
> Also you can use the following script
> http://solidqualitylearning.com/blo...02/25/1618.aspx
> Regards
>
> --
> Antonio Soto
> Solid Quality Learning
> http://www.sqlu.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
> "mecn" <mecn2002@.yahoo.com> escribi en el mensaje
> news:umAey80UGHA.1688@.TK2MSFTNGP11.phx.gbl...
>

login probs after restore

sql2k sp3
About once a month Im asked to take a backup from
production and restore it in a development environment.
(these are in the same domain) After the restore, I can
see the users in the db, but those users cant see the db
from tools like Query Analyzer. I have to actually drop/
recreate the users from the db and then reset the perms to
get them going. Not a big deal for me, but there has got
to be a better way? Any ideas?
TIA, ChrisRHi,
Yes, the better way is to execute "sp_change_users_login" procedure from the
restored database.
See the details of sp_change_users_login procedure in books online.
Thanks
Hari
MCDBA
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:1ab6401c44ef9$173fb1d0$a501280a@.phx.gbl...
> sql2k sp3
> About once a month Im asked to take a backup from
> production and restore it in a development environment.
> (these are in the same domain) After the restore, I can
> see the users in the db, but those users cant see the db
> from tools like Query Analyzer. I have to actually drop/
> recreate the users from the db and then reset the perms to
> get them going. Not a big deal for me, but there has got
> to be a better way? Any ideas?
> TIA, ChrisR|||Chris,
the SID in master.dbo.sysxlogins needs to map to the SID
in yourdb.dbo.sysusers and there is a sp to help you remap
them in a backup/restore scenario:
sp_change_users_login
Full details are in BOL, but this procedure will mean you
can retain the users and permissions.
HTH,
Paul Ibison|||Thanks Paul. Do you know what I use for the WINNT guys?
>--Original Message--
>Chris,
>the SID in master.dbo.sysxlogins needs to map to the SID
>in yourdb.dbo.sysusers and there is a sp to help you
remap
>them in a backup/restore scenario:
>sp_change_users_login
>Full details are in BOL, but this procedure will mean you
>can retain the users and permissions.
>HTH,
>Paul Ibison
>.
>|||Chris,
as far as I understand these should be OK as the SID won't
change as you're still in the same domain. You can use
sp_validatelogins to be sure.
Also, Rand from MS has previously posted up this link for
mapping both types of logins :
http://support.microsoft.com/default.aspx?kbid=298897
Regards,
Paul Ibison|||Also, search KB and read about sp_help_revlogin.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:1ab6401c44ef9$173fb1d0$a501280a@.phx.gbl...
> sql2k sp3
> About once a month Im asked to take a backup from
> production and restore it in a development environment.
> (these are in the same domain) After the restore, I can
> see the users in the db, but those users cant see the db
> from tools like Query Analyzer. I have to actually drop/
> recreate the users from the db and then reset the perms to
> get them going. Not a big deal for me, but there has got
> to be a better way? Any ideas?
> TIA, ChrisR

login probs after restore

sql2k sp3
About once a month Im asked to take a backup from
production and restore it in a development environment.
(these are in the same domain) After the restore, I can
see the users in the db, but those users cant see the db
from tools like Query Analyzer. I have to actually drop/
recreate the users from the db and then reset the perms to
get them going. Not a big deal for me, but there has got
to be a better way? Any ideas?
TIA, ChrisR
Hi,
Yes, the better way is to execute "sp_change_users_login" procedure from the
restored database.
See the details of sp_change_users_login procedure in books online.
Thanks
Hari
MCDBA
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:1ab6401c44ef9$173fb1d0$a501280a@.phx.gbl...
> sql2k sp3
> About once a month Im asked to take a backup from
> production and restore it in a development environment.
> (these are in the same domain) After the restore, I can
> see the users in the db, but those users cant see the db
> from tools like Query Analyzer. I have to actually drop/
> recreate the users from the db and then reset the perms to
> get them going. Not a big deal for me, but there has got
> to be a better way? Any ideas?
> TIA, ChrisR
|||Also, search KB and read about sp_help_revlogin.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:1ab6401c44ef9$173fb1d0$a501280a@.phx.gbl...
> sql2k sp3
> About once a month Im asked to take a backup from
> production and restore it in a development environment.
> (these are in the same domain) After the restore, I can
> see the users in the db, but those users cant see the db
> from tools like Query Analyzer. I have to actually drop/
> recreate the users from the db and then reset the perms to
> get them going. Not a big deal for me, but there has got
> to be a better way? Any ideas?
> TIA, ChrisR

login probs after restore

sql2k sp3
About once a month Im asked to take a backup from
production and restore it in a development environment.
(these are in the same domain) After the restore, I can
see the users in the db, but those users cant see the db
from tools like Query Analyzer. I have to actually drop/
recreate the users from the db and then reset the perms to
get them going. Not a big deal for me, but there has got
to be a better way? Any ideas?
TIA, ChrisRHi,
Yes, the better way is to execute "sp_change_users_login" procedure from the
restored database.
See the details of sp_change_users_login procedure in books online.
Thanks
Hari
MCDBA
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:1ab6401c44ef9$173fb1d0$a501280a@.phx
.gbl...
> sql2k sp3
> About once a month Im asked to take a backup from
> production and restore it in a development environment.
> (these are in the same domain) After the restore, I can
> see the users in the db, but those users cant see the db
> from tools like Query Analyzer. I have to actually drop/
> recreate the users from the db and then reset the perms to
> get them going. Not a big deal for me, but there has got
> to be a better way? Any ideas?
> TIA, ChrisR|||Also, search KB and read about sp_help_revlogin.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:1ab6401c44ef9$173fb1d0$a501280a@.phx
.gbl...
> sql2k sp3
> About once a month Im asked to take a backup from
> production and restore it in a development environment.
> (these are in the same domain) After the restore, I can
> see the users in the db, but those users cant see the db
> from tools like Query Analyzer. I have to actually drop/
> recreate the users from the db and then reset the perms to
> get them going. Not a big deal for me, but there has got
> to be a better way? Any ideas?
> TIA, ChrisR

Login problem

Hi all,
I wrote an ISAPI (dll) application to work with a database on my SQL2K. When
I use windows NT integrated security (MyDomain\Username), The application
works properly. But when I use a specific username and password (using a sql
buildin login) I get this error message when adding a new record to the
database.
Internal Application Error
Application uses a value of the wrong type for the current operation
I granted the user to have full access to the database.
Any help would be appreciated.
Thank you,
MehrdadHi
Does the user have all appropriate permissions to insert data into table?
"M. Noroozi Eghbali" <galaxy@.irandoc.ac.ir> wrote in message
news:%23CCQpjtyEHA.2600@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I wrote an ISAPI (dll) application to work with a database on my SQL2K.
When
> I use windows NT integrated security (MyDomain\Username), The application
> works properly. But when I use a specific username and password (using a
sql
> buildin login) I get this error message when adding a new record to the
> database.
> --
> Internal Application Error
> Application uses a value of the wrong type for the current operation
> --
> I granted the user to have full access to the database.
> Any help would be appreciated.
> Thank you,
> Mehrdad
>|||Yes, the user have all the permissions to Insert, Delete, Edit, and Create
the table.
Mehrdad
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O4Zq21tyEHA.3120@.TK2MSFTNGP12.phx.gbl...
> Hi
> Does the user have all appropriate permissions to insert data into table?
>
>
>
> "M. Noroozi Eghbali" <galaxy@.irandoc.ac.ir> wrote in message
> news:%23CCQpjtyEHA.2600@.TK2MSFTNGP09.phx.gbl...
> When
application[vbcol=seagreen]
> sql
>|||Hi
Perhaps you try to insert a value that has a different datatype (as error
said). Can you show us the INSERT statement?
"M. Noroozi Eghbali" <galaxy@.irandoc.ac.ir> wrote in message
news:uK$TWauyEHA.352@.TK2MSFTNGP14.phx.gbl...
> Yes, the user have all the permissions to Insert, Delete, Edit, and Create
> the table.
> Mehrdad
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:O4Zq21tyEHA.3120@.TK2MSFTNGP12.phx.gbl...
table?[vbcol=seagreen]
SQL2K.[vbcol=seagreen]
> application
a[vbcol=seagreen]
the[vbcol=seagreen]
>|||Thank you Uru for your notice to my problem. I asked the administrator to
restart the SQL server. It works now properly.
Thank you,
Mehrdad
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OMn4KduyEHA.2568@.TK2MSFTNGP10.phx.gbl...
> Hi
> Perhaps you try to insert a value that has a different datatype (as error
> said). Can you show us the INSERT statement?
>
>
>
> "M. Noroozi Eghbali" <galaxy@.irandoc.ac.ir> wrote in message
> news:uK$TWauyEHA.352@.TK2MSFTNGP14.phx.gbl...
Create[vbcol=seagreen]
> table?
> SQL2K.
(using[vbcol=seagreen]
> a
> the
>