Help with manual Log shipping in SQL Server 2000

I am trying to implement a manual log shipping method in SQL server
2000 (standard edition) but I cannot seem to get the transaction logs
applied. There is a strong chance that I might be doing something
basic wrong. The solution involves two jobs setup in Enterprise
Manager and two stored procedures.
If I do the following:
(a) Database backup as per job (1)
(b) Change the database (via Query Analyser)
(c) Restore logs as per job (2)
I was hoping that the change in the source database would get
refelected in the DR database but to no avail? There are no errors,
just no change in the DR database.
I have two jobs setup in Enterprise Manager:
(1) Database Backup
Checkpoint
backup log Suppliers with truncate_only
backup database Suppliers to disk = '\\drsql\g$\DRSuppliers.BAK' with
init
exec DRSQL.master.dbo.restore_icps_database_backups
(2) Restore logs
backup log suppliers to disk='\\drsql\g$\DRSuppliers_log2.LDF' with
init, no_truncate -- I am not convinced that this step is right'
Note the difference in log file name. If it is the same, then I get
errors.
exec DRSQL.master.dbo.restore_icps_database_logs
The two procs are as follows:
CREATE PROCEDURE dbo.restore_icps_database_backups AS
restore database Suppliers
from disk = 'G:\DRSuppliers.bak'
with
replace
,dbo_only
,standby = 'G:\DRSuppliers_log.ldf'
,move 'Suppliers_data' to 'E:\SQLDATA\MSSQL\Data\Suppliers_Data.mdf'
,move 'Suppliers_Log' to 'F:\SQLDATA\MSSQL\data\Suppiers_Log.ldf'
GO
CREATE PROCEDURE dbo.restore_icps_database_logs AS
restore log Suppliers
from disk = 'G:\DRSuppliers.bak'
with
standby = 'G:\DRSuppliers_log.ldf'
GOHi
http://www.sql-server-performance.com/sql_server_log_shipping.asp
"PromisedOyster" <PromisedOyster@.hotmail.com> wrote in message
news:1174892030.962292.33520@.p77g2000hsh.googlegroups.com...
>I am trying to implement a manual log shipping method in SQL server
> 2000 (standard edition) but I cannot seem to get the transaction logs
> applied. There is a strong chance that I might be doing something
> basic wrong. The solution involves two jobs setup in Enterprise
> Manager and two stored procedures.
> If I do the following:
> (a) Database backup as per job (1)
> (b) Change the database (via Query Analyser)
> (c) Restore logs as per job (2)
> I was hoping that the change in the source database would get
> refelected in the DR database but to no avail? There are no errors,
> just no change in the DR database.
>
> I have two jobs setup in Enterprise Manager:
> (1) Database Backup
> Checkpoint
> backup log Suppliers with truncate_only
> backup database Suppliers to disk = '\\drsql\g$\DRSuppliers.BAK' with
> init
> exec DRSQL.master.dbo.restore_icps_database_backups
> (2) Restore logs
> backup log suppliers to disk='\\drsql\g$\DRSuppliers_log2.LDF' with
> init, no_truncate -- I am not convinced that this step is right'
> Note the difference in log file name. If it is the same, then I get
> errors.
> exec DRSQL.master.dbo.restore_icps_database_logs
> The two procs are as follows:
> CREATE PROCEDURE dbo.restore_icps_database_backups AS
> restore database Suppliers
> from disk = 'G:\DRSuppliers.bak'
> with
> replace
> ,dbo_only
> ,standby = 'G:\DRSuppliers_log.ldf'
> ,move 'Suppliers_data' to 'E:\SQLDATA\MSSQL\Data\Suppliers_Data.mdf'
> ,move 'Suppliers_Log' to 'F:\SQLDATA\MSSQL\data\Suppiers_Log.ldf'
> GO
>
> CREATE PROCEDURE dbo.restore_icps_database_logs AS
> restore log Suppliers
> from disk = 'G:\DRSuppliers.bak'
> with
> standby = 'G:\DRSuppliers_log.ldf'
> GO
>|||On Mar 26, 5:50 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hihttp://www.sql-server-performance.com/sql_server_log_shipping.asp
> "PromisedOyster" <PromisedOys...@.hotmail.com> wrote in message
> news:1174892030.962292.33520@.p77g2000hsh.googlegroups.com...
>
> >I am trying to implement a manual log shipping method in SQL server
> > 2000 (standard edition) but I cannot seem to get the transaction logs
> > applied. There is a strong chance that I might be doing something
> > basic wrong. The solution involves two jobs setup in Enterprise
> > Manager and two stored procedures.
> > If I do the following:
> > (a) Database backup as per job (1)
> > (b) Change the database (via Query Analyser)
> > (c) Restore logs as per job (2)
> > I was hoping that the change in the source database would get
> > refelected in the DR database but to no avail? There are no errors,
> > just no change in the DR database.
> > I have two jobs setup in Enterprise Manager:
> > (1) Database Backup
> > Checkpoint
> > backup log Suppliers with truncate_only
> > backup database Suppliers to disk = '\\drsql\g$\DRSuppliers.BAK' with
> > init
> > exec DRSQL.master.dbo.restore_icps_database_backups
> > (2) Restore logs
> > backup log suppliers to disk='\\drsql\g$\DRSuppliers_log2.LDF' with
> > init, no_truncate -- I am not convinced that this step is right'
> > Note the difference in log file name. If it is the same, then I get
> > errors.
> > exec DRSQL.master.dbo.restore_icps_database_logs
> > The two procs are as follows:
> > CREATE PROCEDURE dbo.restore_icps_database_backups AS
> > restore database Suppliers
> > from disk = 'G:\DRSuppliers.bak'
> > with
> > replace
> > ,dbo_only
> > ,standby = 'G:\DRSuppliers_log.ldf'
> > ,move 'Suppliers_data' to 'E:\SQLDATA\MSSQL\Data\Suppliers_Data.mdf'
> > ,move 'Suppliers_Log' to 'F:\SQLDATA\MSSQL\data\Suppiers_Log.ldf'
> > GO
> > CREATE PROCEDURE dbo.restore_icps_database_logs AS
> > restore log Suppliers
> > from disk = 'G:\DRSuppliers.bak'
> > with
> > standby = 'G:\DRSuppliers_log.ldf'
> > GO- Hide quoted text -
> - Show quoted text -
Hi
Thats the article I am using, but I think I am having problems with
the backup log stage. I think I may have sussed it out though.
I was not setting log backup device name properly in the proc/job|||Hi
Have you checked out
http://www.sql-server-performance.com/sql_server_log_shipping.asp which shows
the steps you need to do for this?
"PromisedOyster" wrote:
> I am trying to implement a manual log shipping method in SQL server
> 2000 (standard edition) but I cannot seem to get the transaction logs
> applied. There is a strong chance that I might be doing something
> basic wrong. The solution involves two jobs setup in Enterprise
> Manager and two stored procedures.
> If I do the following:
> (a) Database backup as per job (1)
> (b) Change the database (via Query Analyser)
> (c) Restore logs as per job (2)
If you have not backed up the log after b abd restored it, then it will not
be reflected on your restored database.
> I was hoping that the change in the source database would get
> refelected in the DR database but to no avail? There are no errors,
> just no change in the DR database.
>
John