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 |
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
 Posted - 2007-12-07 : 10:32:08
|
| What is the best and safest way to move all databases(almost 500GB total) from our existing server(2000) to the new server(2005).and What about the jobs and SSIS(DTS) Packages.Thanks |
|
|
jordanam
Yak Posting Veteran
62 Posts |
Posted - 2007-12-07 : 11:09:41
|
| You can make full backups of each database, and restore those to your new server. If you have a complex user/permission scheme, be prepared to either re-create that from scratch, or to run a few changes on system tables, in order to re-associate.Transferring 500 GB can be pretty painful. If you have physical means of transferring the backup files (like, moving a harddrive), definitely go that route. If you have to do it over a LAN, you might want to consider compressing the backups with something like WinRar. Rar compression on a SQL backup has generally yielded me around a 7:1 compression ratio, and Zip has offered roughly 5:1. Raring can take a very long time on large files, so if you don't have a fast server, ignore it. **The latest versions of Winrar DO support multi-threading very well.If you have to transfer over the internet, good luck!Robocopy, a free tool you can download straight from Microsoft (search for rktools.exe), is a great copying utility. I would recommend it, regardless of how you move the files. It supports resuming, as well as a number of other features. It is completely command-line drive (although you can get an interface for it from MS... it sucks), and you can script it into batches.As for jobs and packages, I will leave that open... jobs you can copy out from system tables, and the packages can be imported, although I don't know how competently SQL Server deals with that. |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-12-07 : 11:13:02
|
| Jobs you can script and run the script on the other server. Would be a good time to review them (and the owners).SSIS packages - if you keep them in the file system then it's just a matter of copying the files, if you don't then consider making the 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. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-12-07 : 23:04:36
|
| You can move jobs with ssis task, and migrate dts packages with wizard. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2007-12-09 : 17:09:55
|
| Thanks for the Reply.I have a very important Questions1) What should i do with system database ( can i backup and restore in new server especially Master and MSDB database?2) How should i move the system database?Can you please give exact right direction? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-12-09 : 17:46:18
|
| If both servers have same version and sp level, you can backup them on one server then restore them on the other. From sql2k to sql2k5, nothing you can do. |
|
|
|
|
|
|
|
|
Move all database from one server to other