Hi,

The easiest way to do this type of migration is Backup old DB, Copy Backup to new server, Restore on new server.

It is a simple process but there are many ways to make mistakes and when you have 100 db's that gives you 100X opportunities to stumble:w00t:

First for the backups. How to Automate the Backup of a SQL Server 2005 Express Data Store is about SQL 2005 Express on Citrix but the process is the same. Once you do it a couple of times you can script and automate the process pretty easily.

File copy is File copy, or perhaps use a SAN or Network Share.

Restore on SQL 2008R2 is pretty straight forward. Step through the restore wizard and then generate a restore script (option at top of the wizard). You can parameterize the generated script to automate restores.

You don't have to do all at once (unless you application requires all 100 dbs). You can test the process by moving a couple of dbs to "pre-production". I would run through the whole process at least 3 or 4 times.

Using this approach you can minimize overall downtime (again if your application supports it). Any given db is only down from the time of it's last backup on the SQL 2005 side to the time it is restored on SQL 2008R2.

I have done several migrations like this and I urge you to do the test cycle. The upgrade from 5 to 8 is mostly seamless but there are potential issues depending on how the database is designed and implemented.

The credentials are a whole 'nother kettle. It depends on whether the apps use SQL Logins or Windows Security. Security Groups or individual logins.

While the process is "simple" you might consider consulting with a good dba for the project. You should be able to find someone through a local SQL Server User Group

Good luck.