Moving to a new SQL server

I wonder if anyone can point me in the right direction..
Am in the middle of a server refresh (nearly completed the file servers)..
now got to do the SQL boxes!!
Presently they are running Window 2000 and SQL2000. I want to move to a
bigger server running Windows 2003 and SQL2000.
What is the best way to move SQL to the new servers? Is the Move Database
Wizard any good?
(Have never touched SQL before.. so please be gentle with me :-) )
Any advice real welcome!!
Thanks
See this message thread - should have all your answers.
http://groups-beta.google.com/group/...1ca9f11d15a0a9
Never used the Move Database wizard, but depending on their situation
(downtime requirements, etc.), a full backup from the first server, and
then restore on the new server (be sure and change file paths to match
with the new server) should suffice. This simplicity assumes that your
SQL Versions & SP's are the same.
DRLJAMES wrote:
> I wonder if anyone can point me in the right direction..
> Am in the middle of a server refresh (nearly completed the file servers)..
> now got to do the SQL boxes!!
> Presently they are running Window 2000 and SQL2000. I want to move to a
> bigger server running Windows 2003 and SQL2000.
> What is the best way to move SQL to the new servers? Is the Move Database
> Wizard any good?
> (Have never touched SQL before.. so please be gentle with me :-) )
> Any advice real welcome!!
> Thanks
|||Hi,
Do not use Move database wizard. This is very time consuming. Go for either
of the below 2 approches:-
1. BACKUP and RESTORE DATABASE
2. DETACH and ATTACH.
Yesterday there was a discussion with the same topic. See the URL specified
by unc27932
Thanks
Hari
SQL Server MVP
"DRLJAMES" <DRLJAMES@.discussions.microsoft.com> wrote in message
news:8AC35D5C-05AD-45EA-A070-0993A97BC9C6@.microsoft.com...
>I wonder if anyone can point me in the right direction..
> Am in the middle of a server refresh (nearly completed the file servers)..
> now got to do the SQL boxes!!
> Presently they are running Window 2000 and SQL2000. I want to move to a
> bigger server running Windows 2003 and SQL2000.
> What is the best way to move SQL to the new servers? Is the Move Database
> Wizard any good?
> (Have never touched SQL before.. so please be gentle with me :-) )
> Any advice real welcome!!
> Thanks
>
|||Hi,
Thanks for the replies.
There are 10 databases on each server (and I have 9 servers to do). I assume
I have to back up each database in turn? Will I still need to copy over the
logins?
I thought the move database wizard looked too good to be true!!
"Hari Prasad" wrote:

> Hi,
> Do not use Move database wizard. This is very time consuming. Go for either
> of the below 2 approches:-
> 1. BACKUP and RESTORE DATABASE
> 2. DETACH and ATTACH.
> Yesterday there was a discussion with the same topic. See the URL specified
> by unc27932
> Thanks
> Hari
> SQL Server MVP
>
> "DRLJAMES" <DRLJAMES@.discussions.microsoft.com> wrote in message
> news:8AC35D5C-05AD-45EA-A070-0993A97BC9C6@.microsoft.com...
>
>
|||I would create a backup script that you can copy/paste 10 times &
change the filenames, servernames etc. - then stick it in isqlw and
click go - this way you only have to run 9 scripts (one per server).
Sample...
BACKUP DATABASE databasename
TO DISK = 'c:\filepath\databasename_dump.BAK'
WITH INIT ,
NAME = 'databasename',
DESCRIPTION = 'databasename BackupFull'
As far as the logins go - see this url - we use it regularly to get the
logins/passwords off our servers for disaster purposes.
http://databasejournal.com/features/...le.php/2228611
|||Hi,
Copy the MATER database and MSDB databases to your new server as well. This
will copy all your logins, Jobs, operators, alers ...
to new server. No need to do any manual stuff.
Have a look into the URL posted in earlier post.
Thanks
Hari
SQL Server MVP
<unc27932@.yahoo.com> wrote in message
news:1123101653.993783.224140@.g14g2000cwa.googlegr oups.com...
>I would create a backup script that you can copy/paste 10 times &
> change the filenames, servernames etc. - then stick it in isqlw and
> click go - this way you only have to run 9 scripts (one per server).
> Sample...
> BACKUP DATABASE databasename
> TO DISK = 'c:\filepath\databasename_dump.BAK'
> WITH INIT ,
> NAME = 'databasename',
> DESCRIPTION = 'databasename BackupFull'
> As far as the logins go - see this url - we use it regularly to get the
> logins/passwords off our servers for disaster purposes.
> http://databasejournal.com/features/...le.php/2228611
>
|||Hari - I only understand about 30% of this, but does just copying the
master & msdb retain the passwords and the user SID stuff? From the
article....
"When you move a database from one server to another, the entire
database and all the system tables associated with it are also moved.
One of those system tables is the sysusers table. The sysusers table
contains all the users, groups and roles that have access to the
database. In order for a person to be able to access the database, they
must have two things. The first thing they must have is a SQL Server
login. The second thing they need is to be defined as a user in the
database. Therefore, if you copy a database from one server to another
and the users of the database don't have SQL Server logins then these
database users become orphan users. An orphan user is a user in a
database with a SID that does not exist in the syslogins table in the
master database. Also if the SID stored in the database sysusers table,
differs from SID stored in the syslogin table for the matching database
user, then the database user is also considered an orphan user. If you
retain the original SID for logins, when a user database is migrated,
you will not have a problem with orphan users."
Hari Prasad wrote:[vbcol=seagreen]
> Hi,
> Copy the MATER database and MSDB databases to your new server as well. This
> will copy all your logins, Jobs, operators, alers ...
> to new server. No need to do any manual stuff.
> Have a look into the URL posted in earlier post.
> Thanks
> Hari
> SQL Server MVP
> <unc27932@.yahoo.com> wrote in message
> news:1123101653.993783.224140@.g14g2000cwa.googlegr oups.com...