how to move dataabse from old to new server?

[WiccaChic]

Hi all. I know that since you know nothing about my database it will
be a stretch to answer this - but does anyone have a boiler plate
checklist for moving a database, dts packages, agent jobs, etc...from
one server to another? Maybe something that just says I should at
least do x,y,z.

Also if anyone out there has any hard won experience/advice about
gotchas when doing this I would love to hear it. We have outgrown a
server and are installing a new one this week that we need to move
over to.

Thanks!

 

[nigelrivett]

Make sure the collation is the same.

You could just restore backups of all the databases - including system databases.
Then do a reinstall to change the server name. Might have to update the server in msdb..sysjobs.
See connections in dts packages below.
Check @@server_name.

dts packages - copy masdb..sysdtspackages to the new server.
If you have hard coded connections then you will have to change them. Might be a good time to change to get the server/databases from a table or ini file.
Databases - backup and restore. You might have a problem wit the logins mapping to users in the databases - see sp_changeuserslogin or just drop the users and add them again.
jobs - you can script the jobs and run the scripts on the destination server.

Any hard coded server names anywhere you will have to change.


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[JayKusch]

May take a look at this link as well as follow Nigels' pointers.

http://vyaskn.tripod.com/moving_sql_server.htm

Thanks

J. Kusch