CODEWITHSUNDEEP
sql-server
R.D.
R.D.

Reputation: 7403

SQL-Server: The backup set holds a backup of a database other than the existing

I am trying to restore a SQL Server backup file for my database, but it is throwing an error as follow:

The backup set holds a backup of a database other than the existing

My database in SQL Server 2008 and the backup file is in 2005.

What can be the problem?

Upvotes: 642

Views: 644870

Answers (26)

Smit Patel
Smit Patel

Reputation: 3247

I was facing same problem and found the solution by doing this, using SSMS 2014 

Just select the Option Overwrite the existing database(WITH REPLACE)

Existing Database> Task> Restore> Database

Upvotes: 76

Naser Nikzad
Naser Nikzad

Reputation: 941

Usually dealing with .bak files are coming with headaches, a more straight forward way is using sqldump files to transfer databases.

  1. Generate script of current database you want to move or copy.
  2. Instead of entire database, select All Tables.
  3. From the option menu choose save as script, and click on advance button and set the following configs (I am suing MSSQL 2016): enter image description here
  4. Now, where you want to import these data, create a new database, switch to this new database (I mean set it as default)
  5. Finally run the script, all table and data will be imported.

Good Luck.

  1. List item

Upvotes: 0

gui2
gui2

Reputation: 51

I had the same issue but on PS. I leave it here in case someone is trying to do the same

Restore-SqlDatabase -ServerInstance "<your instance name>" -Database "<your db name>" -BackupFile "<backup file route>.bak" -ReplaceDatabase

remember to use Set-ExecutionPolicy Unrestricted -Force and import import-module sqlps. Don't forget to set back your Execution Policy back to restricted once you are done.

Upvotes: 0

tinonetic
tinonetic

Reputation: 8036

If you are using the script approach and have an error concerning the LDF and MDF files, you can first query the the backup file for the logical names (and other details) of files in the backup set, using the following:

-- Queries the backup file for the file list in backup set, where Type denotes 
-- type of file. Can be L,D,F or S
-- info: https://learn.microsoft.com/en-us/sql/t-sql/statements/restore-statements-filelistonly-transact-sql
RESTORE FILELISTONLY FROM DISK = 'C:\Temp\DB_backup.bak'
GO

You will get results similar to the following:

enter image description here

And then you can use those logical names in the queries:

    -- Script assumes you want MDF and LDF files restored on separate drives. Modify for your scenario
    RESTORE DATABASE DB 
    FROM DISK='C:\Temp\DB_backup.bak'
    WITH REPLACE,
      MOVE 'DB' TO 'E:\MSSQL\Data\DB.mdf', -- "DB" is the mdf logical name from query above
      MOVE 'DB_log' TO 'F:\MSSQL\Logs\DB.ldf'; -- "DB_log" is LDF logical name from query above

More info on RESTORE FILELISTONLY can be found from the SQL Server docs.

Upvotes: 28

Alireza Abdollahnejad
Alireza Abdollahnejad

Reputation: 1037

This causes always due to version incompatibility. follow these steps to solve:

Step 1: Create a database with your preferred name. (In our case AdventureWorks)

Step 2: Write click on the database and click on Tasks >> Restore >> Database…

enter image description here

Step 3: On the restore screen go to third selection of Options. Now select the checkbox “Overwrite the existing database (WITH REPLACE)”

enter image description here

Step 4: Click OK. It should successfully restore the database.

Note: When you restore a database WITH REPLACE it will overwrite the old database.

Upvotes: 55

Prajwal Bhat
Prajwal Bhat

Reputation: 303

This helped me to import the back-up file from the system drive

  1. Create a database with the same name(preferably) as your .bak file database name
  2. Right click the database > Tasks > Restore > Database
  3. Under "Source for restore" select "From Device"
  4. Select the .bak file selecting the path from the system
  5. Select the check box for the database in the list box below
  6. Under "Select a Page" on the right Select "Options"
  7. Select the checkbox labeled "Preserve the replication settings(WITH KEEP_REPLICATION)
  8. Select the checkbox for Overwrite the existing database(WITH REPLACE) Now Go back to the General page and click OK to restore the database...

Upvotes: 1

Ali Sheikhpour
Ali Sheikhpour

Reputation: 11045

Simple 3 steps:

1- Right click on database → Tasks → restore → Database

2- Check Device as source and locate .bak (or zipped .bak) file

3- In the left pane click on options and:

  • check Overwrite the existing database.
  • uncheck Take tail-log backup before restore
  • check Close existing connection to destination database.

Other options are really optional (and important of course)!

Upvotes: 36

Amarnath
Amarnath

Reputation: 8855

Either:

1) Use WITH REPLACE while using the RESTORE command (if using the GUI, it is found under Options -> Overwrite the existing database (WITH REPLACE)).

2) Delete the older database which is conflicting and restore again using RESTORE command.

Check the link for more details.

Upvotes: 239

Ali Sadri
Ali Sadri

Reputation: 1676

instead of click on Restore Database click on Restore File and Filegroups..

thats work on my sql server

Upvotes: 0

akshay5662
akshay5662

Reputation: 291

system.data.sqlclient.sqlerror:The backup set holds a backup of a database other than the existing 'Dbname' database

I have came across to find soultion

  1. Don't Create a database with the same name or different database name !Important.

  2. right click the database | Tasks > Restore > Database

  3. Under "Source for restore" select "From Device"

  4. Select .bak file

  5. Select the check box for the database in the gridview below

  6. To DataBase: "Here You can type New Database Name" (Ex:DemoDB)

  7. Don't select the Existing Database From DropDownlist

  8. Now Click on Ok Button ,it will create a new Databse and restore all data from your .bak file .

