Restoring a Backup with SQL Server

Question

Hello,

I have a SQL Server database that I am trying to restore. My source database was on a SQL Server 2005 installation. My new server is a SQL Server 2008 machine. I have backed-up the database on the SQL Server 2005 installation. I am trying to restore it onto the SQL Server 2008 installation.

When I try to restore the backed-up database, I receive an error that says:

TITLE: Microsoft SQL Server Management Studio
------------------------------
Restore failed for Server 'DBSERVER'.  (Microsoft.SqlServer.SmoExtended)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1540+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The backup set holds a backup of a database other than the existing 'MyDatabase' database.
RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error: 3154)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=3154&LinkId=20476

What is the problem? What am I doing wrong? I have checked the "Overwrite existing database (WITH REPLACE)" box on the Options page.

Thank you

Answer 1

You should verify that the destination directories exist for the database. By default the database files will be restored to the directories from which they came. It's likely that the destination directory doesn't exist because the SQL Server 2005 default location is different than the SQL Server 2008 default location.

You can look at the Options page, "Restore the database files as..." section in the Restore dialog. It contains a list of files that will be restored. Look at the "Restore As" column and verify that the destination directory exists for each file.

If the destination doesn't exist, then change the "Restore As" value to a valid file path. This is known as the MOVE option in the RESTORE DATABASE command.