Moving A SQL Server Database Desirée Harris March, 3 2005A database move can be done a couple different ways, but when
database is updated frequently and move needs to be completed with minimal downtime and no data loss, it is best to move it by detaching and then reattaching database on new server. The database will be offline during move but it is better to have it offline than to miss transactions when backing up and then restoring database on new server.
In order to get 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 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 database. 2. Open all necessary windows/applications to perform necessary tasks: 1. Locate and make note of path and name of database .mdf / .ldf files on both current and new SQL server for database. 2. Identify all usernames and passwords for database. 3. Open Query Analyzer on new SQL server to run SQL commands. 4. Open Enterprise Manager on both current and new SQL server to complete necessary tasks. 3. Read through instructions at least once to help process go smoothly.
In summary, we'll backup database, detach it, and then reattach it on 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 database up to time of 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 database being backed up, select 'All Tasks' and then click 'Backup Database…'. 3. Verify and make note of database name and backup destination path. I usually change destination path to 'd: empdbname.bak'. It isn't necessary to change it, but ensure you can find it if you need it. 4. Under 'Overwrite' section, select 'Overwrite existing media'. 5. On 'Options' tab, put a check next to 'Verify backup upon completion'. 6. Click 'OK'.
Detach Database
1. Right-click database, select 'All Tasks', and then click 'Detach Database…'. 2. At this point current number of connections to database will be displayed. If there are any connections, they must be cleared before 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 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 currently connected users that their sessions in database will be ended?' I always click 'No' for web applications. And then click 'OK' to detach database. 3. At this point database is detached (it will not appear in Enterprise Manager).
Next, you'll need to copy data file (.mdf) and transaction log (.ldf) file to new server. By default, SQL server will create databases and put these files in same directory (it's usually Program FilesMicrosoft SQL ServerMSSQLData). However, for performance gains, this may be changed so transaction log file is put on another hard drive. Be sure to check new SQL server to see how it is setup. If transaction log files are setup on a different hard drive, it should be copied there and data file should be put in its appropriate directory. And, when attaching database, be sure paths to files are correct.
