script to restore database sql server from bak file, doesn't work

Question

I have an empty database:

DB_Clients

And I want to restore the database from a .bak file:

OldDBClients.bak 

This is the path:

C:\OldDBClients.bak 

And this is my script:

USE [master] GO     RESTORE DATABASE DB_Clients     FROM DISK = 'C:\OldDBClients.bak' 

When I execute it, I get this error message:

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

Can someone tell me why this happen? I have to point that the file has the permissions to read and write.

Thank's.

Answer 1

You need to use WITH REPLACE option in order to overwrite the existing database.

RESTORE DATABASE DB_Clients FROM DISK = 'C:\OldDBClients.bak' WITH REPLACE 

Probably you also need to specify WITH MOVE options; in this case:

• use RESTORE FILELISTONLY FROM DISK = 'C:\OldDBClients.bak' to know logical name of your MDF/LDF
• use WITH MOVE options in your RESTORE

For example:

RESTORE DATABASE DB_Clients FROM DISK = 'C:\OldDBClients.bak' WITH REPLACE, MOVE 'YourMDFLogicalName' TO '<MDF file path>', MOVE 'YourLDFLogicalName' TO '<LDF file path>' 

Please note that you can also DROP your empty DB_Clients database and use a simple RESTORE.

Answer 2

You should this syntax:

USE [master] GO RESTORE DATABASE DB_Clients FROM DISK = 'C:\OldDBClients.bak' WITH  MOVE 'DB_Clients' TO 'D:\SQLServer\Data\DB_Clients.mdf', MOVE 'DB_Clients_log' TO 'D:\SQLServer\Log\DB_Clients.ldf', REPLACE 

It instructs SQL Server to overwrite the existing copy and specifies a valid location for your data and log files