Restoring two databases from a single backup file (SQL Server 2005)

Question

I have a .bak file which contains backup sets of two different databases. It was made by sql server maintenance plan. Now I have to restore both databases. The problem is, that while the first database is restored ok (db_companies) the other database (db_data) gives an error:

Restore failed for Server 'SBSERVER'. (Microsoft.SqlServer.Smo)

System.Data.SqlClient.SqlError: Logical file 'CompaniesDB' is not part of database 'DataDB'. Use RESTORE FILELISTONLY to list the logical file names. (Microsoft.SqlServer.Smo)

The database restore wizards shows up both databases, and I select full and the lastest differential sets. The RESTORE FILELIST command show only the CompaniesDB. What's up with this?

I've also tried RESTORE DATABASE WITH MOVE but it doesn't recognize the DataDB logical name.

Is there any way to restore the DataDB from the backup set?

Answer 1

1)If you (in Management Studio), detach db_data, back up the existing mdf and log, then delete database completely, making sure the mdf and log are gone from their original DATA folder.
2)Then, right click on databases and go to "Restore Database"
3)Choose the source first! Go to "From Device" and browse to the .MDF
4)Now expand the drop down list with the heading "To Database" under the "Destination for Restore" section and choose db_data.
5)Click OK
6)It should restore fine, right click on it, go to properties and set the owner.

Note, this is me using MSSQL 08 but if memory serves me right there wasnt a drastic difference in management studio for 05.

Im sure someone else here knows a cleaner way, but the above will work.

Answer 2

The bak file may not have the backup of both databases.

Depending on your setup, it's one backup per file unless the file is actually a "device" and the backup did not initiase the device/file.

Answer 3

After doing some intensive research, I finally found out that this is a bug in sql server 2005. After I installed the SP3 everything went fine.