Bringing a log shipping standby database online

Category: How do I?
Date: 09 Jun 2009
Product: SQL Backup
If the "source" database in a log shipping setup fails, some steps are necessary to bring the log shipping standby database online for use as the primary database.

The standby server in a log shipping scenario can either be inaccessible or read-only. In order to make the standby available for reading and writing, the log shipping needs to be broken and the database brought online WITH RECOVERY.

To do this, first see if you can bring the standby database to the current state of the source database. If it is possible to perform a final log backup on the source database, then run the log shipping backup job on the source server and copy the backup file to the log shipping share. If there are files remaining in the log shipping share, then use SQL Backup to restore the logs on the standby database by specifying a wildcard (*.sqb) filename, using the option WITH RECOVERY.

Note that if the source database is damaged, a final log backup may fail. In this case, try appending the NO_TRUNCATE option to the BACKUP LOG command.

If there are no remaining log files to restore, it is still possible to bring the database on-line using a SQL query using a query tool such as Query Analyzer or Enterprise Manager. This query will bring the database on-line:

RESTORE DATABASE [MyDatabase] WITH RECOVERY

Additionally, you need to consider that any SQL users that were transferred using the database backups either do not exist as logins on the destination server or are using invalid security identifiers (SIDs) from the source server's login.

To reconcile the SIDs, the first step is to make a list of the users in the database's users container. Next, open the security container and ensure that the users have corresponding logins on the server. Once that is accomplished, all database users need to be reconciled against the security identifier on the standby server using this query:

USE [MyDatabase]
exec sp_change_users_login 'Update_One', 'MyDBUser', 'MyDBLogin'
/ Note that MyDBUser and MyDBLogin are usually the same /

Also, note that bringing the database on-line will break the log shipping. In order to re-establish log shipping, it's necessary to restore the standby database from a full backup of the source database, if it is available.