Log shipping alerts: pair

Showing posts with label pair. Show all posts

Wednesday, March 28, 2012

Log Shipping Role Reversal

I have log shipping configured on a pair of test SQL 2000 servers, both are
at SP3. The log shipping is working fine, but the problem occurs when I try
to reverse roles. I run this SQL statement on the primary server:
USE master
GO
EXEC msdb.dbo.sp_change_primary_role
@.db_name = 'ModelCopy',
@.backup_log = 1,
@.terminate = 1,
@.final_state = 3,
@.access_level = 1
This places the primary database into read-only mode. I then run this SQL
statement on the secondary server.
USE master
GO
EXEC msdb.dbo.sp_change_secondary_role
@.db_name = 'ModelCopy',
@.do_load = 1,
@.force_load = 1,
@.final_state = 1,
@.access_level = 1,
@.terminate = 1,
@.keep_replication = 0,
@.stopat = null
At which time I get this error:
Server: Msg 927, Level 14, State 2, Line 1
Database 'ModelCopy' cannot be opened. It is in the middle of a restore.
Server: Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Server: Msg 14440, Level 16, State 1, Procedure sp_change_secondary_role,
Line 49
Could not set single user mode.
In each case, I am runnig the statements in Query Analyzer with the
Enterprise Manager window closed. I understand that these statements cause
the primary to make a final copy and the secondary to do a final restore, but
the database I'm using is very small (a copy of the Model databae) and
shouldn't take but a few seconds, which is all the time it took to create and
initialize the database on the secondary to begin with. Is this process hung,
or am I just being impatient? It's been over 20 minutes.
TIA,
Ken
Ken,
See the KB article 294397 BUG: sp_change_secondary_role Fails with Error
3101 if There Are Outstanding Transaction Log Backups
http://support.microsoft.com/default...&Product=sql2k
This is similar, though not identical, to the error you are getting. The
upshot is that you need to make sure that all the transaction logs have been
restored if you set the @.terminate parameter to 1.
Hope this helps,
Ron
Ron Talmage
SQL Server MVP
"kmkrause2" <kmkrause2@.discussions.microsoft.com> wrote in message
news:733456B1-BC99-43B2-B6BD-4030A08DB6EC@.microsoft.com...
> I have log shipping configured on a pair of test SQL 2000 servers, both
are
> at SP3. The log shipping is working fine, but the problem occurs when I
try
> to reverse roles. I run this SQL statement on the primary server:
> USE master
> GO
> EXEC msdb.dbo.sp_change_primary_role
> @.db_name = 'ModelCopy',
> @.backup_log = 1,
> @.terminate = 1,
> @.final_state = 3,
> @.access_level = 1
> This places the primary database into read-only mode. I then run this SQL
> statement on the secondary server.
> USE master
> GO
> EXEC msdb.dbo.sp_change_secondary_role
> @.db_name = 'ModelCopy',
> @.do_load = 1,
> @.force_load = 1,
> @.final_state = 1,
> @.access_level = 1,
> @.terminate = 1,
> @.keep_replication = 0,
> @.stopat = null
> At which time I get this error:
> Server: Msg 927, Level 14, State 2, Line 1
> Database 'ModelCopy' cannot be opened. It is in the middle of a restore.
> Server: Msg 5069, Level 16, State 1, Line 1
> ALTER DATABASE statement failed.
> Server: Msg 14440, Level 16, State 1, Procedure sp_change_secondary_role,
> Line 49
> Could not set single user mode.
> In each case, I am runnig the statements in Query Analyzer with the
> Enterprise Manager window closed. I understand that these statements cause
> the primary to make a final copy and the secondary to do a final restore,
but
> the database I'm using is very small (a copy of the Model databae) and
> shouldn't take but a few seconds, which is all the time it took to create
and
> initialize the database on the secondary to begin with. Is this process
hung,
> or am I just being impatient? It's been over 20 minutes.
> TIA,
> Ken
|||Thanks for the info Ron. It pointed me in the right direction. I still needed
to to a detach/attach sequesnce on the database before running the secondary
stored procedure, but at least it is working in a predictable manner now.
"Ron Talmage" wrote:

