How to configure log shipping (Enterprise Manager)
To configure log shipping with the Database Maintenance Plan Wizard
Note Before you configure log shipping, you must create a share on the primary database to make the transaction logs available. This is a share off of the directory that the transaction logs are dumped to. For example, if you dump the logs to the directory e:\data\tlogs\, you could create the \\logshipping\tlogs share off the directory.
- In the Select Databases screen, select the These databases check box, and then select the database to log ship.
If you select more than one database, log shipping will not work, and the log shipping option will not be available. You are not allowed to select a database that is already configured for log shipping.
- Select the Ship the transaction logs to other SQL Servers (Log Shipping) check box.
- Continue through the wizard, specifying the rest of the database maintenance options, until you get to the Specify the Log Shipping Destinations screen.
- Click Add to add a destination database.
For this option to be available, you must have selected to use log shipping earlier in the wizard.
- In the Add Destination Database screen, select a server name.
The server must be registered and running Microsoft® SQL Server™ 2000 Enterprise Edition to appear in the drop-down list. If you want this destination to become an available source destination, you must select the Allow database to assume primary role check box. If this box is not selected, this destination database will not be able to assume the source destination role in the future. If you have selected the Allow database to assume primary role check box, you must also specify the Transaction Log Backup Directory on the destination database to which the logs will be backed up.
- To change the transaction log destination database from the default location, enter a location in the Directory box.
- If the source database does not exist on the destination database, select the Create New Database check box.
The Database Name box will default to the source database name. If you want a different database name on the destination server, specify a new name. If you have chosen to allow this destination database to assume the source role, you cannot change the database name from the default.
- If you have selected the Create New Database check box, you must specify the file directories for the data and log on the destination database in the For Data and For Log boxes.
- If the source database already exists on the destination database, select the Use Existing Database check box. If the database name on the destination server is different, enter it in the Database Name box. This database must have been restored using the WITH STANDBY option to properly accept logs.
- In the Initialize the Destination Databases screen, either:
- Click Take full database backup now.
–or-
- Click Use most recent backup file to initialize the destination database.
- In the Log Shipping Schedules screen, view the default log shipping schedule. If you would like to alter the schedule, click Change.
- In the Copy/Load Frequency box, set the frequency, in minutes, with which you want the destination servers to backup and restore the transaction logs from the source server.
- In the Load Delay box, set the delay, in minutes, you want the destination database to wait before it restores the transaction log from the source server.
The default for this box is 0 minutes, which indicates that the destination database should immediately restore any transaction log backups.
- In the File Retention Period box, specify the length of time that must elapse before a transaction log can be deleted.
- In the Log Shipping Thresholds screen, set the Backup Alert Threshold.
This is the maximum elapsed time since the last transaction log backup was made on the source server. After the time exceeds this specified threshold, an alert will be generated by the monitor server.
- In the Out of Sync Alert box, specify how long a time has passed between the last transaction log backup on the source server and the last transaction log restore on the destination server.
After the time exceeds this specified threshold, an alert will be generated by the monitor server.
- In the Specify the Log Shipping Monitor Information screen, type the name of the server that will monitor log shipping.
- Click either Use Windows Authentication or Use SQL Server Authentication to connect to the monitor server. The log_shipping_monitor_probe login name is fixed and must be used to connect to the monitor server. If this is a new account, choose a new password. If the account already exists on the monitor server, you must specify the existing password.
Note Using the Database Maintenance Wizard to set up log shipping, you can log ship only to disks; the backup to tape option is not available.