Script to restore SQL Server database from bak file does not 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 way:

 C:\OldDBClients.bak 

And this is my script:

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

When I executed it, I get the following error message:

Msg 3154, Level 16, State 4, Line 15
The backup set contains a backup copy of a database other than the existing DB_Clients database.
Msg 3013, Level 16, State 1, Line 15
RESTORE DATABASE ends abnormally.

Can someone tell me why this is happening? I must indicate that the file has read and write permissions.

Thank x.

Answer 1

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

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

You probably also need to specify WITH MOVE options; in this case:

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

For instance:

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

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

Answer 2

This syntax should look like this:

 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 indicates a valid location for your data and log files.

Answer 3

Step 1: Verify the logical file names with the following command:

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

Step 2. Use the logical names that you get from the above query in the following 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 executing the above commands with the correct values, you will see output similar to the following:

 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