Log Shipping out of sync

Hi everyone,
We have set up a log shipping between two SQL 2000 servers with a monitor
server as well.
The problem is that I've been on holidays, and the people that should check
the databases didn't do it. The result is that the log shipping has been out
ot sync since 18th april till now.
We get the following error:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3242: [Microsoft][ODBC SQL
Server Driver][SQL Server]The file on device
'C:\xx\LogShipIN\TRNs\xx_tlog_200704180300.TRN' is not a valid Microsoft Tape
Format backup set.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating
abnormally.
I've check the file and already exist in backup server, but it seems it
wasn't copied correctly. Because it's a lot of days since the file fail to
restore (it's from 18 th april), we don't have it on production server (we
have only from 2 days ago).
Does anybody knows how could I re-sync the log shipping on backup server
again?
Thansk a lot,
Alex.You need to recreate the Log Shipping Maintenance plan for the desired
database.
In order to deleted the current plan, you'll need to remove LS from the plan
first.
If the latest full db backup that you have was created after the error
occurred and you still have all of the log (and/or differential) backups
since the latest db backup then you can use these files to reinitialize db on
the secondary server instead of having to take a full backup of the db. This
would allow you to set up log shipping without having to impact performance
on your primary db.
check out these articles
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/logship1.mspx
http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part4/c1361.mspx?mfr=true
MG
"Alex Delgado" wrote:
> Hi everyone,
> We have set up a log shipping between two SQL 2000 servers with a monitor
> server as well.
> The problem is that I've been on holidays, and the people that should check
> the databases didn't do it. The result is that the log shipping has been out
> ot sync since 18th april till now.
> We get the following error:
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3242: [Microsoft][ODBC SQL
> Server Driver][SQL Server]The file on device
> 'C:\xx\LogShipIN\TRNs\xx_tlog_200704180300.TRN' is not a valid Microsoft Tape
> Format backup set.
> [Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating
> abnormally.
> I've check the file and already exist in backup server, but it seems it
> wasn't copied correctly. Because it's a lot of days since the file fail to
> restore (it's from 18 th april), we don't have it on production server (we
> have only from 2 days ago).
> Does anybody knows how could I re-sync the log shipping on backup server
> again?
> Thansk a lot,
> Alex.|||Hi Hurme,
Thanks a lot for your help.
I still have a doubt.
When you say:
" If the latest full db backup that you have was created after the error
occurred and you still have all of the log (and/or differential) backups
since the latest db backup then you can use these files to reinitialize db on
the secondary server instead of having to take a full backup of the db. This
would allow you to set up log shipping without having to impact performance
on your primary db. "
I have full db backup created after the error that we can restore on
secondary server. About the log backups, I understand you mean the trn files
that log shipping copies to secondary server and that it tries to restore,
don't you? If so, then my answer is yes, I also have them.
Should I run the restore with the following parameter: restore
"Hurme" wrote:
> You need to recreate the Log Shipping Maintenance plan for the desired
> database.
> In order to deleted the current plan, you'll need to remove LS from the plan
> first.
> If the latest full db backup that you have was created after the error
> occurred and you still have all of the log (and/or differential) backups
> since the latest db backup then you can use these files to reinitialize db on
> the secondary server instead of having to take a full backup of the db. This
> would allow you to set up log shipping without having to impact performance
> on your primary db.
> check out these articles
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/logship1.mspx
> http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part4/c1361.mspx?mfr=true
>
> --
> MG
>
> "Alex Delgado" wrote:
> > Hi everyone,
> >
> > We have set up a log shipping between two SQL 2000 servers with a monitor
> > server as well.
> >
> > The problem is that I've been on holidays, and the people that should check
> > the databases didn't do it. The result is that the log shipping has been out
> > ot sync since 18th april till now.
> >
> > We get the following error:
> >
> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3242: [Microsoft][ODBC SQL
> > Server Driver][SQL Server]The file on device
> > 'C:\xx\LogShipIN\TRNs\xx_tlog_200704180300.TRN' is not a valid Microsoft Tape
> > Format backup set.
> > [Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating
> > abnormally.
> >
> > I've check the file and already exist in backup server, but it seems it
> > wasn't copied correctly. Because it's a lot of days since the file fail to
> > restore (it's from 18 th april), we don't have it on production server (we
> > have only from 2 days ago).
> >
> > Does anybody knows how could I re-sync the log shipping on backup server
> > again?
> >
> > Thansk a lot,
> >
> > Alex.|||Hi Hurme,
Thanks a lot for your help.
I still have a doubt.
When you say:
" If the latest full db backup that you have was created after the error
occurred and you still have all of the log (and/or differential) backups
since the latest db backup then you can use these files to reinitialize db on
the secondary server instead of having to take a full backup of the db. This
would allow you to set up log shipping without having to impact performance
on your primary db. "
I have full db backup created after the error that we can restore on
secondary server. About the log backups, I understand you mean the trn files
that log shipping copies to secondary server and that it tries to restore,
don't you? If so, then my answer is yes, I also have them.
Should I run the restore with the following parameter: restore with standby'
How should be the exact command to restore?
Thanks again,
Alex.
"Hurme" wrote:
> You need to recreate the Log Shipping Maintenance plan for the desired
> database.
> In order to deleted the current plan, you'll need to remove LS from the plan
> first.
> If the latest full db backup that you have was created after the error
> occurred and you still have all of the log (and/or differential) backups
> since the latest db backup then you can use these files to reinitialize db on
> the secondary server instead of having to take a full backup of the db. This
> would allow you to set up log shipping without having to impact performance
> on your primary db.
> check out these articles
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/logship1.mspx
> http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part4/c1361.mspx?mfr=true
>
> --
> MG
>
> "Alex Delgado" wrote:
> > Hi everyone,
> >
> > We have set up a log shipping between two SQL 2000 servers with a monitor
> > server as well.
> >
> > The problem is that I've been on holidays, and the people that should check
> > the databases didn't do it. The result is that the log shipping has been out
> > ot sync since 18th april till now.
> >
> > We get the following error:
> >
> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3242: [Microsoft][ODBC SQL
> > Server Driver][SQL Server]The file on device
> > 'C:\xx\LogShipIN\TRNs\xx_tlog_200704180300.TRN' is not a valid Microsoft Tape
> > Format backup set.
> > [Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating
> > abnormally.
> >
> > I've check the file and already exist in backup server, but it seems it
> > wasn't copied correctly. Because it's a lot of days since the file fail to
> > restore (it's from 18 th april), we don't have it on production server (we
> > have only from 2 days ago).
> >
> > Does anybody knows how could I re-sync the log shipping on backup server
> > again?
> >
> > Thansk a lot,
> >
> > Alex.|||Yes, by log I meant the .trn files.
If you don't use standby, the backup database will be left offline and not
readable so that successive .trn (backup log) files can be restored.
If you restore with standby the backup db will be accessable in Read-Only
mode and still be able to have successive .trn files restored to it. The only
issue with this is that exclussive access to the db is still need to restore
a .trn so if some one is accessing the db they need to be kicked out of the
db before a restore can happen.
The restore with standby command should look like:
restore database <db_name>
from disk = 'c:\<backup>.BAK'
with standby = <undo_file_name>
likewise for .trn files
restore log <db_name>
from disk = 'c:\<log_backup>.trn'
with standby = <undo_file_name>
If you're not using it, try using Enterprise Manager to set up Log Shipping.
--
MG
"Alex Delgado" wrote:
> Hi Hurme,
> Thanks a lot for your help.
> I still have a doubt.
> When you say:
> " If the latest full db backup that you have was created after the error
> occurred and you still have all of the log (and/or differential) backups
> since the latest db backup then you can use these files to reinitialize db on
> the secondary server instead of having to take a full backup of the db. This
> would allow you to set up log shipping without having to impact performance
> on your primary db. "
> I have full db backup created after the error that we can restore on
> secondary server. About the log backups, I understand you mean the trn files
> that log shipping copies to secondary server and that it tries to restore,
> don't you? If so, then my answer is yes, I also have them.
> Should I run the restore with the following parameter: restore with standby'
> How should be the exact command to restore?
> Thanks again,
> Alex.
>
> "Hurme" wrote:
> > You need to recreate the Log Shipping Maintenance plan for the desired
> > database.
> > In order to deleted the current plan, you'll need to remove LS from the plan
> > first.
> >
> > If the latest full db backup that you have was created after the error
> > occurred and you still have all of the log (and/or differential) backups
> > since the latest db backup then you can use these files to reinitialize db on
> > the secondary server instead of having to take a full backup of the db. This
> > would allow you to set up log shipping without having to impact performance
> > on your primary db.
> >
> > check out these articles
> > http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/logship1.mspx
> >
> > http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part4/c1361.mspx?mfr=true
> >
> >
> > --
> > MG
> >
> >
> > "Alex Delgado" wrote:
> >
> > > Hi everyone,
> > >
> > > We have set up a log shipping between two SQL 2000 servers with a monitor
> > > server as well.
> > >
> > > The problem is that I've been on holidays, and the people that should check
> > > the databases didn't do it. The result is that the log shipping has been out
> > > ot sync since 18th april till now.
> > >
> > > We get the following error:
> > >
> > > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3242: [Microsoft][ODBC SQL
> > > Server Driver][SQL Server]The file on device
> > > 'C:\xx\LogShipIN\TRNs\xx_tlog_200704180300.TRN' is not a valid Microsoft Tape
> > > Format backup set.
> > > [Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating
> > > abnormally.
> > >
> > > I've check the file and already exist in backup server, but it seems it
> > > wasn't copied correctly. Because it's a lot of days since the file fail to
> > > restore (it's from 18 th april), we don't have it on production server (we
> > > have only from 2 days ago).
> > >
> > > Does anybody knows how could I re-sync the log shipping on backup server
> > > again?
> > >
> > > Thansk a lot,
> > >
> > > Alex.|||On Apr 24, 1:50 pm, Alex Delgado
<AlexDelg...@.discussions.microsoft.com> wrote:
> Hi everyone,
> We have set up a log shipping between two SQL 2000 servers with a monitor
> server as well.
> The problem is that I've been on holidays, and the people that should check
> the databases didn't do it. The result is that the log shipping has been out
> ot sync since 18th april till now.
> We get the following error:
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3242: [Microsoft][ODBC SQL
> Server Driver][SQL Server]The file on device
> 'C:\xx\LogShipIN\TRNs\xx_tlog_200704180300.TRN' is not a valid Microsoft Tape
> Format backup set.
> [Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating
> abnormally.
> I've check the file and already exist in backup server, but it seems it
> wasn't copied correctly. Because it's a lot of days since the file fail to
> restore (it's from 18 th april), we don't have it on production server (we
> have only from 2 days ago).
> Does anybody knows how could I re-sync the log shipping on backup server
> again?
> Thansk a lot,
> Alex.
You should have valid backup. If backup on tape is not O.K then you
can not restore next subsequent log backups. You have to setup
logshipping again,
Regards
Amish Shah
http://shahamishm.tripod.com|||Hi Hurme,
Thanks againg for your help.
I still have a doubt. Before performing the restore on secondary server,
should I disable/delete logshipping, or could I restore the database on
secondary without disabling/deleting logshipping?
If I had to disable/delete logshipping, should I delete the trn log files
that are on secondary and that have been copied before recreating the
logshipping? I understand yes.
I had to restore with standby, because there are users that access this
database in read mode on secondary server.
Thanks,
Alex.
"Hurme" wrote:
> Yes, by log I meant the .trn files.
> If you don't use standby, the backup database will be left offline and not
> readable so that successive .trn (backup log) files can be restored.
> If you restore with standby the backup db will be accessable in Read-Only
> mode and still be able to have successive .trn files restored to it. The only
> issue with this is that exclussive access to the db is still need to restore
> a .trn so if some one is accessing the db they need to be kicked out of the
> db before a restore can happen.
> The restore with standby command should look like:
> restore database <db_name>
> from disk = 'c:\<backup>.BAK'
> with standby = <undo_file_name>
> likewise for .trn files
> restore log <db_name>
> from disk = 'c:\<log_backup>.trn'
> with standby = <undo_file_name>
> If you're not using it, try using Enterprise Manager to set up Log Shipping.
> --
> MG
>
> "Alex Delgado" wrote:
> > Hi Hurme,
> >
> > Thanks a lot for your help.
> >
> > I still have a doubt.
> >
> > When you say:
> >
> > " If the latest full db backup that you have was created after the error
> > occurred and you still have all of the log (and/or differential) backups
> > since the latest db backup then you can use these files to reinitialize db on
> > the secondary server instead of having to take a full backup of the db. This
> > would allow you to set up log shipping without having to impact performance
> > on your primary db. "
> >
> > I have full db backup created after the error that we can restore on
> > secondary server. About the log backups, I understand you mean the trn files
> > that log shipping copies to secondary server and that it tries to restore,
> > don't you? If so, then my answer is yes, I also have them.
> >
> > Should I run the restore with the following parameter: restore with standby'
> >
> > How should be the exact command to restore?
> >
> > Thanks again,
> >
> > Alex.
> >
> >
> > "Hurme" wrote:
> >
> > > You need to recreate the Log Shipping Maintenance plan for the desired
> > > database.
> > > In order to deleted the current plan, you'll need to remove LS from the plan
> > > first.
> > >
> > > If the latest full db backup that you have was created after the error
> > > occurred and you still have all of the log (and/or differential) backups
> > > since the latest db backup then you can use these files to reinitialize db on
> > > the secondary server instead of having to take a full backup of the db. This
> > > would allow you to set up log shipping without having to impact performance
> > > on your primary db.
> > >
> > > check out these articles
> > > http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/logship1.mspx
> > >
> > > http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part4/c1361.mspx?mfr=true
> > >
> > >
> > > --
> > > MG
> > >
> > >
> > > "Alex Delgado" wrote:
> > >
> > > > Hi everyone,
> > > >
> > > > We have set up a log shipping between two SQL 2000 servers with a monitor
> > > > server as well.
> > > >
> > > > The problem is that I've been on holidays, and the people that should check
> > > > the databases didn't do it. The result is that the log shipping has been out
> > > > ot sync since 18th april till now.
> > > >
> > > > We get the following error:
> > > >
> > > > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3242: [Microsoft][ODBC SQL
> > > > Server Driver][SQL Server]The file on device
> > > > 'C:\xx\LogShipIN\TRNs\xx_tlog_200704180300.TRN' is not a valid Microsoft Tape
> > > > Format backup set.
> > > > [Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating
> > > > abnormally.
> > > >
> > > > I've check the file and already exist in backup server, but it seems it
> > > > wasn't copied correctly. Because it's a lot of days since the file fail to
> > > > restore (it's from 18 th april), we don't have it on production server (we
> > > > have only from 2 days ago).
> > > >
> > > > Does anybody knows how could I re-sync the log shipping on backup server
> > > > again?
> > > >
> > > > Thansk a lot,
> > > >
> > > > Alex.