New Server, How to transfer data from old server?

I'm using SQLServer 2000 on Windows 2000 Server.
We have just purchased a new server. The new server will
have the EXACT same file structure, computer name, and
static IP address as the old server. (We DO NOT use
replication).
I want transfer my databases, DTSs, and Logins to the new
server from the old.
How do I so this?
Do I only need to transfer the User databases or must I
also transfer the Master, Model, Tempdb, and msdb system
databases?
Thanksmaster contains the logins which will be needed on the new server...
You could backup/restore master, and msdb...
You could could the copy database wizard to copy the rest...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Brent Murphy" <brent.murphy@.us.schneider-electric.com> wrote in message
news:031d01c3af99$64620b70$a101280a@.phx.gbl...
> I'm using SQLServer 2000 on Windows 2000 Server.
> We have just purchased a new server. The new server will
> have the EXACT same file structure, computer name, and
> static IP address as the old server. (We DO NOT use
> replication).
> I want transfer my databases, DTSs, and Logins to the new
> server from the old.
> How do I so this?
> Do I only need to transfer the User databases or must I
> also transfer the Master, Model, Tempdb, and msdb system
> databases?
> Thanks
>|||Thanks Wayne.
So my Backed up Master DB will just overwrite whatever
Master DB is on the new server from the SQL Server 2000
install?
>--Original Message--
>master contains the logins which will be needed on the
new server...
>You could backup/restore master, and msdb...
>You could could the copy database wizard to copy the
rest...
>
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Computer Education Services Corporation (CESC),
Charlotte, NC
>www.computeredservices.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
>community of SQL Server professionals.
>www.sqlpass.org
>"Brent Murphy" <brent.murphy@.us.schneider-electric.com>
wrote in message
>news:031d01c3af99$64620b70$a101280a@.phx.gbl...
>> I'm using SQLServer 2000 on Windows 2000 Server.
>> We have just purchased a new server. The new server
will
>> have the EXACT same file structure, computer name, and
>> static IP address as the old server. (We DO NOT use
>> replication).
>> I want transfer my databases, DTSs, and Logins to the
new
>> server from the old.
>> How do I so this?
>> Do I only need to transfer the User databases or must I
>> also transfer the Master, Model, Tempdb, and msdb system
>> databases?
>> Thanks
>
>.
>|||Moving SQL Server Databases
http://www.support.microsoft.com/?id=224071
Moving Databases between Servers
http://www.support.microsoft.com/?id=314546
Using WITH MOVE in a Restore to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=246133
Mapping Logins & SIDs after a Restore
http://www.support.microsoft.com/?id=298897
Utility to map users to the correct login
http://www.dbmaint.com/SyncSqlLogins.asp
How to Resolve Permission Issues When a Database Is Moved Between SQL Servers
http://www.support.microsoft.com/?id=240872
User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
Disaster Recovery Articles for SQL Server
http://www.support.microsoft.com/?id=307775
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Brent Murphy" <brent.murphy@.us.schneider-electric.com> wrote in message
news:031d01c3af99$64620b70$a101280a@.phx.gbl...
> I'm using SQLServer 2000 on Windows 2000 Server.
> We have just purchased a new server. The new server will
> have the EXACT same file structure, computer name, and
> static IP address as the old server. (We DO NOT use
> replication).
> I want transfer my databases, DTSs, and Logins to the new
> server from the old.
> How do I so this?
> Do I only need to transfer the User databases or must I
> also transfer the Master, Model, Tempdb, and msdb system
> databases?
> Thanks
>|||One approach that we have used:
Stop sql services (disable) on old SQL server, rename that
server and re-ip it to something temporary.
Name new server and IP it to it's production name/ip.
Install SQL 2000 on it maintaining the exact file
structure of the original.
Apply whatever SP's / hotfixes to get SQL / W2k versions
to match across old box / new box.
Stop all SQL services on new box. Rename the folders
where your mdf's and ldf's are stored.
Copy the folders with the mdf's and ldf's from the
original server to the new server.
Start the services and go into enterprise mgr. and make
sure that all of the DB's are online.
This process will transfer all db's, logins, etc. We have
used it many times. The key is to have the file structure
match exactly as you mentioned. (and sp's)
You will have to recreate any scheduled tasks / maint
plans using this approach. Or you can probably copy them
also by moving the whole mssql folders instead of just
those that contain the data/log files. We haven't tried
this, but it should work.
HTH, Chuck
>--Original Message--
>I'm using SQLServer 2000 on Windows 2000 Server.
>We have just purchased a new server. The new server will
>have the EXACT same file structure, computer name, and
>static IP address as the old server. (We DO NOT use
>replication).
>I want transfer my databases, DTSs, and Logins to the new
>server from the old.
>How do I so this?
>Do I only need to transfer the User databases or must I
>also transfer the Master, Model, Tempdb, and msdb system
>databases?
>Thanks
>.
>|||Thanks Chuck for the informative answer!
I have a question. You said "Stop all SQL services on new
box. Rename the folders where your mdf's and ldf's are
stored."
If I have the same file/folder structure on the new
server, why do I need to rename the folders? I didn't
under stand that part. Is it just so you don't copy over
whatever was put in the orignal folders from the install?
>--Original Message--
>One approach that we have used:
>Stop sql services (disable) on old SQL server, rename
that
>server and re-ip it to something temporary.
>Name new server and IP it to it's production name/ip.
>Install SQL 2000 on it maintaining the exact file
>structure of the original.
>Apply whatever SP's / hotfixes to get SQL / W2k versions
>to match across old box / new box.
>Stop all SQL services on new box. Rename the folders
>where your mdf's and ldf's are stored.
>Copy the folders with the mdf's and ldf's from the
>original server to the new server.
>Start the services and go into enterprise mgr. and make
>sure that all of the DB's are online.
>This process will transfer all db's, logins, etc. We
have
>used it many times. The key is to have the file
structure
>match exactly as you mentioned. (and sp's)
>You will have to recreate any scheduled tasks / maint
>plans using this approach. Or you can probably copy them
>also by moving the whole mssql folders instead of just
>those that contain the data/log files. We haven't tried
>this, but it should work.
>HTH, Chuck
>>--Original Message--
>>I'm using SQLServer 2000 on Windows 2000 Server.
>>We have just purchased a new server. The new server
will
>>have the EXACT same file structure, computer name, and
>>static IP address as the old server. (We DO NOT use
>>replication).
>>I want transfer my databases, DTSs, and Logins to the
new
>>server from the old.
>>How do I so this?
>>Do I only need to transfer the User databases or must I
>>also transfer the Master, Model, Tempdb, and msdb system
>>databases?
>>Thanks
>>.
>.
>|||Sorry took so long for me to get back.. You've probably already
figured this out, but the goal was to rename the data folders on the
new install in case anything went sideways when you copied the data
folders from the original install, so you can go back. Good luck!
Chuck
"Brent Murphy" <brent.murphy@.us.schneier-electric.com> wrote in message news:<0bc201c3b035$e1c4eb40$a101280a@.phx.gbl>...
> Thanks Chuck for the informative answer!
> I have a question. You said "Stop all SQL services on new
> box. Rename the folders where your mdf's and ldf's are
> stored."
> If I have the same file/folder structure on the new
> server, why do I need to rename the folders? I didn't
> under stand that part. Is it just so you don't copy over
> whatever was put in the orignal folders from the install?
>