Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
2  
Have you validated the database you're trying to restore is within the file you're restoring from? Use RESTORE HEADERONLY FROM DISK = 'C:\Path\To\Backup.file' to see what database backups are contained within. –  Mike Fal Mar 19 '13 at 21:58
    
Why are you trying to restore WITH REPLACE when the database doesn't already exist? Am I missing something? –  Jon Seigel Mar 19 '13 at 22:26
    
Is it possible you backed up multiple databases to this generic 'C:\Path\To\Backup.file' without using WITH INIT? –  Aaron Bertrand Mar 20 '13 at 1:34

2 Answers 2

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.

share|improve this answer
up vote 0 down vote accepted

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!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.