Failover
Suppose that our primary database became unavailable because of a disk failure at 3:31 p.m. and we needed to fail over to the standby server. The last transaction log backup of the primary database was taken at 3:30 p.m. Let's presume that no further transaction log backups can be taken on the primary server—that means that transactions that occurred from 3:30 p.m. to 3:31 p.m. are lost forever.
The last backup that was applied to the standby server was the backup of 3:15 p.m. We can copy the backup from 3:30 p.m. to the standby server and execute the following command from the Query Analyzer:
RESTORE LOG northwind1 FROM DISK = 'c:\Program Files\Microsoft SQL Server\MSSQL$Subscription_srv\Log\Northwind_tlog_200407111530.TRN' WITH RECOVERY
Notice that transaction log backups have the naming convention 'database_name_tlog_Year_Month_Day_Hour_Minute'. Also note that the time when backup was taken is tracked by using a 24-hour clock, so you can differentiate backups taken during a.m. or p.m. hours.
The most important piece of the statement above is the WITH RECOVERY clause. This clause makes the database operational; after the database is recovered, you can no longer apply additional transaction log backups to it—the database is accessible by all users.
Now, suppose that we could take one final backup of the transaction log on the primary server at 3:31 p.m. If that were the case, no transactions would be lost; we would restore the backup from 3:30 p.m. by using the NO RECOVERY clause and restore the backup from 3:31 p.m. by using the WITH RECOVERY clause.
All logins that need to have access to the database must exist on the standby server. If you have a handful of logins in your database, I recommend simply adding them to the standby server manually. If you have dozens of logins, you might wish to use the transfer logins task within Data Transformation Services (DTS).
That's all there is to fail over to the standby server as far as SQL Server is concerned. Next, you must configure your data sources to point to the backup server instead of primary server to make sure that your application(s) works.
NOTE
Important Reminder: If you have the same login on two servers, they're not guaranteed to have the same security identifier (SID). The SID is stored in the sysxlogins system table in master database and is assigned automatically by SQL Server. Within each database SQL Server, logins are mapped to the database users—such mappings are recorded in the sysusers system table that exists in every database.
When you fail log-shipped databases over to the standby server, you need to ensure that appropriate mappings exist for every login that needs access to the database you failed over. You can accomplish this manually by updating the SID of the sysusers table and making it the same as SID in sysxlogins. Better way to perform the same task is to use the system stored procedure sp_change_users_login. I don't have room to discuss the details of this procedure in this introductory article; however, you can find details in SQL Server online documentation.