Discover the incredible range of high-quality hair extensions and hair care products at AbHair.co.uk! Elevate your hairstyle and transform your look with their expertly crafted extensions. Visit AbHair.co.uk to explore their collection and find the perfect match for your hair needs.

Error restoring database back file in SQL Server 2005

I made a backup of all databases to a single file using the SQL 2005 Standard Edition maintenance plan wizard. I have been unable to restore databases from this type of backup file.

 

Update; August 2, 2006: I have been told that this bug still exists in SP1 but I have not tested it myself. Below is a link to the bug report that I submitted to Microsoft. It contains a possible work around for restoring the database via a script. I have not tested this workaround.

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124718

 

 

Written by: Greg Van Mullem

Written: February 8, 2006

Last Updated: August 2, 2006

 

This web page is part of a collection of web pages containing difficult to find technical information about the initial release of Microsoft SQL Server 2005 Standard Edition. Click here to go to the main index.

 

 

I made a backup of all databases to a single file using the SQL 2005 Standard Edition maintenance plan wizard. I have been unable to restore databases from this type of backup file. I�m not sure if it is the backup or the restore operation that is broken. I have no resolution for this problem. However, I was able to work around it by using the option to backing up each database to a separate file. I�m posting this without a resolution so that you will not discover the problem when the restore really matters!

 

Here is the error dialog box that we get when attempting to restore a database from this file. We can�t find any useful information about this error message.

 

 

Here is the full text of the error message:

 

TITLE: Microsoft SQL Server Management Studio

------------------------------

 

Restore failed for Server 'sqllasttest'.� (Microsoft.SqlServer.Smo)

 

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

 

------------------------------

ADDITIONAL INFORMATION:

 

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

 

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&LinkId=20476

 

------------------------------

BUTTONS:

 

OK

------------------------------

 

Here is the screen where we are trying to restore the database:

 

Notice that the database name is UNEdb but under �Original File Name� is says �master� and �mastlog�. I suspect that this is the problem but these fields are not editable so I can�t fix it.

 

 

Here is how we created the backup file that can�t be restored. In Microsoft SQL Server Management Studio:

 

(1) Right click �Management/Maintenance Plans� and select �Maintenance Plan Wizard�.

 

(2) Click Next

 

(3) Select �Back Up Database (Full)� and click next.

 

(4) Hit next again which takes you to this screen. We filled it out with the shown values and clicked Next.

 

(5) Click Next threw the rest of the screens. Here is the final screen where we clicked Finish.

 

(6) We then right clicked the maintenance plan to execute. It creates a file with the correct sizes right where it should be.

 

The End