Failing over to a standby server

If you need to switch from the source database to a standby database, you must bring the standby database online manually; there is no automatic failover with the log shipping configuration.

The standby database will be non-operational or read-only, depending on the recovery completion state you selected (WITH NORECOVERY or WITH STANDBY). To make the standby database available for reading and writing, the log shipping needs to be broken and the database brought online WITH RECOVERY:

1. If possible, run the transaction log backup job on the primary server to perform a final backup of the the source database transaction log. You can do this from the Jobs tab in the SQL Backup Pro graphical user interface (GUI).
   If the source database is damaged, the log backup may fail. In this case, edit the SQL Server Agent job (for example, using SQL Server Management Studio) and append WITH NO_TRUNCATE to the BACKUP LOG command, then run the job again.

2. If there are transaction log backup files remaining in the log shipping shared folder, restore them to the standby database, specifying the option WITH RECOVERY when you restore the final backup. For example:

   "RESTORE LOG [standby database] FROM DISK = 'C:\Backups\log_29022013_160012' WITH NORECOVERY"
   
   "RESTORE LOG [standby database] FROM DISK = 'C:\Backups\log_29022013_160512' WITH NORECOVERY"
   
   "RESTORE LOG [standby database] FROM DISK = 'C:\Backups\log_29022013_161012' WITH RECOVERY"

   You can also use the SQL Backup Pro Restore wizard: on step 1, select Browse for backup files to restore and use the File Browser to select the backup files. For more information, see Restoring backups.

   If there are no transaction log backup files remaining, recover the standby database using the native SQL Server RESTORE statement, for example:

   RESTORE DATABASE [database_name] WITH RECOVERY

3. Reconfigure connecting resources to use the standby database.

If any SQL users were transferred via the transaction log backups, they will either not exist as logins on the standby SQL Server or they will use invalid security identifiers (SIDs) from the source SQL Server's login. To reconcile the SIDs:

1. Make a list of the users in the database's Users container (in SQL Server Management Studio, open Object Explorer and select Server > Databases > <standby database> > Security > Users).
2. Open the SQL Server's Security container and ensure that the users have corresponding logins on the SQL Server.

3. Reconcile all database users against the security identifier on the standby server using this query:

   USE [MyDatabase]
   exec sp_change_users_login 'Update_One', 'MyDBUser', 'MyDBLogin'

   where Update_One links the specified user (MyDBUser) in the database (MyDatabase) to the existing SQL Server login (MyDBLogin). The user and the login are usually the same. For more information about sp_change_users_login, refer to you SQL Server documentation

To re-establish log shipping, restore the standby database from a new full backup of the source database using WITH NORECOVERY or WITH STANDBY. You can use the Restore wizard to do this.