Moving An SQL Server Database
Desirée Harris
March 3, 2005

A database move can be done a couple different ways, but when the database is updated frequently and the move needs to be completed with minimal downtime and no data loss, it is best to move it by detaching and then reattaching the database on the new server. The database will be offline during the move but it is better to have it offline than to miss transactions when backing up and then restoring the database on the new server.

In order to get the database moved with minimal downtime, be sure to complete as many tasks ahead of time as possible to move quickly during this process. Many times, a database move requires that the application that uses it to be offline and this time should be minimized as much as possible. These tasks include:

1. Identifying and opening all files that include connection string information to the database.
2. Open all necessary windows/applications to perform necessary tasks:
   1. Locate and make note of the path and the name of the database .mdf / .ldf files on both the current and new SQL server for the database.
      
   2. Identify all usernames and passwords for the database.
   3. Open Query Analyzer on the new SQL server to run SQL commands.
   4. Open Enterprise Manager on both the current and new SQL server to complete necessary tasks.
3. Read through instructions at least once to help the process go smoothly.

In summary, we'll backup the database, detach it, and then reattach it on the new sever. This process can be completed with Query Analyzer, but these instructions will be done via Enterprise Manager. Let's get started!

Backup Database

This is done as a precautionary step. This will allow us to have a good backup of the database up to the time of the move. (I actually do this before I make any changes to a database; you can never have too many backups!)

1. Open Enterprise Manager, and navigate to databases.
2. Right-click the database being backed up, select 'All Tasks' and then click 'Backup Database…'.
3. Verify and make note of database name and the backup destination path. I usually change the destination path to 'd:\temp\dbname.bak'. It isn't necessary to change it, but ensure you can find it if you need it.
4. Under the 'Overwrite' section, select 'Overwrite existing media'.
5. On the 'Options' tab, put a check next to 'Verify backup upon completion'.
6. Click 'OK'.

Detach the Database

1. Right-click the database, select 'All Tasks', and then click 'Detach Database…'.
2. At this point the current number of connections to the database will be displayed. If there are any connections, they must be cleared before the database can be detached. Click 'Clear'. You will then be prompted with 'This will end all active transactions in this database which is required before the database can be detached. Is it OK to proceed?' Click 'OK'. The next prompt is regarding notifying users. It will ask 'Do you want to notify the currently connected users that their sessions in the database will be ended?' I always click 'No' for web applications. And then click 'OK' to detach the database.
3. At this point the database is detached (it will not appear in Enterprise Manager).

Next, you'll need to copy the data file (.mdf) and transaction log (.ldf) file to the new server. By default, SQL server will create databases and put these files in the same directory (it's usually \Program Files\Microsoft SQL Server\MSSQL\Data). However, for performance gains, this may be changed so the transaction log file is put on another hard drive. Be sure to check the new SQL server to see how it is setup. If the transaction log files are setup on a different hard drive, it should be copied there and the data file should be put in its appropriate directory. And, when attaching the database, be sure the paths to the files are correct.

Attaching the Database

1. Right-click the database, select 'All Tasks', and then click 'Attach Database…'.
2. Navigate to the data file by clicking the button that has 3 dots on it (…).
3. Once you've selected the database .mdf file, you'll see the data file path (which should be verified for both the data and transaction log file) and have the option to choose the database name and owner. Attach it as the database name, and ensure the owner name is 'sa'.
4. Verify the path for the transaction log file is correct. If the path isn’t correct, update it to the appropriate directory.
5. Click ‘OK’.

Note: If you didn't move the transaction log file to the new server with the data file, you'll be prompted to create a new one. If you choose to create a new one, it will be done for you automatically.

Orphaned Users

If the database user doesn't exist on the new server, create it now. When creating it, you may receive an error that it already exists, but that's ok, the work that needs completed by SQL Server to add the user account is done. After you receive the error that the user already exists, press cancel and continue on to the next user.

Moving the database to a new server will result in orphaned users. You will need to run a stored procedure that will map the user in the database to a user on the server. This stored procedure will change the relationship between a Microsoft SQL Server login and a SQL Server user in the current database. Basically, it changes the SID in the database to match the one generated by the local server, which allows all custom permissions to be retained - it remembers the access and permissions.

1. Open Query Analyzer.
2. Select the database in the drop-down box on the tool bar if it isn't already selected.
3. For each database user, we'll need to execute the command: "sp_change_users_login 'update_one', 'db_username', 'db_username' " (without the double-quotes).*

*change the db_username to the actual database username in both places.

Now update any connection strings to point to the new SQL server (this could have been done while the database was moving between servers to utilize all available time) and test it well. Also, check any DTS jobs, Full-Text Indexes and Replication configurations to ensure that they are set up on the new server as they won't fully move during this process. Now you should be set. Test it well and sit back, relax and reflect on a job well done.

Sources:
http://vyaskn.tripod.com/moving_sql_server.htm
http://www.databasejournal.com/features/mssql/article.php/2224361
http://vyaskn.tripod.com/troubleshooting_orphan_users.htm

Desirée Harris is a support specialist with ORCS Web - a company that provides managed hosting solutions for clients who develop and deploy their applications on Microsoft Windows platforms. Services include shared hosting, dedicated hosting, and webfarm hosting, with specialty in .Net, SQL Server, and architecting highly scalable solutions.