Simple Log Shipping in SQL Server 2000 Standard Edition

Enterprise Manager's log shipping utility is available only for the SQL Server 2000 Enterprise Edition and SQL Server 2000 Developer Edition. So can you log-ship databases that run on SQL Server 2000 Standard Edition? Microsoft's answer for the Standard Edition is the Simple Log Shipper tool, which you can find in the Microsoft SQL Server 2000 Resource Kit.

Related: Log Shipping in SQL Server 2000, Part 1

Simple Log Shipper uses a linked-server relationship between the primary server and the secondary server. The log shipping activity is controlled from a stored procedure called sp_ShipLog, which you run in a SQL Server Agent job on the primary server. This stored procedure backs up the primary server database's transaction log to a Universal Naming Convention (UNC) file location and makes a remote procedure call (RPC) to sp_ApplyStandbyLog on the secondary server to restore the log file to the secondary database. These stored procedures typically are located in each server's master database, but that location (which the Simple Log Shipper documentation recommends) isn't a requirement.

Simple Log Shipper doesn't provide cleanup for transaction-log backup files, so you have to archive them and delete the old ones manually. You can monitor Simple Log Shipper by reading the primary server's SQL Server Agent job history, SQL Server error log, and Windows event log.