Solved

Restoring a database backup into another database

Posted on 2014-10-08
5
136 Views
Last Modified: 2014-10-14
Hello All,

I have an application pointing into 2 MS SQL 2005 databases, one called PRODUCTION and the other called TEST. I am attempting to replicate all information from the production database to the test database so they both have the exact same data in them.
To accomplish this, I have created a backup of the PRODUCTION database and I am now attempting to restore this backup into the TEST database using the restore utility in Management Studio. After selecting the TEST database --> right click --> Tasks --> Restore and choosing the backup that I made of my production database, in the "Restore the database as" section of the Options screen,  it appears that the restore will rename the TEST database to the name of my production database (mdf and ldf files) and I dont want this to happen, I want to maintain the original name of the database since I am sure what could happen with my application if the TEST database is renamed.

What should I do next?
0
Comment
Question by:LuiLui77
5 Comments
 
LVL 29

Accepted Solution

by:
Paul Jackson earned 250 total points
ID: 40369889
You should be able to update the file paths on the options screen to reflect the name of your test database and then check the checkbox to overwrite the database namely your test database.
0
 
LVL 25

Expert Comment

by:Mohammed Khawaja
ID: 40369910
When you are choosing restore, have you click on the option to overwrite.  Also note that when you click on advanced, you have the option to rename the files.  When you select overwrite, it will overwrite the existing MDF and LDF with the data from the restored database.
0
 
LVL 10

Expert Comment

by:HuaMinChen
ID: 40369947
Hi,
You can also copy the relevant .mdf and .ldf files to the folder like

C:\Program Files\Microsoft SQL Server\MSSQL11.SS2002\MSSQL\DATA

within the server and further attach the DB to the new server.
0
 
LVL 46

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 250 total points
ID: 40370113
Since it's a production database I won't recommend to copy the .mdf and .ldf files because that means you'll need to stop the database during the operation.
As was said in the two first comments you can check the "overwrite the existing database" and then change the filenames and/or they location.
Here is a step-by-step on how to do it.
0
 

Author Closing Comment

by:LuiLui77
ID: 40379865
Thank you Paul, it did work by overwriting and changing the paths to reflect the test Databases.

Thanks Vitor for the clarification.

Thank you all fr you input!
0

Featured Post

Superior storage. Superior surveillance.

WD Purple drives are built for 24/7, always-on, high-definition security systems. With support for up to 8 hard drives and 32 cameras, WD Purple drives are optimized for surveillance.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Are you looking to recover an email message or a contact you just deleted mistakenly? Or you are searching for a contact that you erased from your MS Outlook ‘Contacts’ folder and now realized that it was important.
This tutorial will walk an individual through configuring a drive on a Windows Server 2008 to perform shadow copies in order to quickly recover deleted files and folders. Click on Start and then select Computer to view the available drives on the se…
This tutorial will walk an individual through the steps necessary to install and configure the Windows Server Backup Utility. Directly connect an external storage device such as a USB drive, or CD\DVD burner: If the device is a USB drive, ensure i…

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now