Today I met an issue that my current SQL Server needs to be operated for some reasons and the SQL Server service will be offline for a few days. Our application is a global service for the company and it can't be offline for a long time. This means we have to move all databases from the current server to a backup server.
The basic idea is that create a backup server and restore all databases on it. Then setup a redirect function on the original server so that all requests can be redirected to the new server. This function is called "Alias" in SQL Server, which can be configured in Sql Server Configuration Manager -> SQL Native Client.
There are two summary steps to complete this solution:
1. Backup all databases from the original databases and restore them on the backup server.
2. Setup an Alias on the original server to redirect all requests to the backup server.
A. Backup and Restore
1. Open your SQL Server Management Studio from Start -> All Programs -> Microsoft SQL Server 2008 -> SQL Server Management Studio
6. Follow step 1 to login the new DB server.
7. Right click the Databases node and then click the Restore Database...
B. Setup Alias
After you move all database from the original server to the backup server, the next step is setup a redirect settings in the original server to redirect all requests to the backup server. This setting is called 'Alias' in SQL Server.
1. Select SQL Server Configuration Manager from Start -> All Programs -> Microsoft SQL Server 2008 -> Configuration Tools
2. Expand the SQL Native Client 10.0 Configuration -> Aliases. Generally there will be two menus, one is for 32 bit, the other is for 64 bit.
The Alias Name should be the 'Data Source' in your connection string
The default Port No is 1433
The Protocol should choose TCP/IP
The Server field should be the new SQL Server name.
6. Now, you backup server is ready to go. Disable you current SQL Server service and try your applications, it should work.