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