Log shipping alerts: deleted

Showing posts with label deleted. Show all posts

Friday, March 23, 2012

log shipping problem

So...
I *accidentally* deleted some of the msdb.dbo log shipping tables, could
anyone out there help me out with the table generation scripts so that I
don't have to re-install SQL 2000?
WillieAll the MSDB stuff is in the instmsdb.sql script which I think should be in
the INSTALL directory for your instance. It's unlikely that you understand
log shipping well enough to get everything back to a useable state if only
some of the tables were dropped so your number one option is to restore an
old backup and number to is to ask customer support for help.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
news:OTOVdZHeHHA.1216@.TK2MSFTNGP03.phx.gbl...
> So...
> I *accidentally* deleted some of the msdb.dbo log shipping tables, could
> anyone out there help me out with the table generation scripts so that I
> don't have to re-install SQL 2000?
> Willie
>|||Actually, this got me on the right path, but it was the instls.sql file in
the /install directory that creates the log shipping pieces.
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:910467DF-E344-4246-96E0-EF31FF583BEA@.microsoft.com...
> All the MSDB stuff is in the instmsdb.sql script which I think should be
> in the INSTALL directory for your instance. It's unlikely that you
> understand log shipping well enough to get everything back to a useable
> state if only some of the tables were dropped so your number one option is
> to restore an old backup and number to is to ask customer support for
> help.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
> news:OTOVdZHeHHA.1216@.TK2MSFTNGP03.phx.gbl...
>> So...
>> I *accidentally* deleted some of the msdb.dbo log shipping tables, could
>> anyone out there help me out with the table generation scripts so that I
>> don't have to re-install SQL 2000?
>> Willie
>|||Sorry, I just have SQL 2005 on my box. That's why I was a little vague
about locations.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
news:OrDV8XJeHHA.2188@.TK2MSFTNGP04.phx.gbl...
> Actually, this got me on the right path, but it was the instls.sql file in
> the /install directory that creates the log shipping pieces.
> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
> news:910467DF-E344-4246-96E0-EF31FF583BEA@.microsoft.com...
>> All the MSDB stuff is in the instmsdb.sql script which I think should be
>> in the INSTALL directory for your instance. It's unlikely that you
>> understand log shipping well enough to get everything back to a useable
>> state if only some of the tables were dropped so your number one option
>> is to restore an old backup and number to is to ask customer support for
>> help.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> Use of included script samples are subject to the terms specified at
>> http://www.microsoft.com/info/cpyright.htm
>> "Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
>> news:OTOVdZHeHHA.1216@.TK2MSFTNGP03.phx.gbl...
>> So...
>> I *accidentally* deleted some of the msdb.dbo log shipping tables, could
>> anyone out there help me out with the table generation scripts so that I
>> don't have to re-install SQL 2000?
>> Willie
>>
>|||Once you recover your tables , make sure that you add MSDB and MASTER
databases in your regular backup procedure
Thanks
Hari
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:E0C391EC-AD75-48EC-A5B7-29DAF4D13510@.microsoft.com...
> Sorry, I just have SQL 2005 on my box. That's why I was a little vague
> about locations.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
> news:OrDV8XJeHHA.2188@.TK2MSFTNGP04.phx.gbl...
>> Actually, this got me on the right path, but it was the instls.sql file
>> in the /install directory that creates the log shipping pieces.
>> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
>> news:910467DF-E344-4246-96E0-EF31FF583BEA@.microsoft.com...
>> All the MSDB stuff is in the instmsdb.sql script which I think should be
>> in the INSTALL directory for your instance. It's unlikely that you
>> understand log shipping well enough to get everything back to a useable
>> state if only some of the tables were dropped so your number one option
>> is to restore an old backup and number to is to ask customer support for
>> help.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> Use of included script samples are subject to the terms specified at
>> http://www.microsoft.com/info/cpyright.htm
>> "Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
>> news:OTOVdZHeHHA.1216@.TK2MSFTNGP03.phx.gbl...
>> So...
>> I *accidentally* deleted some of the msdb.dbo log shipping tables,
>> could anyone out there help me out with the table generation scripts so
>> that I don't have to re-install SQL 2000?
>> Willie
>>
>>
>|||Thank you for the help you have already given me, and now I have another
problem with the log shipping. I have 7 databases that I have setup Log
Shipping on and 6 of them work great. The one with issues is a much larger
database (exponentially larger). On the other 6 I have log shipping done at
longer intervals to spread the load out and because they don't see much
activity. On the main database, it seems to work along fine for a while and
then suddenly it can no longer restore the log files. What I have is the SQL
Server installed on the C drive (33.5 GB free), the data is on the D drive
(38.9 GB free) and the log files are on the F drive (262 GB free). The log
files are generally between 2 and 4 MB and the log file that it choked on
was 2,493 kb, with the last one before it being 2,237 kb. I have tried it
with 15 minute intervals between transaction log backups and an hour between
backups, both ending in the same thing: Everything works fine for a while
and then all of a sudden the restore fails and the database is left in the
loading state where it has been for 26 hours now. Does anybody out there
have any suggestions for me? Is there some limit on how many databases you
can setup log shipping on any one machine? Or perhaps any other suggestions
for where I could like for the cause of this problem? Thanks again for any
help you can give me.
Willie
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23tR%23c5LeHHA.928@.TK2MSFTNGP02.phx.gbl...
> Once you recover your tables , make sure that you add MSDB and MASTER
> databases in your regular backup procedure
> Thanks
> Hari
> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
> news:E0C391EC-AD75-48EC-A5B7-29DAF4D13510@.microsoft.com...
>> Sorry, I just have SQL 2005 on my box. That's why I was a little vague
>> about locations.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> Use of included script samples are subject to the terms specified at
>> http://www.microsoft.com/info/cpyright.htm
>> "Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
>> news:OrDV8XJeHHA.2188@.TK2MSFTNGP04.phx.gbl...
>> Actually, this got me on the right path, but it was the instls.sql file
>> in the /install directory that creates the log shipping pieces.
>> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
>> news:910467DF-E344-4246-96E0-EF31FF583BEA@.microsoft.com...
>> All the MSDB stuff is in the instmsdb.sql script which I think should
>> be in the INSTALL directory for your instance. It's unlikely that you
>> understand log shipping well enough to get everything back to a useable
>> state if only some of the tables were dropped so your number one option
>> is to restore an old backup and number to is to ask customer support
>> for help.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> Use of included script samples are subject to the terms specified at
>> http://www.microsoft.com/info/cpyright.htm
>> "Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
>> news:OTOVdZHeHHA.1216@.TK2MSFTNGP03.phx.gbl...
>> So...
>> I *accidentally* deleted some of the msdb.dbo log shipping tables,
>> could anyone out there help me out with the table generation scripts
>> so that I don't have to re-install SQL 2000?
>> Willie
>>
>>
>

Wednesday, March 21, 2012

Log Shipping out of sync

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:
[vbcol=seagreen]
> 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 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:
[vbcol=seagreen]
> 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:
|||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:
[vbcol=seagreen]
> 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:
sql

Friday, March 23, 2012

log shipping problem

log shipping problem

log shipping problem

Wednesday, March 21, 2012

Log Shipping out of sync

Log shipping alerts

Blog Archive

About Me