Can't Restore Database

For years I've been using SQL Server 2000. Before heading out on a recent trip, I backed up a database on it and copied this backup over to my laptop. On my laptop I installed SQL Server 2005 Express and successfully restored this backup. While traveling I did development work using this database and it all worked great.

Now that I'm home, I'd like to backup & restore the laptop's database back onto my server. I've since removed SQL Server 2000 and have instead installed SQL Server 2005 Express. I followed exactly the same procedure as before, first creating the required database and then restoring it.

But the restoration failed with the following error and I'm not sure what's wrong:

Restore failed for Server 'SERVER\SQLEXPRESS'. (Microsoft.SqlServer.Express.Smo)

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

ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing 'Incentives' database. (Microsoft.SqlServer.Express.Smo)

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

If anyone has any ideas, I'd love to hear them!

Robert W.

Vancouver, BC

Perhaps a 'typo' is involved. Check the database name on the backup. Try:

Code Snippet

RESTORE FILELISTONLY
FROM DISK = {FilePathName}

|||

Arnie, I'm using the dialog box in the SQL Server Mgmt tool and picking everything so I doubt that it could be a typo problem.

Robert

|||

The error message indicates that the database you are attempting to restore does not exist in the backup file (media).

Do the two computers have the same Service Packs installed on SQL Express?

|||

Ah hah, they're not! The destination computer doesn't have SP2 installed. Correcting now! I'll keep you posted. Thanks, Arnie!!

Robert

|||

Hi Arnie,

Well, there's egg on my face! I updated SQL Server Express 2005 to SP2 and tried restoring. Didn't work. Then I wondered if perhaps I shouldn't have first created a blank DB, like I *thought* I had to do with the initial backup & restore. So I deleted the empty DB and then tried the restore from scratch. Worked perfectly!

Thanks for your time & patience with this!!

Robert W.

|||Thanks for letting us know that you were finally successful. Good luck.|||Thanks, Arnie. Incidentally, I can't connect to the SQL Server 2005 Express DB like I used to be able to before with the SQL Server 2000 one. It has been so long since I setup that old one. Are there some permissions or such that I need to set so that I can connect to it from my laptop?

Or am I perhaps not specifying the username correctly. In my web app, I have the following variables set in Web.config:

<add key="DataBaseServer" value="SERVER\SQLEXPRESS"/>
<add key="DataBase" value="Incentives"/>
<add key="UserId" value="SERVER\Inc_Admin"/>

FYI I've setup "Inc_Admin" as a special login on my server's SQL Server rather than just using "sa" as I did in the past. But I also tried "Inc_Admin" and "sa" as substitutes for "UserId" but neither worked. So I'm thinking that it is perhaps a permissions issue.

Robert
|||Arnie,

More egg on my face! I just found this: http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

And it solve the problem.

Robert

|||Egg salad sandwiches for lunch tomorrow!