INF: Frequently Asked Questions - SQL Server 7.0 - Log Shipping

View products that this article applies to.

This article was previously published under Q275146

SUMMARY

This article addresses some common questions about the Log Shipping tool for Microsoft SQL Server 7.0.

Log shipping automates the process of backing up, copying, and restoring transaction log backups from a primary server to a standby server. You can use log shipping to maintain a warm standby server that can be brought online quickly in the event of a failure of the primary server.

In SQL Server 7.0, log shipping is an unsupported tool. However, Microsoft Product Support Services provides "best effort" assistance to resolve any issues with the tool that do not require source code modifications.

Back to the top

MORE INFORMATION

Q. Where can I find the log shipping installation files or program?

A. In SQL Server 7.0 log shipping is in the Back Office Resource Kit. The files supplied as part of the Log Shipping utility include scripts to create the required tables and stored procedures to set up log shipping. The tool also includes a help file, which covers setup and troubleshooting techniques.

Q. Will my SQL Server logins be transferred as part of log shipping?

A. No. SQL Server logins are not transferred during log shipping. SQL Server logins are stored in the master database of the server. To make sure that you have all the logins from the production server on the warm standby server you can:

Backup and restore the master database from the production server to the warm standby server before you set up log shipping.

-or-
Create the logins manually on the destination server. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
246133 (http://support.microsoft.com/kb/246133/EN-US/ ) HOW TO: Transfer Logins and Passwords Between Instances of SQL Server

Q. What needs to be done in an environment where log shipping is set up to bring a warm standby server online?

A. To bring a warm standby server online, use these steps:

Restore the last transaction log backup from the production server database to the warm standby server by using the WITH RECOVERY option to recover the database.
Rename the standby server to the name of the primary server or redirect your application to the standby server.
The SQL Server administrator must ensure that all SQL Server logins exist on the standby server. To do this, Microsoft recommends that you restore the master database on the standby server from the primary server before you set up log shipping.NOTE: Restoring the primary server's master database to the standby server removes references to any user databases on the standby server. Existing databases on the standby server can then be reattached. You must re-create any logins that are specific to the standby server.

Q. Can you use log shipping in an environment where there are non-logged operations like SELECT INTO or BulkCopy?

A. No. The log shipping sequence is broken when you perform any non-logged operations on the source server. Transaction log backups are not allowed after you perform non-logged operations.

Perform either a full or a differential backup of the database after any non-logged operation. Restore that backup on the destination server before you resume log shipping.

Q. What process does Microsoft recommend for setting up log shipping in Microsoft SQL Server 7.0?

A. Use these steps:

If you have any logins that need to be transferred, transfer the logins to the warm standby server by restoring the master database. For more information, see the "How to restore the master database" topic in SQL Server Books Online.
Make a backup of the production database that is being set up for log shipping, and then restore the backup on the standby server and use either the NORECOVERY or STANDBY option.
Run the log shipping stored procedures included with the tool to create the log shipping control tables and jobs on the secondary server.

Q. Where can I check for log shipping error messages?

A. The SQL Server error logs on the servers have information about backup and restore operations. In addition, the backup_movement_plan_history table is a history table that is populated by the copy and load jobs on the standby server. The backup_movement_plan_history table provides useful information about the cause of log shipping failures.

Q. When I try to add a backup movement plan by using this code

exec msdb..sp_create_backup_movement_plan

I get this error message:

"SQLServerAgent is not currently running so cannot be notified of this action".

Why do I get this error message and what are the implications of this error message?

A. Running the preceding stored procedure populates the table msdb..backup_movement_plan, and then creates the SQL Server Agent copy and load jobs. If the SQL Server Agent is not running on the destination server, the error message occurs. You can safely ignore the error message after you start the SQL Server Agent on the destination server.

Q. The copy job on my destination server fails. What can I check?

A. You can check:

To see if the SQL Server Agent is running on the destination server.
The SQL Server Agent startup account on the standby server. Make sure that it is a domain account and that is has read permissions on the backup share where the backups reside. If the SQL Server Agent is using the system account, the SQL Agent is not able to access the network resources.
The job history on the destination server to see which specific job is failing.

Q. Can I have the destination database online while transaction logs are being loaded on the database?

A. No. You must restore the full source database backup on the destination database by using either the NORECOVERY or the STANDBY option for log shipping to work. If not, the following error message appears in the backup_movement_plan_history table:

"Error on loading file c:\mssql7\backup\Log_Source_tlog_200010090015.TRN : [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 4306: [Microsoft][ODBC SQL Server Driver][SQL Server]The preceding restore operation did not specify WITH NORECOVERY or WITH STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH STANDBY for all but the final step. [Microsoft][ODBC SQL Server Driver][SQL Server]Backup or restore operation terminating abnormally."

Q. What can I check if I see this error message in the backup_movement_plan_history table?

Source directory \\Servername\logshipping share\ is not accessible

A. This error message indicates either a connectivity or permissions problem. Try mapping the share from the standby server in Microsoft Windows Explorer. If you are not successful in mapping the share then:

Check to make sure that the share has the right permissions and the correct share name.
Check to see if the primary server is online.
Check the network connectivity between the two servers.

Q. When I see the following error message in the backup_movement_plan_history table, what are the available options?

This backup set cannot be restored because the database has not been rolled forward far enough

A. Typically, this error message occurs if the transaction log backup sequence is broken. A transaction log backup may have been made that was not part of the log shipping process. The best way to recover from a situation like this is to disable the load job on the secondary server, and then check for the last successfully loaded transaction log on the database. If the log backups are all available, apply each transaction log backup since the last successful restore. If one or more transaction log backups are unavailable, you must resynchronize log shipping.

If you have several files to load to get the databases resynchronized, you can always create a complete backup of the production database, and then perform a restore that uses the NORECOVERY or STANDBY option on the secondary server.

Q. Can database files be added or deleted from the database that is the source in log shipping?

A. No. The addition or deletion of any files by altering the database results in the need to resynchronize log shipping. Transaction log restores on the destination server fail with this error message:

Server: Msg 3155, Level 16, State 1, Line 1 The RESTORE operation cannot proceed because one or more files have been added or dropped from the database since the backup set was created. Server: Msg 3013, Level 16, State 1, Line 1 Backup or restore operation terminating abnormally.

Back to the top