Log Shipping -SQL 2000

Is there a way to detach a database that is in warm
standby mode and attach it back in the same mode and
continue with T-logs restores ?Not to my knowledge, no.
Why would you want to do such a thing? It seems illogical.
>--Original Message--
>Is there a way to detach a database that is in warm
>standby mode and attach it back in the same mode and
>continue with T-logs restores ?
>.
>sql

Log Shipping Problem/Question.

It seems that my warm standby database is locked up in status
(Loading). The Standby server locked up over the weekend and now the
database was left in (Loading) Status.

If I try to right click on the database in enterprise manager and do a
properties I get the following error message:

"Error 927: Database 'db name' cannot be opened. It is in the middle
of a restore."

Is there some way to abort the current restore process and re-start
the log shipping process?"kip cavin" <cavink@.sitemaster.com> wrote in message
news:8323efee.0409131241.729db773@.posting.google.c om...
> It seems that my warm standby database is locked up in status
> (Loading). The Standby server locked up over the weekend and now the
> database was left in (Loading) Status.
> If I try to right click on the database in enterprise manager and do a
> properties I get the following error message:
> "Error 927: Database 'db name' cannot be opened. It is in the middle
> of a restore."
> Is there some way to abort the current restore process and re-start
> the log shipping process?

You can try a RESTORE DATABASE 'db name' with recovery

That may get it back into a usuable state.

