Restore clients database on my desktop

[stockton]

I am having a problem restoring my clients MSSQL database onto my desktop using Enterprise Manager.
Apparently the database itself contains information about where the original database was backed up from & irrespective of what alterations I make in Enterprise Manager the restore fails.
Is there some other program I can use to do the restore?

[sknake]

This is a common problem and you can override it. What version of MSSQL are you using?

Here is a query which restores the .mdf and .ldf from the .bak file.

RESTORE DATABASE [ServMan] FROM  DISK = N'K:\Volume3\BackupSqlDbo\ServManNew_backup_200908302125.bak' WITH  FILE = 1,  MOVE N'ServManNew' TO N'D:\Microsoft SQL Server\Data\ServMan.mdf',  MOVE N'ServManNew_log' TO N'D:\Microsoft SQL Server\Data\ServMan_log.ldf',  NOUNLOAD,  REPLACE,  STATS = 10

[edit]Note the name of ServMan.mdf but the DB is ServManNew.bak. I had the same issue and just changed the logical file paths for the backup file[/edit]

[stockton]

I attempted your suggestion with:-

RESTORE DATABASE [Frontier] FROM  DISK = N'G:\FrontierDB\GMSBackup30.bak' WITH  FILE = 1,  MOVE N'FrontierNew' TO N'c:\Microsoft SQL Server\Data\Frontier.mdf',  MOVE N'FrontierNew_log' TO N'c:\Microsoft SQL Server\Data\Frontier_log.ldf',  NOUNLOAD,  REPLACE,  STATS = 10

and the reply/errors I got were:-
Server: Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

[stockton]

Got it based on your help, Thank you.
I used:-

use Master;
Alter Database Frontier SET SINGLE_USER With ROLLBACK IMMEDIATE; 
RESTORE DATABASE [Frontier] FROM  DISK = N'G:\FrontierDB\GMSBackup30.bak' WITH  FILE = 1,  MOVE N'FI_GMS_Slots_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Frontier.mdf',  MOVE N'FI_GMS_Slots_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Frontier_log.ldf',  NOUNLOAD,  REPLACE,  STATS = 10;

[sknake]

Sounds good. I also use a killall sproc to forcible terminate connections from a database when I want to restore. Here is the query.

CREATE PROCEDURE [dbo].[killall]  
       @DBName varchar(20) 
as

--Declare @DBName varchar(30)
Declare @Spid int

Declare det_cursor CURSOR FOR

select spid
from  master.dbo.sysprocesses
Where
(case
  when dbid = 0 then null
  when dbid <> 0 then db_name(dbid)
   end
) = @DBName

OPEN det_cursor

FETCH NEXT FROM det_cursor
INTO @Spid

While @@FETCH_STATUS = 0
BEGIN
  exec('kill ' + @spid)
  --print cast(@spid as varchar(10)) 
  FETCH NEXT FROM det_cursor
  INTO @SPID
END

CLOSE det_cursor
DEALLOCATE det_cursor

return 0

Please mark this thread as solved if you have found the answer to your original question and good luck!