Backup & Restore

Question

Backup & Restore

Whizkid

Right there with Babe

Points: 783
More actions
September 20, 2019 at 7:22 am

#3681706
I have DB in the production server in the name "LMS_App" and took the backup of this DB. I would like to resore this DB in our development server. While restoring in our development server with different DB name, I'm getting an error and cause of this error is that the LDF & MDF file has exists in the development server in the same name of "LMS_App". Hence, while restoring, I've changed the location of MDF & LDF file in the other name(LMS_App_Prod) and it donesn't helping me to resolve this issue.
Can you please help me to restore this DB in the same server with different DB name?
- This topic was modified 5 years, 2 months ago by Whizkid. Reason: typo

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99130 More actions · Answer 1

What's the SQL you're running to restore the Backup? Are you using WITH MOVE ... TO syntax (as shown in the documentation here)?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Whizkid Right there with Babe Points: 783 More actions · Answer 2

I'm able to achieve through below SQL Query. How do I do this in SQL Server Management Studio Wizard?

--Create TestDB1
CREATE DATABASE TestDB1;
GO

--Create TestDB2
CREATE DATABASE TestDB2;
GO

--Backup of TestDB1
BACKUP DATABASE TestDB1 TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\TestDB1.bak' WITH INIT, COMPRESSION;
GO

--Restore TestDB1 backup into TestDB2
RESTORE DATABASE TestDB2
  FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\TestDB1.bak'
  WITH REPLACE,
  MOVE 'TestDB1' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TestDB2.mdf',
  MOVE 'TestDB1_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TestDB2.ldf';

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 3

I think it's the second tab in the restore GUI- there's a box you can tick.

Whizkid Right there with Babe Points: 783 More actions · Answer 4

Can you please explain the GUI box (caption) you are referring in second tab?

This reply was modified 5 years, 2 months ago by Whizkid. Reason: typo

Steve Jones - SSC Editor SSC Guru Points: 736183 More actions · Answer 5

In the restore dialog, there are selections on the left side, one of which is for the Files tab. This is the second one. There is a "relocate" checkbox on this tab.

2019-09-23 09_17_30-Restore Database - Northwind

Whizkid Right there with Babe Points: 783 More actions · Answer 6

Can't we restore the DB using "Tasks->Restore->Files and Filegroups" option? I was following below steps.

1) Created a new database

2) Navigate to Tasks->Restore->Files and Filegroups

3) In “General” tab,

a. I gave the DB name which is created in Step 1 in “To Database” dropdown under “Destination to restore” section

b. Click “From Device” radio button and select location of BAK file under “Source for restore” section

c. Select “Restore” checkbox ” in “Select the backups sets to restore” section

4) In “Options” tab,

a. Select “Overwrite the existing database (WITH REPLACE)” checkbox under “Restore Options” section

b. Change the location of MDF & LDF file of the databse which is created in Step 1 under “Restore As ”

4) Click "OK" button and ending up with below error.

I'm ending up with an error "The backup set holds a backup of database other than the existing "TestDB9" database.

RESTORE DATABASE is terminating abnormally(Microsoft SQL Server, Error: 3154)"

Note:

When I select "Tasks->Restore->Files and Filegroups" option, I'll be getting the dialog box with 2 options like "General", "Options" (ref. below screenshot) where as in "Tasks->Restore->Database" option, we do have an additional option called "Files".

Untitled