SQL2005 to SQL2008R2 side by side migration

Depending upon time data speeds between the two servers, my vote would be to go for a backup/restore. If both are on premise, you could probably move the entire backup once the older database has been made offline.

If data speeds are not that great, I would restore the last known full  backup (e.g. the one taken the previous night) using the WITH RECOVERY option. Before taking the older database offline, take a tail log backup. On the new server, you would just need to restore the most recent differential and log backups.

Once the restore is done, you would of course need to do post tasks like setting up security (logins/users), agent jobs, maintenance jobs, changing of the compatibility level, updating statistics, rebuild indexes, etc.

1) Back up the master and msdb dbs; optionally model db.
2) Restore the master db.
3) Restore the msdb db; optionally model db.
4) Disable all the jobs.

That will prep the new environment.

Then, ahead of time but as close to the final cut-over as you can but still leaving plenty of time for any mix-ups or failures:

1) Back up the user db(s).
2) Make sure no more full backups run on the old instance.
3) Restore the user db(s) WITH NORECOVERY.

In order to test, you can do WITH RECOVERY, then, after testing, do the final version WITH NORECOVERY.

At final cut-over time, for each user db:

1) Get in the context of that db:
USE [user_db_name];
2) Put db into single_user (or restricted) mode.
ALTER DATABASE [user_db_name] SET SINGLE_USER /*or RESTRICTED_USER*/;
3) Do a differential backup.
4) Switch to the master db.
USE master;
5) Take the user db offline.
ALTER DATABASE [user_db_name] SET OFFLINE WITH ROLLBACK IMMEDIATE;
6) Copy the diff backup to the new instance.
7) Restore the diff backup WITH RECOVERY.

When ready, re-enable all the jobs (of course you may want to leave the job disabled if it was disabled on the original instance).

Hi Nakul, yes, both servers are on premise and are running on a gigabit network.  Is in intrusive to backup the database during production hours or is this something that needs to be done during our maintenance window?

Hi Scott,  is it necessary to backup the master and msdb dbs model db?  The name of the database i am looking to migrate is named:  "moveitdmz".  I was thinking to do a simple backup of the moveitdmz database to the local server (ServerA), then copy the backed-up database to ServerB, then restore the database to SerevrB.  once the restore is complete, I found a Script that would transfer logins and passwords between ServerA and ServerB.  Is this accomplishing anything different than what you are proposing?

It depends.  There's potentially more in the master db than just logins, in particular the instance settings (max memory, etc.).  For a simple, single-db move, you could probably just duplicate the master db settings and re-create the logins, being sure to use the same sid for native SQL logins/users.

Scott,  will your method of moving the master and msdb dbs and model db also move any jobs from the old server?  Also, can  backup these databases (including the "moveitdmz" database during production hours?  Will that impact end users' access to that server while it is busy backing up?

Moving msdb will move the jobs, since they are stored in msdb.

Backups don't affect access to the db: the db can still be fully used while a backup is in progress.

FYI, linked server info is also stored in the master db.  If you don't restore master, and you do have linked servers, you'll need to recreate all your linked server definitions.  You probably don't have endpoints, but those would be in the master db as well.

Ok, I think I will follow your steps above.  The only parts I am not sure i follow are below and if I can sort that out, I think I got it:  

At final cut-over time, for each user db:

1) Get in the context of that db:
USE [user_db_name];
2) Put db into single_user (or restricted) mode.
ALTER DATABASE [user_db_name] SET SINGLE_USER /*or RESTRICTED_USER*/;
3) Do a differential backup.
4) Switch to the master db.
USE master;
5) Take the user db offline.
ALTER DATABASE [user_db_name] SET OFFLINE WITH ROLLBACK IMMEDIATE;

Thank you for your assistance!

You're welcome!  Good luck with the migration ... they can be easy or they can be a royal pain.