> Ken,
> See the KB article 294397 BUG: sp_change_secondary_role Fails with Error
> 3101 if There Are Outstanding Transaction Log Backups
> http://support.microsoft.com/default...&Product=sql2k
> This is similar, though not identical, to the error you are getting. The
> upshot is that you need to make sure that all the transaction logs have been
> restored if you set the @.terminate parameter to 1.
> Hope this helps,
> Ron
> --
> Ron Talmage
> SQL Server MVP
> "kmkrause2" <kmkrause2@.discussions.microsoft.com> wrote in message
> news:733456B1-BC99-43B2-B6BD-4030A08DB6EC@.microsoft.com...
> are
> try
> but
> and
> hung,
>
>
|||OK, I spoke too soon. Occasionally, the sp_change_secondary_role procedure
will end with a "sqlmaint.exe failed. [SQLSTATE 42000][Error 22029]. The step
failed." message. I haven't been able to find any helpful information on this
message yet. Can you shed some more light on what is happening during this
procedure? Same scripts as posted previously in this thread, and include the
manual running of the restore job and a database detach/attach sequence in
Enterprise Manager immediately prior to running the secondary role change
stpred procedure.
Thanks Again,
Ken
"kmkrause2" wrote:
[vbcol=seagreen]
> Thanks for the info Ron. It pointed me in the right direction. I still needed
> to to a detach/attach sequesnce on the database before running the secondary
> stored procedure, but at least it is working in a predictable manner now.
> "Ron Talmage" wrote:

Wednesday, March 21, 2012

Log Shipping Monitor Shows Problems when there aren't any

I Have setup a pair of server with log shipping, it works perfectly, logs are
backed up every 2 mins and restored succesfully to the destination server. My
only problem is that the monitor (running on the backup server) thinks that
things are not working correctly. It shows the correct times for the copy and
load jobs but the backup job time never gets updated. I have read the MS KB
article 292586 which states that this could be a permissions issue that the
source server does not have permissions to update the msdb database on the
destination server, however I have tried changing things in this area by
having the sql account running with full admin privs (both domain admin and
sql admin) this didnt work, I also tried running sql in mixed auth mode and
then configurung log shipping to use SQL authentication, it correctly created
the new log_shipping_monitor account, but still the log shipping monitor
refuses to work. Can somebody help me before I pull all my hair out.
Brett
Hello Brett,
My suggestion is that you reset password for log_shipping_monitor_probe
account on all shipping databases (both secondary and primary) and removed
and
reapplied permissions to the log shipping tables on both primary and
secondary servers. If log_shipping_monitor_prob does not exist, create the
login, set the default
database to msdb, and grant it access to msdb.
On both Primary / Secondary server, right click the log shipping database,
select properties. In the general tab, click the details button for log
shipping. Select "Use SQL Server Authentication" and change the password
to log_shipping_monitor_prob account.
More related information:
321247 How To Configure Security for Log Shipping
http://support.microsoft.com/?id=321247
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Log Shipping Monitor Shows Problems when there aren't any
>thread-index: AcX4xD/DRKnfVG7nSZmLj/7UPPi8Vg==
>X-WBNR-Posting-Host: 194.109.239.116
>From: "=?Utf-8?B?QnJldHQgQ2Fycg==?=" <brettcarr@.news.postalias>
>Subject: Log Shipping Monitor Shows Problems when there aren't any
>Date: Sun, 4 Dec 2005 03:17:01 -0800
>Lines: 15
>Message-ID: <0E563B60-3095-4635-BC0A-3153BEB87CDB@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.replication
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGXA03.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.replication:67206
>X-Tomcat-NG: microsoft.public.sqlserver.replication
>I Have setup a pair of server with log shipping, it works perfectly, logs
are
>backed up every 2 mins and restored succesfully to the destination server.
My
>only problem is that the monitor (running on the backup server) thinks
that
>things are not working correctly. It shows the correct times for the copy
and
>load jobs but the backup job time never gets updated. I have read the MS
KB
>article 292586 which states that this could be a permissions issue that
the
>source server does not have permissions to update the msdb database on the
>destination server, however I have tried changing things in this area by
>having the sql account running with full admin privs (both domain admin
and
>sql admin) this didnt work, I also tried running sql in mixed auth mode
and
>then configurung log shipping to use SQL authentication, it correctly
created
>the new log_shipping_monitor account, but still the log shipping monitor
>refuses to work. Can somebody help me before I pull all my hair out.
>Brett
>
|||"Peter Yang [MSFT]" wrote:

