|
Moving_A_SQL_Server_Database
| Moving A SQL Server Database
Moving A 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: empdbname.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 FilesMicrosoft SQL ServerMSSQLData). 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 services for clients who develop
and deploy their applications on Microsoft Windows platforms.
About the author:
Desirée Harris is a support specialist with ORCS Web - a company
that provides managed hosting services for clients who develop
and deploy their applications on Microsoft Windows platforms.
|
|
| |
| |
