When restoring a database using a backup file made from another database, you may encounter the following error:
`SQL Server has returned a failure message to LiteSpeed 2005 which has prevented the operation from succeeding.
The following message is not a LiteSpeed 2005 message. Please refer to SQL Server books online or Microsoft technical support for a solution:
RESTORE DATABASE is terminating abnormally.
The database to be restored was named '(source database name)'. Reissue the statement using the WITH REPLACE option to overwrite the '(destination database)' database.
Location of *Data.mdf and *Log.ndf files
Please see video below:
Due to the difference in the location of *Data.mdf and *Log.ndf files, the '@with = N'REPLACE' option must be present within the script. In the 'Restore Database Wizard', select the 'Force restore over existing database' option under the 'Data Files' window.
In the example below, restoring Northwind's backup file over the top of the database NW_test. The script is run in Query Analyzer, against the master database (since that's where the xps are located). The MOVE parameters indicate the location of the files on the destination server.
exec master.dbo.xp_restore_database @database = N'NW_test'
, @filename = N'D:\Backup\Northwind200702150842LiteSpeed_Full.BKP'
, @filenumber = 1
, @with = N'RECOVERY'
, @with = N'NOUNLOAD'
, @with = N'STATS = 10'
, @with = N'REPLACE'
, @with = N'MOVE N''Northwind'' TO N''C:\Program Files\Microsoft SQL Server\MSSQL\data\NW_test_Data.mdf'''
, @with = N'MOVE N''Northwind_log'' TO N''C:\Program Files\Microsoft SQL Server\MSSQL\data\NW_test_Log.ldf'''
All versions of LiteSpeed
All versions of SQL Server