Replace SQL server in multiple server network

Hello all DBAs
what is the best methodolgy to replace a server with a new server in a
multiple sql server environment.. Is is using back up/restore to
another/bridge type server then rename servers or is it to bring in new
server then tranfer the datafiles. Does tranfering the data files
include logins and jobs ?? Or is ther a better way that I have not
realised?

Bill

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!"William Logan" <kwiso@.msn.com> wrote in message
news:4152f0c3$0$26150$c397aba@.news.newsgroups.ws.. .
> Hello all DBAs
> what is the best methodolgy to replace a server with a new server in a
> multiple sql server environment.. Is is using back up/restore to
> another/bridge type server then rename servers or is it to bring in new
> server then tranfer the datafiles. Does tranfering the data files
> include logins and jobs ?? Or is ther a better way that I have not
> realised?

Funny you should ask that.

I'm doing that right now. The complication is one of our larger databases
has file corruption that is preventing me from doing backups.

So... for all but one user database the plan will be to do a full backup
from the source machine and then restore to the new machine.

Then, at the proper time, shut down the source machine, perform a
transaction backup, then restore that to the destination machine.

As for user logins, jobs, etc. There are DTS packages that will help.
(though the DTS job to move logins does NOT move the sid, so you either have
to script that by hand or you have to do a sp_change_users_login for each
user in each database.

MS has several KB articles you can search for that outline some of the
steps.

So far the transfer looks to be fairly straightforward (other than that one
DB I've got to do by hand.)

> Bill
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Thanx that is what I thought and told my cio I was told to do it another
way like copying the files over by some else I like my way better thanx
Bill

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||If you can afford the downtime a simple detach and reattach should do
the trick pretty quickly with minimal overhead. You just have to wait
for the files to copy from one server to the next.

Its usually much faster than a backup and restore.

Then run some scripts to re-permission the users, You can then use DTS
packages to move your jobs and DTS packages to the new server.

Either way you need to figure out how to repermission and move jobs...
why spend the extra time on a backup and restore?|||"Bill" <nospam@.devdex.com> wrote in message
news:4154423e$0$26123$c397aba@.news.newsgroups.ws.. .
> Thanx that is what I thought and told my cio I was told to do it another
> way like copying the files over by some else I like my way better thanx

Well, it depends.

If I could afford the downtime, I'd probably detach the databases from the
primary server and copy them to the secondary and then re-attach them. Much
simpler.

But I can't afford the downtime that would require in my case.

> Bill
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!