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

Esraa_92 picture Esraa_92 · Nov 18, 2015 · Viewed 62.7k times · Source

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

tezzo picture tezzo · Nov 18, 2015

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.