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

backupdatabase-restoresql-server-2005

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?

Best Answer

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.

Related Solutions

Sql-server – SQL Server: Database stuck in “Restoring” state

I had this situation restoring a database to an SQL Server 2005 Standard Edition instance using Symantec Backup Exec 11d. After the restore job completed the database remained in a "Restoring" state. I had no disk space issues-- the database simply didn't come out of the "Restoring" state.

I ran the following query against the SQL Server instance and found that the database immediately became usable:

RESTORE DATABASE <database name> WITH RECOVERY

How to restore to a different database in sql server

You can create a new db then use the "Restore Wizard" enabling the Overwrite option or:

View the contents of the backup file:

RESTORE FILELISTONLY FROM DISK='c:\your.bak'

note the logical names of the .mdf & .ldf from the results, then:

RESTORE DATABASE MyTempCopy FROM DISK='c:\your.bak'
WITH 
   MOVE 'LogicalNameForTheMDF' TO 'c:\MyTempCopy.mdf',
   MOVE 'LogicalNameForTheLDF' TO 'c:\MyTempCopy_log.ldf'

This will create the database MyTempCopy with the contents of your.bak.

(Don't create the MyTempCopy, it's created during the restore)

Example (restores a backup of a db called 'creditline' to 'MyTempCopy'):

RESTORE FILELISTONLY FROM DISK='e:\mssql\backup\creditline.bak'

>LogicalName
>--------------
>CreditLine
>CreditLine_log

RESTORE DATABASE MyTempCopy FROM DISK='e:\mssql\backup\creditline.bak'
WITH 
   MOVE 'CreditLine' TO 'e:\mssql\MyTempCopy.mdf',
   MOVE 'CreditLine_log' TO 'e:\mssql\MyTempCopy_log.ldf'

>RESTORE DATABASE successfully processed 186 pages in 0.010 seconds (144.970 MB/sec).

Best Answer

Related Solutions

Sql-server – SQL Server: Database stuck in “Restoring” state

How to restore to a different database in sql server

Related Topic