(You might also try RESTORE LOG from disk ='applicable log file' with
standby='c:\foo.log' This should try to restore the log and put the DB in
read-only mode, but allow you to restore subsequent logs w/o an issue.sql

Log Shipping Problem

I have a warm standby 2003 Server and am trying to set up log shipping. I am
working with SQL Server 2005 and have created the log shipping jobs using the
wizard. Initially as suggested in the documentation, I backed up the primary
database using norecover options. I resored the database on the secondary to
overwrite the existing database with recovery and standby options which has
left the secondary database in Standby/Read-Only mode. All the the log
shipping job history on the primary and secondary servers show success (even
the restore on the secondary). When I insert records into tables on the
primary I do not see them appear in the secondary database after the restore
job executes. I don't have much experience with log shipping or partial
restores from log files for that matter, I have always done full restores
with no recovery. Why don't I see the new records in the secondary database
after a successful restore (according to job history). I can see the .trn
files in the shipped log file directory, how does this process work, does it
take a restore with norecovery before you can see them in the secondary
database? There is nothing in the documentation that indicates this, I'm
missing something here ... I guess it has something to do with partial
restores. What am I doing wrong.
Fran Morabito wrote:
> I have a warm standby 2003 Server and am trying to set up log shipping. I am
> working with SQL Server 2005 and have created the log shipping jobs using the
> wizard. Initially as suggested in the documentation, I backed up the primary
> database using norecover options. I resored the database on the secondary to
> overwrite the existing database with recovery and standby options which has
> left the secondary database in Standby/Read-Only mode. All the the log
> shipping job history on the primary and secondary servers show success (even
> the restore on the secondary). When I insert records into tables on the
> primary I do not see them appear in the secondary database after the restore
> job executes. I don't have much experience with log shipping or partial
> restores from log files for that matter, I have always done full restores
> with no recovery. Why don't I see the new records in the secondary database
> after a successful restore (according to job history). I can see the .trn
> files in the shipped log file directory, how does this process work, does it
> take a restore with norecovery before you can see them in the secondary
> database? There is nothing in the documentation that indicates this, I'm
> missing something here ... I guess it has something to do with partial
> restores. What am I doing wrong.
>
I don't know anything about the log shipping wizard, I've always rolled
my own log shipping routines. It's a pretty simple process:
1. Start with a full backup of the live database
2. Restore full backup to standby using WITH NORECOVERY option
3. Schedule frequent transaction log backups of the live database,
preferrably writing each backup to a seperate file
4. Monitor for new transaction log backups, when found, restore to
standby using WITH NORECOVERY option.
I would start by making sure your transaction log backups are occurring
on the live server, and that they are being restored on the standby server.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Log Shipping Problem

I have a warm standby 2003 Server and am trying to set up log shipping. I am
working with SQL Server 2005 and have created the log shipping jobs using th
e
wizard. Initially as suggested in the documentation, I backed up the primary
database using norecover options. I resored the database on the secondary to
overwrite the existing database with recovery and standby options which has
left the secondary database in Standby/Read-Only mode. All the the log
shipping job history on the primary and secondary servers show success (even
the restore on the secondary). When I insert records into tables on the
primary I do not see them appear in the secondary database after the restore
job executes. I don't have much experience with log shipping or partial
restores from log files for that matter, I have always done full restores
with no recovery. Why don't I see the new records in the secondary database
after a successful restore (according to job history). I can see the .trn
files in the shipped log file directory, how does this process work, does it
take a restore with norecovery before you can see them in the secondary
database? There is nothing in the documentation that indicates this, I'm
missing something here ... I guess it has something to do with partial
restores. What am I doing wrong.Fran Morabito wrote:
> I have a warm standby 2003 Server and am trying to set up log shipping. I
am
> working with SQL Server 2005 and have created the log shipping jobs using
the
> wizard. Initially as suggested in the documentation, I backed up the prima
ry
> database using norecover options. I resored the database on the secondary
to
> overwrite the existing database with recovery and standby options which ha
s
> left the secondary database in Standby/Read-Only mode. All the the log
> shipping job history on the primary and secondary servers show success (ev
en
> the restore on the secondary). When I insert records into tables on the
> primary I do not see them appear in the secondary database after the resto
re
> job executes. I don't have much experience with log shipping or partial
> restores from log files for that matter, I have always done full restores
> with no recovery. Why don't I see the new records in the secondary databas
e
> after a successful restore (according to job history). I can see the .trn
> files in the shipped log file directory, how does this process work, does
it
> take a restore with norecovery before you can see them in the secondary
> database? There is nothing in the documentation that indicates this, I'm
> missing something here ... I guess it has something to do with partial
> restores. What am I doing wrong.
>
I don't know anything about the log shipping wizard, I've always rolled
my own log shipping routines. It's a pretty simple process:
1. Start with a full backup of the live database
2. Restore full backup to standby using WITH NORECOVERY option
3. Schedule frequent transaction log backups of the live database,
preferrably writing each backup to a seperate file
4. Monitor for new transaction log backups, when found, restore to
standby using WITH NORECOVERY option.
I would start by making sure your transaction log backups are occurring
on the live server, and that they are being restored on the standby server.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Log Shipping Problem

Hello,
I thought log shipping was supposed to be so strait forward and easy! Does n
ot appear to be a reliable fail-over or "warm standby" solution.
After setting up log shipping I have four databases that continue to try an
load the first_file_000000000000.trn log, that is created when the plan is f
irst initialized. It happens even though the backup/copy/loads are functioni
ng successfully and accordi
ng to the plan schedule, and this initial file doesn't even exist in the des
ignated share because it's been deleted according to the log retention sched
ule. What else should I have expected form Bill and the bunch....Anyway, h
ere is the error: It's like
..tell me something I don't know, like why is this happening!
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 4305: [Microsoft]&#
91;ODBC SQL Server Driver][SQL Server]The log in this backup set begins
at LSN 33000000022600001, which is too late to apply to the database. An ear
lier log backup that includes LSN 330000000
11800001 can be restored.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is te
rminating abnormally.
Any help anyone can give would be greatly appreciated. Thank you much!
Ren Flotlooks to me like you may be trying to apply a log that is somehow leaving
gaps in the transaction chain.
are you sure that you did not miss a logfile somewhere ?
Non-Logged operations can break your log chain....
Greg Jackson
PDX, Oregon

Log Shipping Problem

