Log Shipping or something else?

Here's my scenario.
I have two sql servers, one located in another state, but connected via a
continuous vpn so it really looks like it's always on my network.
I only make changes to the sql server on the remote server, but I want the
local server to be as close as possible to having exactly the same data on
it, for primarily queries, etc.
The local server could be read only I guess, but only if no changes would
need to be made to the queries (MS query mostly).
Would log shipping work' Would something else work better'
Also the remote database is call xxx but my local db will be called yyy, but
it will essentially have the same tables in it.
This is because I have hundreds of queries that reference yyy even though
database xxx is our new database that we're using.
Thanks
Alan SawyerHi,
Do you need the local server for reporting or something ?
With Log shipping your local server is not available for reporting or as a
matter of fact for nothing as it requires an exclusive lock on the database
to restore the log and it will forcibly remove the users or the log shipping
job would fail.
I would say a transactional replication would be a better option. You might
wanna explore more on this.
Also refer to this
[url]http://www.sqlservercentral.com/columnists/pibison/logshippingvsreplication.asp[/u
rl]
Hopefully the above gives you a better idea of what you might do. Do let us
know if you face any problem in setting up the same. I am sure some one will
be able to help you.
Abhishek
"asawyer@.chambersREMOVEbelt.com" wrote:

> Here's my scenario.
> I have two sql servers, one located in another state, but connected via a
> continuous vpn so it really looks like it's always on my network.
> I only make changes to the sql server on the remote server, but I want the
> local server to be as close as possible to having exactly the same data on
> it, for primarily queries, etc.
> The local server could be read only I guess, but only if no changes would
> need to be made to the queries (MS query mostly).
> Would log shipping work' Would something else work better'
>
> Also the remote database is call xxx but my local db will be called yyy, b
ut
> it will essentially have the same tables in it.
> This is because I have hundreds of queries that reference yyy even though
> database xxx is our new database that we're using.
> Thanks
> Alan Sawyer
>|||I definetly need access to the local server, as I want to qurey from it.
I'll take a look at what you suggest.
Alan

Log Shipping from SQL Server 2000 to SQL Server 2005

I work for a small state government department. We are about to install SQL Server 2005.

A national government department that runs on SQL Server 2000 are willing to give us a backup of their database (20 gig I think) and then log ship to us regulary. Our aim is to have a copy of the database that we can query from and that is only a day or so out-of-date.

I'm having no luck.

1. I can backup the SQL Server 2000 database.

2. I can restore the SQL Server 2000 database onto the SQL Server 2005 box no worries.

