Moving a database from one server to another one

Posted on 2005-05-15
Medium Priority
Last Modified: 2010-03-19

I'm planning to move a database called ABC from server A to server B.
I'm using SQL Server 2000.
To achieve that I was thinking of performing the following steps:
1) take a full backup of database ABC on server A;
2) copy over the network the backup of ABC on to server B;
3) rebuild from the backup the database ABC on server B.
My question is. Whatelse do I have to do?
How can I also move loggins and all other objects associated with this database?
The database is used to store data for an application used by some users. Exactly, which others steps do I have to perform to make sure that the users will not have any problem when we database will be move to server B?
Which are the most common problems I may come accross and how solve them?

Thank you
Question by:deepakee
LVL 43

Accepted Solution

Eugene Z earned 1500 total points
ID: 14007496
if yo

review the article and links from the article
HOW TO: Move Databases Between Computers That Are Running SQL Server

HOW TO: Transfer Logins and Passwords Between Instances of SQL Server

Expert Comment

ID: 14007506
Extra job to do :

1. Backup any DTS file you have made and stored on server A
2. Backup any scheduled jobs running on server A
3. Recreate security logins, roles, linked server information on server B

Expert Comment

ID: 14008640
When you restore your database - sometimes your logins are not recognised depending on what service pack was installed on SQL server 2000.

If you have a linked server you will have to re configure.

You need to setup you applications to point to the server b rather that server ABC.

I dont see any other problem with restore.

If your users cannot login  run the following query

sp_change_users_login 'auto_fix', 'username'


Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question