Tuesday, May 6, 2008

The backup set holds a backup of a database other than the existing database

This error message appear when I tried to restore an SQL 2K SP4 db into SQL 2005, using SQL Server Management Studio.
Although I have specified overwrite existing database, the error message still appear.
So I run transact-sql statement instead :

restore filelistonly
from disk = 'D:\SQL Backup\sourceDB.bak'
GO

--This will show the logical file name needed in the operation below
restore database targetDB
from disk = 'D:\SQL Backup\sourceDB.bak'
WITH MOVE 'sourceDB_Data' TO 'D:\SQLDATA\targetDB_Data.MDF',
MOVE 'sourceDB_Log' TO 'D:\SQLDATA\targetDB_Log.ldf',
REPLACE,STATS
GO

-- The option with replace is mandatory as it will overwrite existing database. With stat will show the operation progress

8 comments:

  1. Many Thanks!! I am used to SQLServer 2000 and using SQLServer Express to work from home on company data. I managed to import the dB file but had to delete the existing database as it kept saying that the Database I was trying to restore TO was in use. I deselected it, also shut Windows Explorer but no good. I eventually deleted the DB using the Manager and the database imported without a problem.

    I must say that the quality of your help was spot on. Once again thanks.

    ReplyDelete
  2. Hi,

    I am glad that it helps :)

    Regards,

    Agus

    ReplyDelete
  3. Great! It works perfectly!

    ReplyDelete
  4. i am using the same script however i still get the same error message.

    I appreciate your help

    ReplyDelete