Restoring and upgrading multiple databases I have a SQL Server 2005 instance on my PROD server. I have been given a task to migrate databases from that instance to a different box (SQL Server 2008 R2 on Windows 2008 R2). The current instance has a maintenance plan set up, which backs up 8 different databases every night to a single .bak file.

How do I take that bak file and restore it onto the new SQL Server 2008 R2 instance? I have tried using SQL Server 2008 R2 SSMS, and set the restore from option to device and point it to that .bak file, but what should I target restore to since I have multiple databases to be restored in the new instance?

What is the best approach to migrate all databases in single shot to the new instance without doing one at a time?
First thing i would like to highlight is you should have the system backups in addition to user databases handy when you said you need to migrate from SQL2k5 to SQL2k8R2.

You can copy all the latest backups from the current server onto the Windows2K8 box you mentioned.

Once copied, the first thing you should be looking is restoring the system database backups as mentioned here Backup and Restore system databases

Doing the above will make sure all the logins, SSIS packages, jobs etc would be attached as well when migrating to new version.

Now, you should start restoring the user database backups copied from old server to this new box as guided in Restore database from existing full backups

In addition to above you can use Powershell to completely migrate all the databases in go as you mentioned , from here.

Also, you can refer Migrate SQL server from one machine to another