I have a SQl 2005 Instance in my PROD SQL server. I have been given a task to migrate databases inside that instance to different SQL box ( SQl 2008 r2 in Win 2008 r2 box) Database. Current sql instance have maintenance plan set up which backs up 8 different database every night under single .bak file.

Now how do I take that bak file and restore it into new SQl 2008 r2 instance. I have tried using SSMS in Sql 2008 r2 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 restored in this new instance.

What is the best approach to migrate all databases in single shot to next instance without doing one at a time. Please advise..

Hi,

> but what should I target restore to since I have multiple databases restored in this new instance.

You should select the required file an target to DB you want to restore

Since you said in single .BAK file you have multiple backup files you need to run below command to get details of what different files are there in

restore filelistonly from disk='backuplocation\backup.bak'

now when you restore from this backup select the file which corresponds to the database you want to restore.

dummy query

RESTORE DATABASE AdventureWorks2012
   FROM AdventureWorksBackups
   WITH FILE=3, RECOVERY;

See I have taken file3 in above query.

Sorry if that particular sentence confused you.

Since  I am trying to restore all the database from SQL server 2005 to 2008 R2. I did install a 2008R2 in different Server( Test). The I did right click on the database than restore the data base.

The concern I had was since I havent created any database in the new sql server 2008 R2, what do I provide under TO database

And my next question was, if I have 8 .bak file thats been backed up everynight, is there a way I could restore all the 8 file to the new Instance of SQL Server 2008R2 at once? I want the exact copy of whatever I had in the previous version of SQL server (2005) to the new database (2008R2).

Please suggest

The concern I had was since I havent created any database in the new sql server 2008 R2, what do I provide under TO database

Can you show me the screenshot where you are getting stuck it would help me understand better

And my next question was, if I have 8 .bak file thats been backed up everynight, is there a way I could restore all the 8 file to the new Instance of SQL Server 2008R2 at once? I want the exact copy of whatever I had in the previous version of SQL server (2005) to the new database (2008R2).

Please suggest

Yes you can but then you have to use TSQL query even in that case restore would be one by one and that would depend on what database is restored first

Why not try TSQL command which I have given above its simple and easy

Sorry if that particular sentence confused you.

Since  I am trying to restore all the database from SQL server 2005 to 2008 R2. I did install a 2008R2 in different Server( Test). The I did right click on the database than restore the data base.

The concern I had was since I havent created any database in the new sql server 2008 R2, what do I provide under TO database

And my next question was, if I have 8 .bak file thats been backed up everynight, is there a way I could restore all the 8 file to the new Instance of SQL Server 2008R2 at once? I want the exact copy of whatever I had in the previous version of SQL server (2005) to the new database (2008R2).

Please suggest

since you are not familiar with this.. my advice is to watch some videos on youtube on how to restore the database from backup file.

I strongly believe, it will help you to understand and you can get some confidence if you have atleast seen somebody do it and have some idea on how to do it..

check out those videos ..just search for " sql database restore"