> Hello Brett,
> My suggestion is that you reset password for log_shipping_monitor_probe
> account on all shipping databases (both secondary and primary) and removed
> and
> reapplied permissions to the log shipping tables on both primary and
> secondary servers. If log_shipping_monitor_prob does not exist, create the
> login, set the default
> database to msdb, and grant it access to msdb.
> On both Primary / Secondary server, right click the log shipping database,
> select properties. In the general tab, click the details button for log
> shipping. Select "Use SQL Server Authentication" and change the password
> to log_shipping_monitor_prob account.
>
Ok. I've done the following:
1. log_shipping_monitor_prob did not exist on the primary server so I
created it.
2. I reset the password for log_shipping_monitor_prob on both primary and
secondary.
3. On both primary and secondary servers I have set
log_shipping_monitor_prob to have select,insert,update and delete permissions
on all tables starting with log_shipping in the msdb database.
However I'm afraid the 'Last Backup' date and time listed in the monitor is
still the date and time I set it up originally. The Last copy and Last
restore dates are still being updated correctly.
Brett..
|||Hello Brett,
Going forward, I'd like to confirm if Windows Authentication option is
selected on the Specify The Log Shipping Monitor Server Information screen
during log shipping setup.
If so, you may want to check if the SQL Server Service startup account on
the primary server of a log shipping pair does not have sufficient
privileges to update table msdb..log_shipping_primaries on the monitor
server.
Also, please check if the value in primary_server_name column in
log_shipping_primaries does not match the <@.@.servername> of the primary
server.
If the issue persists, please use SQL profiler on the monitor server to see
if the update operation on log_shipping_primaries occurs or if there is any
related errors in error log.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Log Shipping Monitor Shows Problems when there aren't any
>thread-index: AcX5iaV+Rf+MfqKZTvqVdLuPrgGVlw==
>X-WBNR-Posting-Host: 193.0.2.180
>From: "=?Utf-8?B?QnJldHQgQ2Fycg==?=" <brettcarr@.news.postalias>
>References: <0E563B60-3095-4635-BC0A-3153BEB87CDB@.microsoft.com>
<VAQE#gX#FHA.1236@.TK2MSFTNGXA02.phx.gbl>[vbcol=seagreen]
>Subject: RE: Log Shipping Monitor Shows Problems when there aren't any
>Date: Mon, 5 Dec 2005 02:50:03 -0800
>Lines: 35
>Message-ID: <33C3B088-9CA3-4C3E-98B1-86E784833DDC@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.replication
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.replication:67231
>X-Tomcat-NG: microsoft.public.sqlserver.replication
>"Peter Yang [MSFT]" wrote:
removed[vbcol=seagreen]
the[vbcol=seagreen]
database,[vbcol=seagreen]
password
>Ok. I've done the following:
>1. log_shipping_monitor_prob did not exist on the primary server so I
>created it.
>2. I reset the password for log_shipping_monitor_prob on both primary and
>secondary.
>3. On both primary and secondary servers I have set
>log_shipping_monitor_prob to have select,insert,update and delete
permissions
>on all tables starting with log_shipping in the msdb database.
>However I'm afraid the 'Last Backup' date and time listed in the monitor
is
>still the date and time I set it up originally. The Last copy and Last
>restore dates are still being updated correctly.
>Brett..
>
>

