SQL Server 2008 Restore

Question

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.

Answer 1

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.

Answer 2

Thanks for your suggestions everyone.. the issue ended up being my own mistake. The backup file that I was recovering from contained TWO database backups and a database log backup. My issue was that I was choosing to restore from the log AND one or more database backups. The solution was just ticking off ONE of the database backups contained in the backup file..

Again, thanks for the help everyone!