Re: Log shipping and error 22029

From: kmkrause2 (kmkrause2_at_discussions.microsoft.com)
Date: 09/22/04 

Date: Wed, 22 Sep 2004 06:25:02 -0700

Ron,

Thanks again for your help. I had just about came to the same conclusion
about the "assume primary role option" but wanted to make sure. In fact, I
was figuring I owuld have to resign myself to the option of simply rebuilding
from the start all over again.

Thanks,
Ken

"Ron Talmage" wrote:

> Ken,
>
> First of all, do not use the option "Allow database to assume primary role"
> when setting up log shipping. It just doesn't work.
>
> Reversing the direction of log shipping is a manual process.
>
> 1. Demote the primary database on the Main server using
> msdb.dbo.sp_change_primary_role
> as you have it below. This will make one last tlog backup to disk.
> 2. Promote the secondary database on the Standby server using
> msdb.dbo.sp_change_secondary_role
> as you have it below. This will take that one last tlog backup and apply it
> WITH RECOVERY.
> 3. Remove all log shipping that remains (jobs and rows for that log shipping
> pair in the ls tables - if there are any.)
>
> At this point you can install log shipping from scratch, the new direction
> now being from the primary database on the Standby server to the secondary
> database (in norecovery) on the Main server.
>
> Hope this helps,
> Ron
> --
> Ron Talmage
> SQL Server MVP
>
> "kmkrause2" <kmkrause2@discussions.microsoft.com> wrote in message
> news:3D4C90B5-93A9-4646-ABB2-17110ACF574F@microsoft.com...
> > I am having some trouble using MS SQL Server 2000 log shipping. I can get
> the
> > database to fail over and become active on the secondary server, but I
> want
> > to also have the primary become immediately available for reverse log
> > shipping.
> >
> > After I have run the stored procedures to demote the primary, I have to
> > detach and attach the database on the secondary to be able to run the
> stored
> > procedure to promote the secondary. If I don't the procedure fails (sorry,
> I
> > don't have the exact error, but it refers to the databse being unable to
> > access). I also have to manually run the last copy and restore job to make
> > sure the final transaction log backup gets applied. Once this is done, the
> > stored procedure to promote the secondary is successful. I then run the
> > stored procedure to change the monitor server to reflect the new server
> roles.
> >
> > Once the database is failed over, I have to go into the maintenance plan
> for
> > the reverse log shipping and assign the new destination server. When doing
> > this, I receive a message stating that the database on the new destination
> > server is not in standby mode. If I set the database to Read Only in the
> > Properties dialog box, I can then configure the new destination server.
> When
> > I do this, the log backups work fine and so does the copy job. But the
> > restore job fails with the following error
> >
> > Executed as user: DMSMAIN\Administrator. sqlmaint.exe failed. [SQLSTATE
> > 42000] (Error 22029). The step failed.
> >
> > I can't find any helpful documentation of this message. Listed below are
> the
> > stored procedures I am running.
> >
> > USE master
> > GO
> > EXEC msdb.dbo.sp_change_primary_role
> > @db_name = 'modelcopy',
> > @backup_log = 1,
> > @terminate = 1,
> > @final_state = 2,
> > @access_level = 1
> >
> > 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
> >
> > USE master
> > GO
> > EXEC msdb.dbo.sp_change_monitor_role
> > @primary_server = 'sqlserver1' ,
> > @secondary_server = 'staging3',
> > @database = 'modelcopy',
> > @new_source = '\\staging3\logshipping'
> >
> > Any help is appreciated
> >
> > TIA,
> > Ken
>
>
>

Relevant Pages

  • Re: Log shipping and error 22029
    ... do not use the option "Allow database to assume primary role" ... Reversing the direction of log shipping is a manual process. ... Promote the secondary database on the Standby server using ... > After I have run the stored procedures to demote the primary, ...
    (microsoft.public.sqlserver.server)
  • Re: ReplicationLog ShippingGeogrpahical distributed cluster(W2003)
    ... Log shipping does the entire database. ... server must go offline when the logs are being applied. ... There is no automatic failover with log shipping. ... Replication is generally a good choice when you want to ...
    (microsoft.public.sqlserver.replication)
  • Application concerns OVER Login
    ... setup user on network with only user rights. ... gave that user rights to specific stored procedures on database ... wish to distribute this sql server database on a cd ... is only for the stored procedures. ...
    (microsoft.public.sqlserver.security)
  • Move Log Shipping Primary Database to New Drives
    ... We are planning to move a log shipping primary database to new drives on the ... I have tested on a dev server by detaching the database, ...
    (microsoft.public.sqlserver.server)
  • Log shipping and error 22029
    ... I am having some trouble using MS SQL Server 2000 log shipping. ... After I have run the stored procedures to demote the primary, ... detach and attach the database on the secondary to be able to run the stored ... If I don't the procedure fails (sorry, ...
    (microsoft.public.sqlserver.server)