Friday, March 9, 2012

Log shipping fail over

I assume this is the correct spot for a log shipping question. Pardon if
it's not.
I've set up a log shipping pair (first time) and everything 'appears' to be
running fine, but I have a question about fail over.
In my research I have found that, in order to bring the secondary up as the
primary, I have to run a few procedures. The first of which is to be run on
the primary server to change its role. How would I do this if the server has
crashed? What steps should I take if this is the scenario?
Thanks in advance.
So what's the best fail over method should the primary server crash? This is
how I see it,
1) Run sp_change_secondary_role on the secondary server.
2) Switch app to point to secondary server or rename and re-IP secondary
server.
3) The users log into the application and the app uses one SQL login, so I
assume I don't need to worry too much about resolving logins.
This will get my users back to functional? What about once the old primary
is back up? Should I then run sp_change_primary_role? Or would it be best
to delete the maintenance jobs and recreate the log shipping pair?
Sorry about all of the questions, I've seen plenty of information on how to
change roles if the primary is still functional, but nothing concerning a
primary server crash.
Again, thanks in advance.
"Paul Ibison" wrote:

> Mick,
> you're correct - sp_change_primary_role removes the
> primary server from the maintenance plan and it will not
> usually be possible to do this in reality. The key
> failover procedure is sp_change_secondary_role which gets
> the last log and restores the system with recovery.
> Rgds,
> Paul Ibison (SQL Server MVP)
>
>
|||Mick,
Run sp_change_secondary_role, rename the server and use sp_dropserver,
sp_addserver, restart services. Create the login you require and use
sp_change_users_login if you haven't taken the SID.
There's no simple way of switching back roles to the primary, apart from
setting up a new log shipping pair and reversing the process.
The old maintenance plan at this stage is now defunct and needs to be
removed if the primary server comes online at some later stage.
HTH,
Paul Ibison (SQL Server MVP)
[vbcol=seagreen]

Wednesday, March 7, 2012

Log Shipping and Recovery

I have just created a pair of log shipping SQL Servers.
Both are running SQL Server 2000 sp3 on Win2k sp3.
I've read the documentation on how to change roles between
the servers, but it assumes that both servers are
available for a controlled change. What if the primary
server is destroyed? How can I make the secondary server
the primary in an emergency situation? I know I can't just
change the database on the secondary from Read-Only to
Normal. What has to happen in order for this secondary
server to become active?
TIA,
Ken> I know I can't just
> change the database on the secondary from Read-Only to
> Normal.
Why do you say that? All you should need to do is:
RESTORE DATABASE dbname WITH RECOVERY
Above assumes that EM's log shipping doesn't do anything strange.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Ken Krause" <kenkrause@.promaxautonews.com> wrote in message
news:005601c3aba4$e6b6c5f0$a401280a@.phx.gbl...
> I have just created a pair of log shipping SQL Servers.
> Both are running SQL Server 2000 sp3 on Win2k sp3.
> I've read the documentation on how to change roles between
> the servers, but it assumes that both servers are
> available for a controlled change. What if the primary
> server is destroyed? How can I make the secondary server
> the primary in an emergency situation? I know I can't just
> change the database on the secondary from Read-Only to
> Normal. What has to happen in order for this secondary
> server to become active?
> TIA,
> Ken|||I have documentation for this, send me an email
and I will reply with the information.
>--Original Message--
>I have just created a pair of log shipping SQL Servers.
>Both are running SQL Server 2000 sp3 on Win2k sp3.
>I've read the documentation on how to change roles
between
>the servers, but it assumes that both servers are
>available for a controlled change. What if the primary
>server is destroyed? How can I make the secondary server
>the primary in an emergency situation? I know I can't
just
>change the database on the secondary from Read-Only to
>Normal. What has to happen in order for this secondary
>server to become active?
>TIA,
>Ken
>.
>|||The book Microsoft SQL Server 2000 High Availability
covers this in the Log Shipping chapter. Making a role
change has nothing to do with whether the primary is
available ... you can make it, but you may possibly lose
the last transactions if you can't get the tail of the
log. You're only as good as your last tran log you have
access to.
If you have the DB in read-only mode, this is incorrect.
I assume it is in STANDBY, which shows the DB as read-only.
>--Original Message--
>I have just created a pair of log shipping SQL Servers.
>Both are running SQL Server 2000 sp3 on Win2k sp3.
>I've read the documentation on how to change roles
between
>the servers, but it assumes that both servers are
>available for a controlled change. What if the primary
>server is destroyed? How can I make the secondary server
>the primary in an emergency situation? I know I can't
just
>change the database on the secondary from Read-Only to
>Normal. What has to happen in order for this secondary
>server to become active?
>TIA,
>Ken
>.
>