I have a warm standby 2003 Server and am trying to set up log shipping. I am
working with SQL Server 2005 and have created the log shipping jobs using the
wizard. Initially as suggested in the documentation, I backed up the primary
database using norecover options. I resored the database on the secondary to
overwrite the existing database with recovery and standby options which has
left the secondary database in Standby/Read-Only mode. All the the log
shipping job history on the primary and secondary servers show success (even
the restore on the secondary). When I insert records into tables on the
primary I do not see them appear in the secondary database after the restore
job executes. I don't have much experience with log shipping or partial
restores from log files for that matter, I have always done full restores
with no recovery. Why don't I see the new records in the secondary database
after a successful restore (according to job history). I can see the .trn
files in the shipped log file directory, how does this process work, does it
take a restore with norecovery before you can see them in the secondary
database? There is nothing in the documentation that indicates this, I'm
missing something here ... I guess it has something to do with partial
restores. What am I doing wrong.Fran Morabito wrote:
> I have a warm standby 2003 Server and am trying to set up log shipping. I am
> working with SQL Server 2005 and have created the log shipping jobs using the
> wizard. Initially as suggested in the documentation, I backed up the primary
> database using norecover options. I resored the database on the secondary to
> overwrite the existing database with recovery and standby options which has
> left the secondary database in Standby/Read-Only mode. All the the log
> shipping job history on the primary and secondary servers show success (even
> the restore on the secondary). When I insert records into tables on the
> primary I do not see them appear in the secondary database after the restore
> job executes. I don't have much experience with log shipping or partial
> restores from log files for that matter, I have always done full restores
> with no recovery. Why don't I see the new records in the secondary database
> after a successful restore (according to job history). I can see the .trn
> files in the shipped log file directory, how does this process work, does it
> take a restore with norecovery before you can see them in the secondary
> database? There is nothing in the documentation that indicates this, I'm
> missing something here ... I guess it has something to do with partial
> restores. What am I doing wrong.
>
I don't know anything about the log shipping wizard, I've always rolled
my own log shipping routines. It's a pretty simple process:
1. Start with a full backup of the live database
2. Restore full backup to standby using WITH NORECOVERY option
3. Schedule frequent transaction log backups of the live database,
preferrably writing each backup to a seperate file
4. Monitor for new transaction log backups, when found, restore to
standby using WITH NORECOVERY option.
I would start by making sure your transaction log backups are occurring
on the live server, and that they are being restored on the standby server.
Tracy McKibben
MCDBA
http://www.realsqlguy.comsql

Log shipping on Secondary cannot load large log file

