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.

tezzo · Accepted Answer · 2019-06-24 08:07:04Z

34

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.

edited Jun 24, 2019 at 8:07

answered Nov 18, 2015 at 15:34

tezzo

11.2k2 gold badges27 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Esraa_92 Over a year ago

I get this error: ' Use WITH MOVE to identify a valid location for the file. '

Esraa_92 Over a year ago

Thank's for your help ; -D

sean2078 Over a year ago

FYI - Above syntax is incorrect - Would need to remove the 2nd WITH after the comma for this to be valid syntax

Alex · Accepted Answer · 2015-11-18 15:43:39Z

14

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

edited Nov 18, 2015 at 15:43

answered Nov 18, 2015 at 15:30

Alex

21.9k11 gold badges68 silver badges77 bronze badges

4 Comments

Esraa_92 Over a year ago

*Your code give me this error: * Msg 156, Level 15, State 1, Line 27 Incorrect syntax near the keyword 'FROM'.

Esraa_92 Over a year ago

I get this error: ' Use WITH MOVE to identify a valid location for the file. '

Alex Over a year ago

I added WITH MOVE. You will have to replace the paths with a valid location on your system.

Esraa_92 Over a year ago

Thank you very much for your help ;-D

Raghav · Accepted Answer · 2019-10-20 06:11:06Z

8

Step 1: Check the logical file names with the help of the following command:

RESTORE FILELISTONLY 
FROM DISK = 'E:\DBBackups\mydb.bak'

Step 2: Use the logical names you get from the above query in the below query:

 RESTORE DATABASE [mydb_new]
  FILE = N'<MDFLogicalName>'
  FROM DISK = N'E:\DBBackups\mydb.bak'
  WITH 
    FILE = 1, NOUNLOAD, STATS = 10,
    MOVE N'<MDFLogicalname>'
    TO N'E:\DBBackups\mydb_new.mdf',
    MOVE N'<LDFLogicalName>'
    TO N'E:\DBBackups\mydb_new_0.ldf'

After running the above commands with the correct values you will see the output like this:

10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
100 percent processed.
Processed 7672 pages for database 'mydb_new', file '<MDFLogicalname>' on file 1.
Processed 5 pages for database 'mydb_new', file '<LDFLogicalName>' on file 1.
RESTORE DATABASE ... FILE=<name> successfully processed 7677 pages in 0.780 seconds (76.893 MB/sec).

Completion time: 2019-10-20T11:35:31.8343787+05:30

answered Oct 20, 2019 at 6:11

Raghav

9,7367 gold badges91 silver badges115 bronze badges

1 Comment

Asher Over a year ago

Can you explain the purpose of file = 1 ?

Collectives™ on Stack Overflow

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

3 Answers 3

3 Comments

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related