Monday, February 20, 2012

Log Shipping : Out of Sync

I set up log shipping,

after a while the log shipping pair in LogShip Monitor is out of sync.

what cause this & how to solve this problems.

thx

One common reason is if you truncate the transaction log on the source database (or set it to simple recovery model).

Some other possible causes ;

1) Check that the destination still has access to the source share that contains the transaction logs,

2) Check that the destination directory has not run out of disk space to receive the source transaction logs

Depending on what the problem is, you may need to set it up again. Check the last restore time and last copy time.

Hope this helps.

|||

Hi Rodge, thank you for your reply.

For no.1 question, Yes, the target can acces the source.

For no.2 question, Destination directory space is large enough,

anyway this is a test before implementation, so i only use very small database, less than 2MB.

in Logshipping pair properties, it shows:

status : Backup Not occuring

Last Backup File : first_file_0000000000000.trn

Backup delta 960 minutes

Last File copied : D700_tlog_200608300939.trn

Copy delta -958 minutes

Last File Loaded : D700_tlog_200608300936.trn

Load delta -955 minutes

If we check View Backup History, in Log Shipping pair

for a moment, the backup database & the verify Backup , and backup transaction status shows checkmark (it seems OK)

but finally only backup transaction log shows, the backup database and verify backup is gone. it seems the backup database stops.

thx

|||

my log shipping is working fine now

Everything is ok after i reinstall the OS, and SQL

i will try to bring the secondary server online

thx folks

|||After setting it up successfully, monitor what is happening. You can check out my blog on my share of experiences with log shipping at http://bassplayerdoc.spaces.live.com

Log shipping - moving the monitor server

