Moving an SQL Database
 

I've recently begun working on a web directory application, which uses a SQL database backend. What I need to do is move the database from one computer (Which is acting as a Server using MS Server 2003), to a dedicated server in another room (Also using MS Server 2003).

The database is maintained using MS SQL Server 2000, and the dedicated, destination server will also be using MSSQLS2000.

The application is developed using ASP.NET with a C# backend. I'm not too concerned with the relocation of the source code, as I can easily remote in and get the files, but since I have little experience working with databases, I'm rather concerned with relocating it, and having it remain functional.

At this point, I guess my question for you guys is if you have any suggestion for an easy and safe way for relocating the database.

With that said, I haven't come into this without doing a little research of my own. My current theory (As I haven't had the chance to test it out yet) is that perhaps I can make a Backup of the current database, which is rather simple, and on the new destition server, set up a new server using that database. What I do not know about SQL Server 2000 is if it has the ability to create a new database from a backup of an old one.

If this doesn't work out, perhaps someone here knows if there is a specific import/export function in SQL 2000 which is made for just this situation.

Thanks a lot for your time guys, and I appreciate any responses!

If you have access to the Enterprise manager for the system you should be able to connect to the server view the list of databases. Right click the database and do a Detach. Of course the Database can't be used at that time. Then all you will need to do is copy the files from 1 Machine to the Other and do the same steps as above but do an Attach.

I know that's a bit simple you could also just stop the SQL service and copy out the Database. Then attach it to the new server.

There's really quite a few ways to do it.

http://www.microsoft.com/technet/pro...ackuprest.mspx

^^^ Insanely more info than you needed

http://database.ittoolbox.com/docume...ationcopy-2548

^^^ Much Simpler

And you DO realize that those were the top results for "SQL Server 2000 Database Backup" and "SQL Server 2000 Database Migrate", respectively? It pays to actually look before asking sometimes.