you can get help from this link even

Hope it will help to sort out your issue...

Upvotes: 11

HELPER
HELPER

Reputation: 251

Its because the .mdf and .ldf Files from the original Db were locate at maybe c:\programFile\.... and this info is saved in the Backup!

If you create the same DB on a different SQL Server where the installation is on c:\program Files (x86)\ .... you can not restore as usually. You need to relocate the path for .mdf and .ldf Files.

Therefore:

  • Create a empty DB on the new Server

  • Right click on the empty Db > Tasks > Restore > Database > click Device select your .bak Files > Select Db to restore into

  • click on Files at left side > Select "Relocate all Files to Folder"
  • click Options on the left site > click on Overwrite

Done!
Hope it helps!

Upvotes: 25

Aayush Verma
Aayush Verma

Reputation: 221

Same issue with me.The solution for me is:

  1. Right click on the database.
  2. Select tasks, select restore database.
  3. Click options on the left hand side.
  4. Check first option OverWrite the existing database(WITH REPLACE).
  5. Go to General, select source and destination database.
  6. Click OK, that's it

Upvotes: 5

sunil_philip
sunil_philip

Reputation: 9945

I too came across this issue.

Solution :

  • Don't create an empty database and restore the .bak file on to it.
  • Use 'Restore Database' option accessible by right clicking the "Databases" branch of the SQL Server Management Studio and provide the database name while providing the source to restore.
  • Also change the file names at "Files" if the other database still exists. Otherwise you get "The file '...' cannot be overwritten. It is being used by database 'yourFirstDb'".

Upvotes: 992

Priyanka Rawat
Priyanka Rawat

Reputation: 21

In the Options, change the "Restore As" file name to the new database mdf and ldf. It is referencing the source database .mdf and .ldf files.

Upvotes: 2

Elsoni
Elsoni

Reputation: 491

USE [master];
GO

CREATE DATABASE db;
GO

CREATE DATABASE db2;
GO

BACKUP DATABASE db TO DISK = 'c:\temp\db.bak' WITH INIT, COMPRESSION;
GO

RESTORE DATABASE db2
  FROM DISK = 'c:\temp\db.bak'
  WITH REPLACE,
  MOVE 'db' TO 'c:\temp\db2.mdf',
  MOVE 'db_log' TO 'c:\temp\db2.ldf';

Upvotes: 49

mike nelson
mike nelson

Reputation: 22136

I was trying to restore a production database to a staging database on the same server.

The only thing that worked in my case was restore to a new blank database. This worked great, did not try to overwrite production files (which it would if you just restore production backup file to existing staging database). Then delete old database and rename - the files will keep the new temp name but in my case that is fine.

(Or otherwise delete the staging database first and then you can restore to new database with same name as staging database)

Upvotes: 0

immayankmodi
immayankmodi

Reputation: 8580

I had ran into similar problem today. Tried all the above solutions but didn't worked. So posting my solution here.

Don't forget to uncheck Tail-long Backup before restore

Don't forget to uncheck Tail-long Backup before restore

Hope it help others too!

Upvotes: 21

Fabio
Fabio

Reputation: 11

You can restore to a new DB, verify the file name syntax, it ll be in the log file, for the new SQL version ll be a "_log" suffix

ad check the overwrite the existing database flag in option tab

Fabio

Upvotes: 1

Popas
Popas

Reputation: 80

Im sure this problem is related to the files and folders permissions.

Upvotes: 0

Tots Benedicto
Tots Benedicto

Reputation: 111

Before doing anything else, confirm if your backup is Full or Differential. If you're trying to create a new database out of a differential backup, no matter what you do you will encounter the error.

Upvotes: 11

HimalayanCoder
HimalayanCoder

Reputation: 9850

First create a blank database of the same name. Then go for the restore option

Under Options on the left pane don't forget to select

  • Overwrite the existing database
  • Preserve the replication settings

enter image description here

That's it

Upvotes: 111

Rolon
Rolon

Reputation: 41

Some of you have highly over complicated this. I found this to be extremely simple.

1) Create a database with the same name as your .bak file database name !Important

2) right click the database | Tasks > Restore > Database

3) Under "Source for restore" select "From Device"

4) Select .bak file

5) Select the check box for the database in the gridview below

6) Under "Select a Page" on the right Select "Options"

7) Select the checkbox labeled "Preserve the replication settings(WITH KEEP_REPLICATION)

Now Go back to the General page and click OK to restore the database...That is it.

Upvotes: 4

Altaf Patel
Altaf Patel

Reputation: 1410

I got work done through alternate way, using Generate scripts. That did work for me as Backup-Restore didn't help to resolve the issue due to same error.

Upvotes: 2

Angelina Ilieva
Angelina Ilieva

Reputation: 31

I had to create new db on my local for testing & i had a back up from my prod. I created the db first and tried to run the BAK on top of the new db which produced this error for me. I deleted the db and restored it while sourcing the new db name in the restore screen itself. The db was automatically created on restore.

Upvotes: 3

Clint
Clint

Reputation: 6220

I was just trying to solve this issue.

I'd tried everything from running as admin through to the suggestions found here and elsewhere; what solved it for me in the end was to check the "relocate files" option in the Files property tab.

Hopefully this helps somebody else.

Upvotes: 4

Andrew Marais
Andrew Marais

Reputation: 121

Also as important is to make sure that, your database name matches the data base name in the backup you are trying to restore. If it does not match, you will get the same error.

Upvotes: 12

Related Questions