2. BUT, if I attempt to restore the 2000 database with the option 'Leave the database in read-only mode ...' selected (so that I can then restore transaction logs and it's read-only), then I get an error message. ""This backup cannot be restored using WITH STANDBY because a database upgrade is needed. Reissue the RESTORE without WITH STANDBY.". I'm doing all this using SQL Server Management Studio.

It appears we will have to use SQL Server 2000 rather than 2005 to get this working.

Q: Has anyone successfully done this from SQL 2000 to SQL 2005 please? If so, how was it achieved please?

Thanks.

Geoff - Did you find an answer to this?

I'm also trying to log ship from 2000 to 2005

|||

There is a good explanation about logshipping in the BOL.

"Upgrading a SQL Server 2000 Log Shipping Configuration"

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

The bottom line is that both ends of a log shipping configuration need to be at the same version.

In this case, you have a SQL 2005 instance on your end, and no ability to upgrade the primary database, as you don't control it, right?

One solution would be to install a SQL 2000 instance on your end, and use that for your end of the log-shipping configuration. There is no problem with having SQL 2005 and SQL 2000 instances side-by-side on the same machine.

You can then access the data in the SQL 2000 instance either from within that instance, or by setting up a distributed query from the SQL 2005 instance.

|||

hi,

I,m facing the same problem.

I know this case is already got asnwered status.

but I can't really use 2 sql, 2000 and 2005 cause it's license matter. I read that log shipping is consist 3 step.

1. back up the db

2. copy the backup

3. restore the db

I have lil experience in database. can it be done manually ?

anybody can help me create that step manually ?

regards,

-dedys

|||

Is this the official MircoSoft story on Logshipping between 2000 to 2005 SQL Servers?

I work at a company that could use SQL 2000 and SQL 2005 side by side on the Log Ship receiver

but I prefer to just restore the SQL 2000 transaction log back on the new SQL 2005 DB. Are the file

formats of the transaction log different between SQL 2000 and 2005? Thanks.

|||

Ok Chaps,

You cannot log ship from SQL 2000 to SQL 2005.

What you can do is.

1. Log ship from SQL 2000 to SQL 2000

2. Then upgrade the DR server to 2005.

3. And carry on log shipping until you recover or failover to DR.

The DR server database will remain in SQL 2000 until you recover it. This works if you carry on restoring the log with norecovery. Because you use standby, the recovery is performed each time, the above will not work for you.

Geoff,

For your case try using replication instead.

Jag

Log Shipping from SQL Server 2000 to SQL Server 2005

I work for a small state government department. We are about to install SQL Server 2005.

A national government department that runs on SQL Server 2000 are willing to give us a backup of their database (20 gig I think) and then log ship to us regulary. Our aim is to have a copy of the database that we can query from and that is only a day or so out-of-date.

I'm having no luck.

1. I can backup the SQL Server 2000 database.

2. I can restore the SQL Server 2000 database onto the SQL Server 2005 box no worries.

2. BUT, if I attempt to restore the 2000 database with the option 'Leave the database in read-only mode ...' selected (so that I can then restore transaction logs and it's read-only), then I get an error message. ""This backup cannot be restored using WITH STANDBY because a database upgrade is needed. Reissue the RESTORE without WITH STANDBY.". I'm doing all this using SQL Server Management Studio.

It appears we will have to use SQL Server 2000 rather than 2005 to get this working.

Q: Has anyone successfully done this from SQL 2000 to SQL 2005 please? If so, how was it achieved please?

Thanks.

Geoff - Did you find an answer to this?

I'm also trying to log ship from 2000 to 2005

|||

There is a good explanation about logshipping in the BOL.

"Upgrading a SQL Server 2000 Log Shipping Configuration"

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

The bottom line is that both ends of a log shipping configuration need to be at the same version.

In this case, you have a SQL 2005 instance on your end, and no ability to upgrade the primary database, as you don't control it, right?

One solution would be to install a SQL 2000 instance on your end, and use that for your end of the log-shipping configuration. There is no problem with having SQL 2005 and SQL 2000 instances side-by-side on the same machine.

You can then access the data in the SQL 2000 instance either from within that instance, or by setting up a distributed query from the SQL 2005 instance.

|||

hi,

I,m facing the same problem.

I know this case is already got asnwered status.

but I can't really use 2 sql, 2000 and 2005 cause it's license matter. I read that log shipping is consist 3 step.

1. back up the db

2. copy the backup

3. restore the db

I have lil experience in database. can it be done manually ?

anybody can help me create that step manually ?

regards,

-dedys

|||

Is this the official MircoSoft story on Logshipping between 2000 to 2005 SQL Servers?

I work at a company that could use SQL 2000 and SQL 2005 side by side on the Log Ship receiver

but I prefer to just restore the SQL 2000 transaction log back on the new SQL 2005 DB. Are the file

formats of the transaction log different between SQL 2000 and 2005? Thanks.

|||

Ok Chaps,

You cannot log ship from SQL 2000 to SQL 2005.

What you can do is.

1. Log ship from SQL 2000 to SQL 2000

2. Then upgrade the DR server to 2005.

3. And carry on log shipping until you recover or failover to DR.

The DR server database will remain in SQL 2000 until you recover it. This works if you carry on restoring the log with norecovery. Because you use standby, the recovery is performed each time, the above will not work for you.

Geoff,

For your case try using replication instead.

Jag

Log Shipping from SQL Server 2000 to SQL Server 2005

I work for a small state government department. We are about to install SQL Server 2005.

A national government department that runs on SQL Server 2000 are willing to give us a backup of their database (20 gig I think) and then log ship to us regulary. Our aim is to have a copy of the database that we can query from and that is only a day or so out-of-date.

I'm having no luck.

1. I can backup the SQL Server 2000 database.

2. I can restore the SQL Server 2000 database onto the SQL Server 2005 box no worries.

2. BUT, if I attempt to restore the 2000 database with the option 'Leave the database in read-only mode ...' selected (so that I can then restore transaction logs and it's read-only), then I get an error message. ""This backup cannot be restored using WITH STANDBY because a database upgrade is needed. Reissue the RESTORE without WITH STANDBY.". I'm doing all this using SQL Server Management Studio.

It appears we will have to use SQL Server 2000 rather than 2005 to get this working.

Q: Has anyone successfully done this from SQL 2000 to SQL 2005 please? If so, how was it achieved please?

Thanks.

Geoff - Did you find an answer to this?

I'm also trying to log ship from 2000 to 2005

|||

There is a good explanation about logshipping in the BOL.

"Upgrading a SQL Server 2000 Log Shipping Configuration"

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

The bottom line is that both ends of a log shipping configuration need to be at the same version.

In this case, you have a SQL 2005 instance on your end, and no ability to upgrade the primary database, as you don't control it, right?

One solution would be to install a SQL 2000 instance on your end, and use that for your end of the log-shipping configuration. There is no problem with having SQL 2005 and SQL 2000 instances side-by-side on the same machine.

You can then access the data in the SQL 2000 instance either from within that instance, or by setting up a distributed query from the SQL 2005 instance.

|||

hi,

I,m facing the same problem.

I know this case is already got asnwered status.

but I can't really use 2 sql, 2000 and 2005 cause it's license matter. I read that log shipping is consist 3 step.

1. back up the db

2. copy the backup

3. restore the db

I have lil experience in database. can it be done manually ?

anybody can help me create that step manually ?

regards,

-dedys

|||

Is this the official MircoSoft story on Logshipping between 2000 to 2005 SQL Servers?

I work at a company that could use SQL 2000 and SQL 2005 side by side on the Log Ship receiver

but I prefer to just restore the SQL 2000 transaction log back on the new SQL 2005 DB. Are the file

formats of the transaction log different between SQL 2000 and 2005? Thanks.

|||

Ok Chaps,

You cannot log ship from SQL 2000 to SQL 2005.

What you can do is.

1. Log ship from SQL 2000 to SQL 2000

2. Then upgrade the DR server to 2005.

3. And carry on log shipping until you recover or failover to DR.

The DR server database will remain in SQL 2000 until you recover it. This works if you carry on restoring the log with norecovery. Because you use standby, the recovery is performed each time, the above will not work for you.

Geoff,

For your case try using replication instead.

Jag

Log Shipping from SQL Server 2000 to SQL Server 2005

I work for a small state government department. We are about to install SQL Server 2005.

A national government department that runs on SQL Server 2000 are willing to give us a backup of their database (20 gig I think) and then log ship to us regulary. Our aim is to have a copy of the database that we can query from and that is only a day or so out-of-date.

I'm having no luck.

1. I can backup the SQL Server 2000 database.

2. I can restore the SQL Server 2000 database onto the SQL Server 2005 box no worries.

2. BUT, if I attempt to restore the 2000 database with the option 'Leave the database in read-only mode ...' selected (so that I can then restore transaction logs and it's read-only), then I get an error message. ""This backup cannot be restored using WITH STANDBY because a database upgrade is needed. Reissue the RESTORE without WITH STANDBY.". I'm doing all this using SQL Server Management Studio.

It appears we will have to use SQL Server 2000 rather than 2005 to get this working.

Q: Has anyone successfully done this from SQL 2000 to SQL 2005 please? If so, how was it achieved please?

Thanks.

Geoff - Did you find an answer to this?

I'm also trying to log ship from 2000 to 2005

|||

There is a good explanation about logshipping in the BOL.

"Upgrading a SQL Server 2000 Log Shipping Configuration"

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

The bottom line is that both ends of a log shipping configuration need to be at the same version.

In this case, you have a SQL 2005 instance on your end, and no ability to upgrade the primary database, as you don't control it, right?

One solution would be to install a SQL 2000 instance on your end, and use that for your end of the log-shipping configuration. There is no problem with having SQL 2005 and SQL 2000 instances side-by-side on the same machine.

You can then access the data in the SQL 2000 instance either from within that instance, or by setting up a distributed query from the SQL 2005 instance.

|||

hi,

I,m facing the same problem.

I know this case is already got asnwered status.

but I can't really use 2 sql, 2000 and 2005 cause it's license matter. I read that log shipping is consist 3 step.

1. back up the db

2. copy the backup

3. restore the db

I have lil experience in database. can it be done manually ?

anybody can help me create that step manually ?

regards,

-dedys

|||

Is this the official MircoSoft story on Logshipping between 2000 to 2005 SQL Servers?

I work at a company that could use SQL 2000 and SQL 2005 side by side on the Log Ship receiver

but I prefer to just restore the SQL 2000 transaction log back on the new SQL 2005 DB. Are the file

formats of the transaction log different between SQL 2000 and 2005? Thanks.

|||

Ok Chaps,

You cannot log ship from SQL 2000 to SQL 2005.

What you can do is.

1. Log ship from SQL 2000 to SQL 2000

2. Then upgrade the DR server to 2005.

3. And carry on log shipping until you recover or failover to DR.

The DR server database will remain in SQL 2000 until you recover it. This works if you carry on restoring the log with norecovery. Because you use standby, the recovery is performed each time, the above will not work for you.

Geoff,

For your case try using replication instead.

Jag

Log shipping error - database in use

I am using MS builtin log-shipping functionality in
SQL2000.
My log shipping maintenance plan has a secondary load
state of 'standby' mode. And I have selected
the 'terminate users in database' option - however,
occasionally my log-shipping job is failing with '3101 -
database in use' error.
However, I thought that by selecting 'terminate users in
database' option - this should never happen ?
Has anyone seen anything similar before ?'terminate users in database' will not work always and
does not guarantee anything, a terminated process may drop
into a rollback mode and then it could take a long time to
finally get disappeared.
>--Original Message--
>I am using MS builtin log-shipping functionality in
>SQL2000.
>My log shipping maintenance plan has a secondary load
>state of 'standby' mode. And I have selected
>the 'terminate users in database' option - however,
>occasionally my log-shipping job is failing with '3101 -
>database in use' error.
>However, I thought that by selecting 'terminate users in
>database' option - this should never happen ?
>Has anyone seen anything similar before ?
>.
>

Log shipping and switching recovery modes

I'm a bit confused (a normal state).
With log shipping, is it possible to switch recovery mode from/to Full and
Bulk Logged to alleviate problems caused by reindexing/defragging?
Some documents appear to say Full recovery mode only for log shipping but
others say no problem with Bulk Logged.
Paul
I just tested in my Pubs DB.
1; Set in Full mode.
2; Full Backup.
3; Set in BL mode.
4; TLog Backup.
5; Did a successful Restore.
Despite my 30 second test, Id highly recommend your doing lots of testing
before you implement anything like this.
ChrisR
"Paul Cahill" wrote:

> I'm a bit confused (a normal state).
> With log shipping, is it possible to switch recovery mode from/to Full and
> Bulk Logged to alleviate problems caused by reindexing/defragging?
> Some documents appear to say Full recovery mode only for log shipping but
> others say no problem with Bulk Logged.
> Paul
>
>
|||Thanks Chris
Paul
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:20A96EA2-0684-42C7-9C75-952285D92C25@.microsoft.com...[vbcol=seagreen]
>I just tested in my Pubs DB.
> 1; Set in Full mode.
> 2; Full Backup.
> 3; Set in BL mode.
> 4; TLog Backup.
> 5; Did a successful Restore.
> Despite my 30 second test, Id highly recommend your doing lots of testing
> before you implement anything like this.
> --
> ChrisR
>
> "Paul Cahill" wrote:

Log shipping and switching recovery modes

I'm a bit confused (a normal state).
With log shipping, is it possible to switch recovery mode from/to Full and
Bulk Logged to alleviate problems caused by reindexing/defragging?
Some documents appear to say Full recovery mode only for log shipping but
others say no problem with Bulk Logged.
PaulI just tested in my Pubs DB.
1; Set in Full mode.
2; Full Backup.
3; Set in BL mode.
4; TLog Backup.
5; Did a successful Restore.
Despite my 30 second test, Id highly recommend your doing lots of testing
before you implement anything like this.
--
ChrisR
"Paul Cahill" wrote:
> I'm a bit confused (a normal state).
> With log shipping, is it possible to switch recovery mode from/to Full and
> Bulk Logged to alleviate problems caused by reindexing/defragging?
> Some documents appear to say Full recovery mode only for log shipping but
> others say no problem with Bulk Logged.
> Paul
>
>|||Thanks Chris
Paul
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:20A96EA2-0684-42C7-9C75-952285D92C25@.microsoft.com...
>I just tested in my Pubs DB.
> 1; Set in Full mode.
> 2; Full Backup.
> 3; Set in BL mode.
> 4; TLog Backup.
> 5; Did a successful Restore.
> Despite my 30 second test, Id highly recommend your doing lots of testing
> before you implement anything like this.
> --
> ChrisR
>
> "Paul Cahill" wrote:
>> I'm a bit confused (a normal state).
>> With log shipping, is it possible to switch recovery mode from/to Full
>> and
>> Bulk Logged to alleviate problems caused by reindexing/defragging?
>> Some documents appear to say Full recovery mode only for log shipping but
>> others say no problem with Bulk Logged.
>> Paul
>>

Log shipping and switching recovery modes

I'm a bit confused (a normal state).
With log shipping, is it possible to switch recovery mode from/to Full and
Bulk Logged to alleviate problems caused by reindexing/defragging?
Some documents appear to say Full recovery mode only for log shipping but
others say no problem with Bulk Logged.
PaulI just tested in my Pubs DB.
1; Set in Full mode.
2; Full Backup.
3; Set in BL mode.
4; TLog Backup.
5; Did a successful Restore.
Despite my 30 second test, Id highly recommend your doing lots of testing
before you implement anything like this.
ChrisR
"Paul Cahill" wrote:

> I'm a bit confused (a normal state).
> With log shipping, is it possible to switch recovery mode from/to Full and
> Bulk Logged to alleviate problems caused by reindexing/defragging?
> Some documents appear to say Full recovery mode only for log shipping but
> others say no problem with Bulk Logged.
> Paul
>
>|||Thanks Chris
Paul
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:20A96EA2-0684-42C7-9C75-952285D92C25@.microsoft.com...[vbcol=seagreen]
>I just tested in my Pubs DB.
> 1; Set in Full mode.
> 2; Full Backup.
> 3; Set in BL mode.
> 4; TLog Backup.
> 5; Did a successful Restore.
> Despite my 30 second test, Id highly recommend your doing lots of testing
> before you implement anything like this.
> --
> ChrisR
>
> "Paul Cahill" wrote:
>

Log Shipping (can't "auto_fix" user after db restore)

Anyone,
I've implmented log shipping and it's working fine for the most part, but
since it's in a "read only" state,
I can't run a sp_change_users_login to remap my reports_readonly user to the
login on the secondary server.
Thus, this user can not access the read only database at all.
Anyone else seen this, or have a fix for it.
Thank you,
Wayne
Note the SID of that user in sysusers table within that database.
After that, drop the login and recreate it using sp_addlogin and the most
important being to use the SID noted from the sysusers table above
Look at sp_addlogin in Books Online along with @.sid parameter.. Good luck
!!!
"Wayne" <wgadberry@.comcast.net> wrote in message
news:%23x$ixsqbEHA.368@.TK2MSFTNGP10.phx.gbl...
> Anyone,
> I've implmented log shipping and it's working fine for the most part, but
> since it's in a "read only" state,
> I can't run a sp_change_users_login to remap my reports_readonly user to
the
> login on the secondary server.
> Thus, this user can not access the read only database at all.
> Anyone else seen this, or have a fix for it.
> Thank you,
> Wayne
>
|||i'll give it a shot... thanks..
Wayne
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:ekyMEmubEHA.3636@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Note the SID of that user in sysusers table within that database.
> After that, drop the login and recreate it using sp_addlogin and the most
> important being to use the SID noted from the sysusers table above
> Look at sp_addlogin in Books Online along with @.sid parameter.. Good luck
> !!!
>
> "Wayne" <wgadberry@.comcast.net> wrote in message
> news:%23x$ixsqbEHA.368@.TK2MSFTNGP10.phx.gbl...
but
> the
>
|||Worked like a champ Hassan.. you the man..
Wayne
"Wayne" <wgadberry@.comcast.net> wrote in message
news:%23wqQ3RzbEHA.2816@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> i'll give it a shot... thanks..
> Wayne
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:ekyMEmubEHA.3636@.TK2MSFTNGP10.phx.gbl...
most[vbcol=seagreen]
luck[vbcol=seagreen]
> but
to
>

Log Shipping (can't "auto_fix" user after db restore)

Anyone,
I've implmented log shipping and it's working fine for the most part, but
since it's in a "read only" state,
I can't run a sp_change_users_login to remap my reports_readonly user to the
login on the secondary server.
Thus, this user can not access the read only database at all.
Anyone else seen this, or have a fix for it.
Thank you,
WayneNote the SID of that user in sysusers table within that database.
After that, drop the login and recreate it using sp_addlogin and the most
important being to use the SID noted from the sysusers table above
Look at sp_addlogin in Books Online along with @.sid parameter.. Good luck
!!!
"Wayne" <wgadberry@.comcast.net> wrote in message
news:%23x$ixsqbEHA.368@.TK2MSFTNGP10.phx.gbl...
> Anyone,
> I've implmented log shipping and it's working fine for the most part, but
> since it's in a "read only" state,
> I can't run a sp_change_users_login to remap my reports_readonly user to
the
> login on the secondary server.
> Thus, this user can not access the read only database at all.
> Anyone else seen this, or have a fix for it.
> Thank you,
> Wayne
>|||i'll give it a shot... thanks..
Wayne
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:ekyMEmubEHA.3636@.TK2MSFTNGP10.phx.gbl...
> Note the SID of that user in sysusers table within that database.
> After that, drop the login and recreate it using sp_addlogin and the most
> important being to use the SID noted from the sysusers table above
> Look at sp_addlogin in Books Online along with @.sid parameter.. Good luck
> !!!
>
> "Wayne" <wgadberry@.comcast.net> wrote in message
> news:%23x$ixsqbEHA.368@.TK2MSFTNGP10.phx.gbl...
but[vbcol=seagreen]
> the
>|||Worked like a champ Hassan.. you the man..
Wayne
"Wayne" <wgadberry@.comcast.net> wrote in message
news:%23wqQ3RzbEHA.2816@.TK2MSFTNGP11.phx.gbl...
> i'll give it a shot... thanks..
> Wayne
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:ekyMEmubEHA.3636@.TK2MSFTNGP10.phx.gbl...
most[vbcol=seagreen]
luck[vbcol=seagreen]
> but
to[vbcol=seagreen]
>

Log Shipping (can't "auto_fix" user after db restore)

Anyone,
I've implmented log shipping and it's working fine for the most part, but
since it's in a "read only" state,
I can't run a sp_change_users_login to remap my reports_readonly user to the
login on the secondary server.
Thus, this user can not access the read only database at all.
Anyone else seen this, or have a fix for it.
Thank you,
WayneNote the SID of that user in sysusers table within that database.
After that, drop the login and recreate it using sp_addlogin and the most
important being to use the SID noted from the sysusers table above
Look at sp_addlogin in Books Online along with @.sid parameter.. Good luck
!!!
"Wayne" <wgadberry@.comcast.net> wrote in message
news:%23x$ixsqbEHA.368@.TK2MSFTNGP10.phx.gbl...
> Anyone,
> I've implmented log shipping and it's working fine for the most part, but
> since it's in a "read only" state,
> I can't run a sp_change_users_login to remap my reports_readonly user to
the
> login on the secondary server.
> Thus, this user can not access the read only database at all.
> Anyone else seen this, or have a fix for it.
> Thank you,
> Wayne
>|||i'll give it a shot... thanks..
Wayne
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:ekyMEmubEHA.3636@.TK2MSFTNGP10.phx.gbl...
> Note the SID of that user in sysusers table within that database.
> After that, drop the login and recreate it using sp_addlogin and the most
> important being to use the SID noted from the sysusers table above
> Look at sp_addlogin in Books Online along with @.sid parameter.. Good luck
> !!!
>
> "Wayne" <wgadberry@.comcast.net> wrote in message
> news:%23x$ixsqbEHA.368@.TK2MSFTNGP10.phx.gbl...
> > Anyone,
> >
> > I've implmented log shipping and it's working fine for the most part,
but
> > since it's in a "read only" state,
> > I can't run a sp_change_users_login to remap my reports_readonly user to
> the
> > login on the secondary server.
> > Thus, this user can not access the read only database at all.
> > Anyone else seen this, or have a fix for it.
> >
> > Thank you,
> > Wayne
> >
> >
>|||Worked like a champ Hassan.. you the man..
Wayne
"Wayne" <wgadberry@.comcast.net> wrote in message
news:%23wqQ3RzbEHA.2816@.TK2MSFTNGP11.phx.gbl...
> i'll give it a shot... thanks..
> Wayne
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:ekyMEmubEHA.3636@.TK2MSFTNGP10.phx.gbl...
> > Note the SID of that user in sysusers table within that database.
> >
> > After that, drop the login and recreate it using sp_addlogin and the
most
> > important being to use the SID noted from the sysusers table above
> >
> > Look at sp_addlogin in Books Online along with @.sid parameter.. Good
luck
> > !!!
> >
> >
> > "Wayne" <wgadberry@.comcast.net> wrote in message
> > news:%23x$ixsqbEHA.368@.TK2MSFTNGP10.phx.gbl...
> > > Anyone,
> > >
> > > I've implmented log shipping and it's working fine for the most part,
> but
> > > since it's in a "read only" state,
> > > I can't run a sp_change_users_login to remap my reports_readonly user
to
> > the
> > > login on the secondary server.
> > > Thus, this user can not access the read only database at all.
> > > Anyone else seen this, or have a fix for it.
> > >
> > > Thank you,
> > > Wayne
> > >
> > >
> >
> >
>

Log Shipping "Out of Sync" State

I have two production SQL servers that log ship to two
failsafe servers in different geographic locations. I am
continually having "Out of Sync" conditions on one or more
of the databases that are being log shipped. In order to
clear the "Out of Sync" condition, I must blow away the
log shipping maintenance plan, all existing transaction
log backups for the particular database on all servers and
recreate the plans. What can I do to prevent or easily
correct the "Out of Sync" condition? What is the impact of
the "Out of Sync" condition to my failsafe plan? Are the
databases invalid?Log shipping is just the technique of applying a database backup to a =server and then applying future transaction log backups to the server. =They can get out of synch when a transaction restore fails to execute =and another transaction log backup happens and is attempted to be =applied to the server.
To correct the out of synch errors simply disable your transaction log =backups, re-run your full database backup, restore to your standby =server, and then re-enable your transaction log backups (and automated =restore process)
To prevent this from happening make sure that you have a reliable =network. You might also want to put some logging/error checking in =place. If a job fails perhaps you can retry it.
What do you mean by invalid? When this does happen the databases are =not bad, the log shipped database simply does not contain current data.
Perhaps you will find this additional information helpful:
http://sqlguy.home.comcast.net/logship.htm
-- Keith
"Colby" <anonymous@.discussions.microsoft.com> wrote in message =news:b50b01c4375d$fffeee80$a301280a@.phx.gbl...
> I have two production SQL servers that log ship to two > failsafe servers in different geographic locations. I am > continually having "Out of Sync" conditions on one or more > of the databases that are being log shipped. In order to > clear the "Out of Sync" condition, I must blow away the > log shipping maintenance plan, all existing transaction > log backups for the particular database on all servers and > recreate the plans. What can I do to prevent or easily > correct the "Out of Sync" condition? What is the impact of > the "Out of Sync" condition to my failsafe plan? Are the > databases invalid?

Log Shipping "Out of Sync" State

I have two production SQL servers that log ship to two
failsafe servers in different geographic locations. I am
continually having "Out of Sync" conditions on one or more
of the databases that are being log shipped. In order to
clear the "Out of Sync" condition, I must blow away the
log shipping maintenance plan, all existing transaction
log backups for the particular database on all servers and
recreate the plans. What can I do to prevent or easily
correct the "Out of Sync" condition? What is the impact of
the "Out of Sync" condition to my failsafe plan? Are the
databases invalid?Log shipping is just the technique of applying a database backup to a =
server and then applying future transaction log backups to the server. =
They can get out of synch when a transaction restore fails to execute =
and another transaction log backup happens and is attempted to be =
applied to the server.
To correct the out of synch errors simply disable your transaction log =
backups, re-run your full database backup, restore to your standby =
server, and then re-enable your transaction log backups (and automated =
restore process)
To prevent this from happening make sure that you have a reliable =
network. You might also want to put some logging/error checking in =
place. If a job fails perhaps you can retry it.
What do you mean by invalid? When this does happen the databases are =
not bad, the log shipped database simply does not contain current data.
Perhaps you will find this additional information helpful:
http://sqlguy.home.comcast.net/logship.htm
--=20
Keith
"Colby" <anonymous@.discussions.microsoft.com> wrote in message =
news:b50b01c4375d$fffeee80$a301280a@.phx.gbl...
> I have two production SQL servers that log ship to two=20
> failsafe servers in different geographic locations. I am=20
> continually having "Out of Sync" conditions on one or more=20
> of the databases that are being log shipped. In order to=20
> clear the "Out of Sync" condition, I must blow away the=20
> log shipping maintenance plan, all existing transaction=20
> log backups for the particular database on all servers and=20
> recreate the plans. What can I do to prevent or easily=20
> correct the "Out of Sync" condition? What is the impact of=20
> the "Out of Sync" condition to my failsafe plan? Are the=20
> databases invalid?

Log Shipping "Out of Sync" State

I have two production SQL servers that log ship to two
failsafe servers in different geographic locations. I am
continually having "Out of Sync" conditions on one or more
of the databases that are being log shipped. In order to
clear the "Out of Sync" condition, I must blow away the
log shipping maintenance plan, all existing transaction
log backups for the particular database on all servers and
recreate the plans. What can I do to prevent or easily
correct the "Out of Sync" condition? What is the impact of
the "Out of Sync" condition to my failsafe plan? Are the
databases invalid?
Log shipping is just the technique of applying a database backup to a =
server and then applying future transaction log backups to the server. =
They can get out of synch when a transaction restore fails to execute =
and another transaction log backup happens and is attempted to be =
applied to the server.
To correct the out of synch errors simply disable your transaction log =
backups, re-run your full database backup, restore to your standby =
server, and then re-enable your transaction log backups (and automated =
restore process)
To prevent this from happening make sure that you have a reliable =
network. You might also want to put some logging/error checking in =
place. If a job fails perhaps you can retry it.
What do you mean by invalid? When this does happen the databases are =
not bad, the log shipped database simply does not contain current data.
Perhaps you will find this additional information helpful:
http://sqlguy.home.comcast.net/logship.htm
--=20
Keith
"Colby" <anonymous@.discussions.microsoft.com> wrote in message =
news:b50b01c4375d$fffeee80$a301280a@.phx.gbl...
> I have two production SQL servers that log ship to two=20
> failsafe servers in different geographic locations. I am=20
> continually having "Out of Sync" conditions on one or more=20
> of the databases that are being log shipped. In order to=20
> clear the "Out of Sync" condition, I must blow away the=20
> log shipping maintenance plan, all existing transaction=20
> log backups for the particular database on all servers and=20
> recreate the plans. What can I do to prevent or easily=20
> correct the "Out of Sync" condition? What is the impact of=20
> the "Out of Sync" condition to my failsafe plan? Are the=20
> databases invalid?