We have log shipping from a primary to a secondary for warm standby and
reporting.
After a reindexing job that caused a particularly large log backup to be
created (9,141,927,424 bytes), the secondary appears to have pre-allocated
the disk space required to copy the file over (the file has a time stamp for
the time at which the Log-ship copy was scheduled) but after 5+ hours the
time stamp on the file has not changed and the log-ship copy/restore history
reports errors like:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL
Server Driver][SQL Server]Cannot open backup device
'H:\SQLData\MSSQL$NODEB\BACKUP\LogShip\OASIS_TTC_tlog_200408311200.TRN'.
Device error or device off-line. See the SQL Server error log for more
details.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating
abnormally.
When I inspect the process in the secondary that appears to be doing the
copy I see the following:
EXECUTE master.dbo.xp_sqlmaint '-LSCopyPlanID
"87C86B58-847F-4505-AFAE-4A1F9E31211E"'
Status: runnable
CPU: 0
Physical I/O: 0
Memory Usage: 2
Login time: 31/8/2004 1:00:07 PM
Last Batch: 31/8/2004 1:00:07 PM
As you can see the process seems to have stalled.
As far as disk and network through-put is concerned I would expect a worst
case scenario of 10 Mbytes per second - more than enough to copy the file
within the time since it was created.
Is there a way to recover from this situation without having to remove log
shipping and starting from scratch?
Any tips appreciated.Hello David
I'm not clear on the problem description that you have outlined in your
message. As I understand it, you are mentioning the following sequence of
events (please correct me if I'm wrong) :
1. You had a large tran log backup that has the same timestamp in the
filename as when the copy job ran.
2. You noticed, 5 hours after the copy job ran, that the restore job failed
with the error :
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL
Server Driver][SQL Server]Cannot open backup device
'H:\SQLData\MSSQL$NODEB\BACKUP\LogShip\OASIS_TTC_tlog_200408311200.TRN'.
Device error or device off-line. See the SQL Server error log for more
details.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating
abnormally.
I wanted to point out couple of things here :
1. The filename for the transaction log backup does not change through the
entire log shipping life cycle.
2. The filename of the transaction log backup file and the time the copy
job runs has no relation
3. Copy and Restore jobs on the secondary server are 2 different jobs and
run independent of each other.
4. The failure that you see above is reported by the Restore job (hence the
message "RESTORE LOG is terminating abnormally")
The question that I wanted to ask you was - Are you absolutely positive
that the copy job has completed copying the file to the secondary server?
Please confirm this by either trying to rename the file (and then renaming
it back to its original name) or using the HANDLE utility from
SYSINTERNALS.COM
As you have mentioned later in your message, it seems like the copy job is
still running. I would advise you to wait for the job to complete.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Hi Pankaj,
Thanks for the reply.
To cut a long story short, I killed the copy process that appeared to have
stalled.
In due course a new process started and the file was restored into the
secondary within a reasonably short amount of time.
I realise that killing a job like that is risky and normally you should
leave it run, but in my case we have a 55 GB database that requries the
log-shipping secondary to be highly available and up-to-date for reporting.
My choices were:
1) Kill the job and hope the secondary came back into synch within a couple
of hours.
2) Remove log-shipping and wait 8+ hours for the secondary to rebuild from
scratch.
I took a gamble and chose option 1.
"Pankaj Agarwal [MSFT]" wrote:
> Hello David
> I'm not clear on the problem description that you have outlined in your
> message. As I understand it, you are mentioning the following sequence of
> events (please correct me if I'm wrong) :
> 1. You had a large tran log backup that has the same timestamp in the
> filename as when the copy job ran.
> 2. You noticed, 5 hours after the copy job ran, that the restore job failed
> with the error :
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL
> Server Driver][SQL Server]Cannot open backup device
> 'H:\SQLData\MSSQL$NODEB\BACKUP\LogShip\OASIS_TTC_tlog_200408311200.TRN'.
> Device error or device off-line. See the SQL Server error log for more
> details.
> [Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating
> abnormally.
> I wanted to point out couple of things here :
> 1. The filename for the transaction log backup does not change through the
> entire log shipping life cycle.
> 2. The filename of the transaction log backup file and the time the copy
> job runs has no relation
> 3. Copy and Restore jobs on the secondary server are 2 different jobs and
> run independent of each other.
> 4. The failure that you see above is reported by the Restore job (hence the
> message "RESTORE LOG is terminating abnormally")
> The question that I wanted to ask you was - Are you absolutely positive
> that the copy job has completed copying the file to the secondary server?
> Please confirm this by either trying to rename the file (and then renaming
> it back to its original name) or using the HANDLE utility from
> SYSINTERNALS.COM
> As you have mentioned later in your message, it seems like the copy job is
> still running. I would advise you to wait for the job to complete.
> Thank you for using Microsoft newsgroups.
> Sincerely
> Pankaj Agarwal
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>

Log shipping on Secondary cannot load large log file

We have log shipping from a primary to a secondary for warm standby and
reporting.
After a reindexing job that caused a particularly large log backup to be
created (9,141,927,424 bytes), the secondary appears to have pre-allocated
the disk space required to copy the file over (the file has a time stamp for
the time at which the Log-ship copy was scheduled) but after 5+ hours the
time stamp on the file has not changed and the log-ship copy/restore history
reports errors like:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL
Server Driver][SQL Server]Cannot open backup device
'H:\SQLData\MSSQL$NODEB\BACKUP\LogShip\OASIS_TTC_t log_200408311200.TRN'.
Device error or device off-line. See the SQL Server error log for more
details.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating
abnormally.
When I inspect the process in the secondary that appears to be doing the
copy I see the following:
EXECUTE master.dbo.xp_sqlmaint '-LSCopyPlanID
"87C86B58-847F-4505-AFAE-4A1F9E31211E"'
Status: runnable
CPU: 0
Physical I/O: 0
Memory Usage: 2
Login time: 31/8/2004 1:00:07 PM
Last Batch: 31/8/2004 1:00:07 PM
As you can see the process seems to have stalled.
As far as disk and network through-put is concerned I would expect a worst
case scenario of 10 Mbytes per second - more than enough to copy the file
within the time since it was created.
Is there a way to recover from this situation without having to remove log
shipping and starting from scratch?
Any tips appreciated.
Hello David
I'm not clear on the problem description that you have outlined in your
message. As I understand it, you are mentioning the following sequence of
events (please correct me if I'm wrong) :
1. You had a large tran log backup that has the same timestamp in the
filename as when the copy job ran.
2. You noticed, 5 hours after the copy job ran, that the restore job failed
with the error :
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL
Server Driver][SQL Server]Cannot open backup device
'H:\SQLData\MSSQL$NODEB\BACKUP\LogShip\OASIS_TTC_t log_200408311200.TRN'.
Device error or device off-line. See the SQL Server error log for more
details.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating
abnormally.
I wanted to point out couple of things here :
1. The filename for the transaction log backup does not change through the
entire log shipping life cycle.
2. The filename of the transaction log backup file and the time the copy
job runs has no relation
3. Copy and Restore jobs on the secondary server are 2 different jobs and
run independent of each other.
4. The failure that you see above is reported by the Restore job (hence the
message "RESTORE LOG is terminating abnormally")
The question that I wanted to ask you was - Are you absolutely positive
that the copy job has completed copying the file to the secondary server?
Please confirm this by either trying to rename the file (and then renaming
it back to its original name) or using the HANDLE utility from
SYSINTERNALS.COM
As you have mentioned later in your message, it seems like the copy job is
still running. I would advise you to wait for the job to complete.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||Hi Pankaj,
Thanks for the reply.
To cut a long story short, I killed the copy process that appeared to have
stalled.
In due course a new process started and the file was restored into the
secondary within a reasonably short amount of time.
I realise that killing a job like that is risky and normally you should
leave it run, but in my case we have a 55 GB database that requries the
log-shipping secondary to be highly available and up-to-date for reporting.
My choices were:
1) Kill the job and hope the secondary came back into synch within a couple
of hours.
2) Remove log-shipping and wait 8+ hours for the secondary to rebuild from
scratch.
I took a gamble and chose option 1.
"Pankaj Agarwal [MSFT]" wrote:

> Hello David
> I'm not clear on the problem description that you have outlined in your
> message. As I understand it, you are mentioning the following sequence of
> events (please correct me if I'm wrong) :
> 1. You had a large tran log backup that has the same timestamp in the
> filename as when the copy job ran.
> 2. You noticed, 5 hours after the copy job ran, that the restore job failed
> with the error :
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL
> Server Driver][SQL Server]Cannot open backup device
> 'H:\SQLData\MSSQL$NODEB\BACKUP\LogShip\OASIS_TTC_t log_200408311200.TRN'.
> Device error or device off-line. See the SQL Server error log for more
> details.
> [Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating
> abnormally.
> I wanted to point out couple of things here :
> 1. The filename for the transaction log backup does not change through the
> entire log shipping life cycle.
> 2. The filename of the transaction log backup file and the time the copy
> job runs has no relation
> 3. Copy and Restore jobs on the secondary server are 2 different jobs and
> run independent of each other.
> 4. The failure that you see above is reported by the Restore job (hence the
> message "RESTORE LOG is terminating abnormally")
> The question that I wanted to ask you was - Are you absolutely positive
> that the copy job has completed copying the file to the secondary server?
> Please confirm this by either trying to rename the file (and then renaming
> it back to its original name) or using the HANDLE utility from
> SYSINTERNALS.COM
> As you have mentioned later in your message, it seems like the copy job is
> still running. I would advise you to wait for the job to complete.
> Thank you for using Microsoft newsgroups.
> Sincerely
> Pankaj Agarwal
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>

Log shipping on Secondary cannot load large log file

We have log shipping from a primary to a secondary for warm standby and
reporting.
After a reindexing job that caused a particularly large log backup to be
created (9,141,927,424 bytes), the secondary appears to have pre-allocated
the disk space required to copy the file over (the file has a time stamp for
the time at which the Log-ship copy was scheduled) but after 5+ hours the
time stamp on the file has not changed and the log-ship copy/restore history
reports errors like:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft]&#
91;ODBC SQL
Server Driver][SQL Server]Cannot open backup device
'H:\SQLData\MSSQL$NODEB\BACKUP\LogShip\O
ASIS_TTC_tlog_200408311200.TRN'.
Device error or device off-line. See the SQL Server error log for more
details.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is te
rminating
abnormally.
When I inspect the process in the secondary that appears to be doing the
copy I see the following:
EXECUTE master.dbo.xp_sqlmaint '-LSCopyPlanID
"87C86B58-847F-4505-AFAE-4A1F9E31211E"'
Status: runnable
CPU: 0
Physical I/O: 0
Memory Usage: 2
Login time: 31/8/2004 1:00:07 PM
Last Batch: 31/8/2004 1:00:07 PM
As you can see the process seems to have stalled.
As far as disk and network through-put is concerned I would expect a worst
case scenario of 10 Mbytes per second - more than enough to copy the file
within the time since it was created.
Is there a way to recover from this situation without having to remove log
shipping and starting from scratch?
Any tips appreciated.Hello David
I'm not clear on the problem description that you have outlined in your
message. As I understand it, you are mentioning the following sequence of
events (please correct me if I'm wrong) :
1. You had a large tran log backup that has the same timestamp in the
filename as when the copy job ran.
2. You noticed, 5 hours after the copy job ran, that the restore job failed
with the error :
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft]&#
91;ODBC SQL
Server Driver][SQL Server]Cannot open backup device
'H:\SQLData\MSSQL$NODEB\BACKUP\LogShip\O
ASIS_TTC_tlog_200408311200.TRN'.
Device error or device off-line. See the SQL Server error log for more
details.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is te
rminating
abnormally.
I wanted to point out couple of things here :
1. The filename for the transaction log backup does not change through the
entire log shipping life cycle.
2. The filename of the transaction log backup file and the time the copy
job runs has no relation
3. Copy and Restore jobs on the secondary server are 2 different jobs and
run independent of each other.
4. The failure that you see above is reported by the Restore job (hence the
message "RESTORE LOG is terminating abnormally")
The question that I wanted to ask you was - Are you absolutely positive
that the copy job has completed copying the file to the secondary server?
Please confirm this by either trying to rename the file (and then renaming
it back to its original name) or using the HANDLE utility from
SYSINTERNALS.COM
As you have mentioned later in your message, it seems like the copy job is
still running. I would advise you to wait for the job to complete.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Hi Pankaj,
Thanks for the reply.
To cut a long story short, I killed the copy process that appeared to have
stalled.
In due course a new process started and the file was restored into the
secondary within a reasonably short amount of time.
I realise that killing a job like that is risky and normally you should
leave it run, but in my case we have a 55 GB database that requries the
log-shipping secondary to be highly available and up-to-date for reporting.
My choices were:
1) Kill the job and hope the secondary came back into synch within a couple
of hours.
2) Remove log-shipping and wait 8+ hours for the secondary to rebuild from
scratch.
I took a gamble and chose option 1.
"Pankaj Agarwal [MSFT]" wrote:

> Hello David
> I'm not clear on the problem description that you have outlined in your
> message. As I understand it, you are mentioning the following sequence of
> events (please correct me if I'm wrong) :
> 1. You had a large tran log backup that has the same timestamp in the
> filename as when the copy job ran.
> 2. You noticed, 5 hours after the copy job ran, that the restore job faile
d
> with the error :
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft]
[ODBC SQL
> Server Driver][SQL Server]Cannot open backup device
> 'H:\SQLData\MSSQL$NODEB\BACKUP\LogShip\O
ASIS_TTC_tlog_200408311200.TRN'.
> Device error or device off-line. See the SQL Server error log for more
> details.
> [Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is
terminating
> abnormally.
> I wanted to point out couple of things here :
> 1. The filename for the transaction log backup does not change through the
> entire log shipping life cycle.
> 2. The filename of the transaction log backup file and the time the copy
> job runs has no relation
> 3. Copy and Restore jobs on the secondary server are 2 different jobs and
> run independent of each other.
> 4. The failure that you see above is reported by the Restore job (hence th
e
> message "RESTORE LOG is terminating abnormally")
> The question that I wanted to ask you was - Are you absolutely positive
> that the copy job has completed copying the file to the secondary server?
> Please confirm this by either trying to rename the file (and then renaming
> it back to its original name) or using the HANDLE utility from
> SYSINTERNALS.COM
> As you have mentioned later in your message, it seems like the copy job is
> still running. I would advise you to wait for the job to complete.
> Thank you for using Microsoft newsgroups.
> Sincerely
> Pankaj Agarwal
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>sql

log shipping encryption

I have a few questions that are bugging me, being a newbie to SQL 2000
Enterprise. I want to set up a warm site server for disaster recovery of my
production server at a remote location. Both Sql servers will be behind
firewalls, (if that matters) and the remote site will not be a production
server; only receiving the shipped transaction logs and being updated
accordingly. For budgetary reasons, they are Internet attached via T1.
1. Is this setup possible?
2. If so, will SQL2000 allow me to encrypt the data it is shipping?
3. Why can't the Chiefs find a defense that will hold the other team to
less than 35 points so I can get rid of my ulcer?
Hoping for all three answers, but hey...2 out of 3 is still a good average.
Thanks in advance.
Hi
The setup is possible.
SQL does not support encryption at transport level, that function belongs to
the OS or network stack. You could connect the 2 servers via Windows VPN.
Setup the dial-up VPN, originating from the local server, to use the network
cards, with no-hangup, and auto-retry.
The VPN then can run compression (big benefit as most log files can be
compressed by up to 70%) and encryption.
Windows VPN is very efficent and is great for security needs like yours.
Regards
Mike
"Rod Hurley" wrote:

> I have a few questions that are bugging me, being a newbie to SQL 2000
> Enterprise. I want to set up a warm site server for disaster recovery of my
> production server at a remote location. Both Sql servers will be behind
> firewalls, (if that matters) and the remote site will not be a production
> server; only receiving the shipped transaction logs and being updated
> accordingly. For budgetary reasons, they are Internet attached via T1.
> 1. Is this setup possible?
> 2. If so, will SQL2000 allow me to encrypt the data it is shipping?
> 3. Why can't the Chiefs find a defense that will hold the other team to
> less than 35 points so I can get rid of my ulcer?
> Hoping for all three answers, but hey...2 out of 3 is still a good average.
> Thanks in advance.

Log Shipping and Reporting Servers

My scenario:
One SQL Server (DB4) in Oklahoma, and it's warm standby (DB3) in Oklahoma
(via Log shipping).
One SQL Server in St. Louis (DB1) , which is a distant warm standby (via Log
shipping) over our WAN.
I want a reporting server in St. Louis. I don't necessary want to set up
another log shipping operation across the WAN with the high amount of
activity that the log shipping is dealing with now.. I would like to grab
the databases off of the warm standby in St. Louis and restore/etc to
another server in St. Louis. I don't want to increase traffic over our WAN
when the data is already on here on this server. The log shipping sent to
the St. Louis server happens every 5 minutes.
Is their a best way to accomplish this? I realized I cannot backup up the
current database because they're in Warm Standby mode. Is is possible to
take them out of this mode, back them up, and put them back in that mode for
the Log Shipping to resume?
TIA,
EricEric
You can not do anything with your warm standby in St
Louis, what you can do is use the transaction log backups
that the log shipping is already copying to the server in
St Louis.
You would need to copy the full database from Oklahoma and
restore it to set up the second warm standby, you could
then use the transaction logs that were already being
coppied.
You would need to then set up your own version of log
shipping using the logs you already have.
Hope this helps.
Regards
John