SQL Server 2008 Restore I'm trying to restore from a database backup to SQL Server 2008.  The backup came from a different server.  I'm executing the following query:

RESTORE DATABASE Database_Name
FROM DISK = 'C:\Path\To\Backup.file'
WITH REPLACE
GO


If the database does not exist, I'm getting the following error:


  Msg 3118, Level 16, State 1, Line 1
  The database "Database_Name" does not exist. RESTORE can only create a database when restoring either a full backup or a file backup of the primary file.
  Msg 3013, Level 16, State 1, Line 1
  RESTORE DATABASE is terminating abnormally.


The file is a full backup.

If the database does exist, I get the following error:


  Msg 3154, Level 16, State 4, Line 1
  The backup set holds a backup of a database other than the existing 'Database_Name'  database.
  Msg 3013, Level 16, State 1, Line 1
  RESTORE DATABASE is terminating abnormally.


From what I understand, WITH REPLACE is supposed to be a workaround for this.

I'm stumped.
Try this:

CREATE DATABASE foo;
GO
CREATE DATABASE bar;
GO
BACKUP DATABASE foo TO DISK = 'c:\temp\x.bak';
BACKUP DATABASE bar TO DISK = 'c:\temp\x.bak';
GO
RESTORE HEADERONLY FROM DISK = 'c:\temp\x.bak';


Relevant columns for this scenario:

Position  DatabaseName
--------  ------------
1         foo
2         bar


To restore foo, whether it exists or not:

RESTORE DATABASE foo FROM DISK = 'c:\temp\x.bak'
  WITH FILE = 1, REPLACE;


To restore bar, whether it exists or not:

RESTORE DATABASE bar FROM DISK = 'c:\temp\x.bak'
  WITH FILE = 2, REPLACE;


If you don't specify WITH FILE, you will get a slew of errors, depending on whether the database in Position = 1 already exists.

The lessons here:


do not use common and generic names for backup files - name them for the database they represent and keep them separate.
use WITH INIT if you expect a backup file to only ever contain one copy of a backup.
understand what is in your backup by first running RESTORE
HEADERONLY and/or RESTORE FILELISTONLY.


If you show us those two columns from the output of RESTORE HEADERONLY and what database you are actually trying to restore, we can provide you with a specific command that should work.