Easiest way to move SQL databases to a new server?

What's the easiest way to move SQL databases to a new server? I have a
computer with SQL Server 2000 on it with several databases, and I just built
a new Win 2003 Server with SQL Server 2000 on it, and want to move all the
databases to the new server and decommission the old one.
Thanks in advance.
-MikeMike,
Perhaps the easiest and safest is to back up each database to a file, copy
it to the other server, and restore. The benefit is that you then have a
backup of the databases.
Just as easily, you can also detach the databases, copy the files to the new
server, and attach them there.
Ron
--
Ron Talmage
SQL Server MVP
"Mike Schinkel" <ms@.mikeschinkel.com> wrote in message
news:%23QlbsbQzDHA.1740@.TK2MSFTNGP09.phx.gbl...
> What's the easiest way to move SQL databases to a new server? I have a
> computer with SQL Server 2000 on it with several databases, and I just
built
> a new Win 2003 Server with SQL Server 2000 on it, and want to move all the
> databases to the new server and decommission the old one.
> Thanks in advance.
> -Mike
>|||Hi,
If you can maintain the same directory structure in the new server, Stop the
MSSQL server service in both servers and copy all the .MDF and .LDF files
to the identical folder in new server and then start the MSSQL service.
Once the service come up , you can use the below system stored procedure to
change the server name.
sp_dropserver 'oldservername'
go
sp_addserver 'newservername','local'
After this restart the SQL server service.
Thanks
Hari
MCDBA
"Mike Schinkel" <ms@.mikeschinkel.com> wrote in message
news:#QlbsbQzDHA.1740@.TK2MSFTNGP09.phx.gbl...
> What's the easiest way to move SQL databases to a new server? I have a
> computer with SQL Server 2000 on it with several databases, and I just
built
> a new Win 2003 Server with SQL Server 2000 on it, and want to move all the
> databases to the new server and decommission the old one.
> Thanks in advance.
> -Mike
>|||[posted and mailed, please reply in news]
Mike Schinkel (ms@.mikeschinkel.com) writes:
> What's the easiest way to move SQL databases to a new server? I have a
> computer with SQL Server 2000 on it with several databases, and I just
> built a new Win 2003 Server with SQL Server 2000 on it, and want to move
> all the databases to the new server and decommission the old one.
See http://support.microsoft.com/default.aspx?scid=kb;EN-US;224071.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||There is an HOWTO Article on this as well
http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
HOW TO: Move Databases Between Computers That Are Running SQL Server
Yih-Yoon Lee
"Mike Schinkel" <ms@.mikeschinkel.com> wrote in message news:<#QlbsbQzDHA.1740@.TK2MSFTNGP09.phx.gbl>...
> What's the easiest way to move SQL databases to a new server? I have a
> computer with SQL Server 2000 on it with several databases, and I just built
> a new Win 2003 Server with SQL Server 2000 on it, and want to move all the
> databases to the new server and decommission the old one.
> Thanks in advance.
> -Mike|||The simplest way is to use the wizard.
1. Ensure that your enterprise manager can see both
servers and has access to both servers as owner (SA
access)
2. On the source server, select the "Management" folder
3. Go to the Tools menu, choose Wizards
4. Select Management from the Wizard menu
5. Select "Copy Database Wizard"
The Wizard allows you to select the source and
destination servers, and choose the databases to be moved
or copied. It creates all the logins on the destination
server, and moves supported shared objects, shared
procedures,jobs and error messages. It also allows you to
schedule the migration.
It is simple, fast and works a treat. Even a non-SQL
person can use this and correctly move/copy the databases
easily
>--Original Message--
>What's the easiest way to move SQL databases to a new
server? I have a
>computer with SQL Server 2000 on it with several
databases, and I just built
>a new Win 2003 Server with SQL Server 2000 on it, and
want to move all the
>databases to the new server and decommission the old one.
>Thanks in advance.
>-Mike
>
>.
>sql