Hi all,
We have a log shipping pair, and a monitoring server. The monitoring server
is being retired, so I need to find out if there is a way to move/create a
log shipping monitor server on a new server, without effecting the
primary/secondary log shipping pair? Anyone tried this before?
Thanks,
daveHi David
I have never tried this, I would expect if your new server is the same name
as the old one and you move the databases using attach/detach from the old
server then it I could be relatively trouble free... but you don't give any
details about the replacement!!!
John
"David Curlewis" wrote:
> Hi all,
> We have a log shipping pair, and a monitoring server. The monitoring server
> is being retired, so I need to find out if there is a way to move/create a
> log shipping monitor server on a new server, without effecting the
> primary/secondary log shipping pair? Anyone tried this before?
> Thanks,
> dave|||Here the details are then; the server that currently acts as monitor is going
to be "reassigned" to another role, which will require it being completely
rebuilt, arrays reconfigured, SANs changed, etc. It is therefore likely that
I will be able to keep the same server name, and could detach and reattach
the databases.
But what databases contain the replication information, and can they be
successfully re-attached to a new install of SQL? Is it the MSDB database
mainly, or are there tables in Master as well? All servers are running
SQL2000 SP4 on Windows 2003 Server SP1.
Thanks,
Dave
"John Bell" wrote:
> Hi David
> I have never tried this, I would expect if your new server is the same name
> as the old one and you move the databases using attach/detach from the old
> server then it I could be relatively trouble free... but you don't give any
> details about the replacement!!!
> John|||This is a multi-part message in MIME format.
--050800070200090007000605
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Log shipping info, on the primary, secondary & monitor boxes, is kept in
the msdb.dbo.log_shipping_* tables. So the msdb database should be the
only DB relevant to log shipping on the monitor box. However, you'll
need to make sure the SQL logins you use for inter-server communication
are the same on the new monitor server (and that login info is stored in
the master database).
If it was me, I'd be thinking about setting up my new monitor box,
restoring the master DB from a backup of your current master DB on the
monitor box (see Restoring the master Database from a Current Backup
<http://msdn.microsoft.com/library/en-us/adminsql/ad_bkprst_4g4w.asp>)
and then restoring the msdb database (Restoring the model, msdb, and
distribution Databases
<http://msdn.microsoft.com/library/en-us/adminsql/ad_bkprst_2w1f.asp>).
I'm not sure if that will work (as I haven't tested it before), but that
would be the recovery strategy I'd start with to recover or replace the
monitor box involved in log shipping.
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
DavidCur wrote:
>Here the details are then; the server that currently acts as monitor is going
>to be "reassigned" to another role, which will require it being completely
>rebuilt, arrays reconfigured, SANs changed, etc. It is therefore likely that
>I will be able to keep the same server name, and could detach and reattach
>the databases.
>But what databases contain the replication information, and can they be
>successfully re-attached to a new install of SQL? Is it the MSDB database
>mainly, or are there tables in Master as well? All servers are running
>SQL2000 SP4 on Windows 2003 Server SP1.
>Thanks,
>Dave
>"John Bell" wrote:
>
>>Hi David
>>I have never tried this, I would expect if your new server is the same name
>>as the old one and you move the databases using attach/detach from the old
>>server then it I could be relatively trouble free... but you don't give any
>>details about the replacement!!!
>>John
>>
--050800070200090007000605
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Log shipping info, on the primary, secondary & monitor boxes,
is kept in the msdb.dbo.log_shipping_* tables.Â So the msdb database
should be the only DB relevant to log shipping on the monitor box.Â
However, you'll need to make sure the SQL logins you use for
inter-server communication are the same on the new monitor server (and
that login info is stored in the master database). 
 
If it was me, I'd be thinking about setting up my new monitor box,
restoring the master DB from a backup of your current master DB on the
monitor box (see <a
href="http://links.10026.com/?link=Restoring">http://msdn.microsoft.com/library/en-us/adminsql/ad_bkprst_4g4w.asp">Restoring
the master Database from a Current Backup</a></tt><tt>) and then
restoring the msdb database (<a
href="http://links.10026.com/?link=Restoring">http://msdn.microsoft.com/library/en-us/adminsql/ad_bkprst_2w1f.asp">Restoring
the model, msdb, and distribution Databases</a></tt><tt>). 
 
I'm not sure if that will work (as I haven't tested it before), but
that would be the recovery strategy I'd start with to recover or
replace the monitor box involved in log shipping. 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson 
blog: <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
 
 
DavidCur wrote:
<blockquote cite="mid7F7714F8-B22E-4380-85A5-483A25B73B47@.microsoft.com"
type="cite">
<pre wrap="">Here the details are then; the server that currently acts as monitor is going
to be "reassigned" to another role, which will require it being completely
rebuilt, arrays reconfigured, SANs changed, etc. It is therefore likely that
I will be able to keep the same server name, and could detach and reattach
the databases.
But what databases contain the replication information, and can they be
successfully re-attached to a new install of SQL? Is it the MSDB database
mainly, or are there tables in Master as well? All servers are running
SQL2000 SP4 on Windows 2003 Server SP1.
Thanks,
Dave
"John Bell" wrote:
</pre>
<blockquote type="cite">
<pre wrap="">Hi David
I have never tried this, I would expect if your new server is the same name
as the old one and you move the databases using attach/detach from the old
server then it I could be relatively trouble free... but you don't give any
details about the replacement!!!
John
</pre>
</blockquote>
</blockquote>
</body>
</html>
--050800070200090007000605--|||Thanks very much for that. I've set up a test environment today, so will
test your proposed restoration strategy next week (Friday afternoon here,
yay!). I will post the results once the testing is completed.
Thanks again,
Dave
"Mike Hodgson" wrote:
> Log shipping info, on the primary, secondary & monitor boxes, is kept in
> the msdb.dbo.log_shipping_* tables. So the msdb database should be the
> only DB relevant to log shipping on the monitor box. However, you'll
> need to make sure the SQL logins you use for inter-server communication
> are the same on the new monitor server (and that login info is stored in
> the master database).
> If it was me, I'd be thinking about setting up my new monitor box,
> restoring the master DB from a backup of your current master DB on the
> monitor box (see Restoring the master Database from a Current Backup
> <http://msdn.microsoft.com/library/en-us/adminsql/ad_bkprst_4g4w.asp>)
> and then restoring the msdb database (Restoring the model, msdb, and
> distribution Databases
> <http://msdn.microsoft.com/library/en-us/adminsql/ad_bkprst_2w1f.asp>).
> I'm not sure if that will work (as I haven't tested it before), but that
> would be the recovery strategy I'd start with to recover or replace the
> monitor box involved in log shipping.
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com|||Hi David
You may want to also check out:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314546
http://support.microsoft.com/kb/224071/EN-US/
John
"DavidCur" wrote:
> Thanks very much for that. I've set up a test environment today, so will
> test your proposed restoration strategy next week (Friday afternoon here,
> yay!). I will post the results once the testing is completed.
> Thanks again,
> Dave
> "Mike Hodgson" wrote:
> > Log shipping info, on the primary, secondary & monitor boxes, is kept in
> > the msdb.dbo.log_shipping_* tables. So the msdb database should be the
> > only DB relevant to log shipping on the monitor box. However, you'll
> > need to make sure the SQL logins you use for inter-server communication
> > are the same on the new monitor server (and that login info is stored in
> > the master database).
> >
> > If it was me, I'd be thinking about setting up my new monitor box,
> > restoring the master DB from a backup of your current master DB on the
> > monitor box (see Restoring the master Database from a Current Backup
> > <http://msdn.microsoft.com/library/en-us/adminsql/ad_bkprst_4g4w.asp>)
> > and then restoring the msdb database (Restoring the model, msdb, and
> > distribution Databases
> > <http://msdn.microsoft.com/library/en-us/adminsql/ad_bkprst_2w1f.asp>).
> >
> > I'm not sure if that will work (as I haven't tested it before), but that
> > would be the recovery strategy I'd start with to recover or replace the
> > monitor box involved in log shipping.
> >
> > --
> > *mike hodgson*
> > blog: http://sqlnerd.blogspot.com
>|||Restoring just the MSDB database seems to do the trick. On my test system I
setup log shipping, then backed up the MSDB database on the monitoring
server, and uninstalled SQL. Then reinstalled SQL and restored the MSDB
database. The "Log Shipping Monitor" option then became available under
Management in Enterprise Manager, and everything seems to be working
normally. This has been running for a week now without problems.
Thanks everyone!
dave

Wednesday, March 28, 2012

Log Shipping Role Reversal

Wednesday, March 21, 2012

Log Shipping Monitor Shows Problems when there aren't any

Friday, March 9, 2012

Log shipping fail over

Wednesday, March 7, 2012

Log Shipping and Recovery

Monday, February 20, 2012

Log Shipping : Out of Sync

Log shipping - moving the monitor server

Log shipping - moving the monitor server

Log shipping - moving the monitor server

Log shipping alerts

Blog Archive

About Me