Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dbaman
Starting Member
46 Posts |
 Posted - 2005-09-21 : 09:23:22
|
| I have SQLServer7 up and running now in Windows2000 Advance Server(Lets Call it OLD Server for discussion). I have approx. 50 production databases running here now.I am building a new bigger and better server(Lets Call it NEW Server for Discussion). What is the best/easiest way to transfer all my database from Old Server to new server. I have around 50 Production Database running on Old SQLServer now.ThanksR |
|
|
Thrasymachus
Constraint Violating Yak Guru
483 Posts |
Posted - 2005-09-21 : 09:35:30
|
| since there is an upgrade involved I do not think a backup and restore is going to cut it.i think you are largely left with using the <sigh> copy database wizard or the sp_dettach_db or sp_attach_db commands. you may have trouble with full text indices if you have any. I would read the following section of Books Online.Contents --> Installing SQL Server --> Upgrading to SQL Server 2000: Overview====================================================Regards,Sean Roussyaka the merciless black hearted capitalist troublemaker"pimpin ain't easy, but someone has to do it" -- Pimpin Whitefolks(?) |
 |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2005-09-21 : 09:53:20
|
| "you may have trouble with full text indices if you have any....."....you may also have trouble with any new reserved words in code/columns/table names. |
|
|
|
dbaman
Starting Member
46 Posts |
Posted - 2005-09-21 : 10:11:28
|
| Thanks for all the replies so far. Just to clear one more thing this is transfer from one Server to another and both the Server are running SQLServer7. There is no SQLServer 2000 upgrade involved.I was wondering as the New Server is really a new Install can't I do some thing like this:-- stop SQLServer and SQLAgent Service on Current Server as well as on New Server-- Copy all the datafile/logfiles from current server to new server-- Is there any kind of script I can run which corrects any master database information where ServerName itself is stored?As I said this is just a thought came to mind..I am not sure if this is workable?Please share your thoughtsR |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-09-21 : 15:34:27
|
| Why not restore the Master database as well?Are all the paths to databases going to be identical on the new server? That would help ... <g>Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-09-21 : 15:45:33
|
| If the paths are the same, then this is as simple as this:Stop MSSQLSERVER service on old and new server. Copy all MDFs and LDFs to new server in same location. Start MSSQLSERVER service on new server. Since all databases were copied, including system ones, you should be good to go. You'll have all your logins, jobs, DTS packages, etc... But the key is to make the paths the same at install time.DISCLAIMER: I've done this multiple times on 2000 but never on 7.0. Test in a test environment first.Tara |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-09-21 : 17:45:51
|
| Things would also be easier if you can rename the new server to the same name as the old server.This would get around any problems with application that point to the old server having to change. If you have a lot of DTS packages with connections to the old server, that would be a big timesaver.CODO ERGO SUM |
|
|
|
|
|
|
Moving SQLServer from One Server to Another