Sql-server – Unable to restore database

The message seems pretty clear: you cannot be using the DB1 database when trying to restore it - instead, use the master database!

So in SQL Server Management Studio, first close all windows that are open and using that database! Then run this:

USE master
GO

RESTORE DATABASE [DB1] 
FROM DISK = N'D:\new.bak' 
WITH RESTRICTED_USER, FILE = 1, NOUNLOAD, REPLACE, STATS = 10;

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.