• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 249
  • Last Modified:

How can i restore a copy of PROD db to TEST db SQL05

I am trying to restore a copy of our production database to TEST environemnt to perform some final testing changes.
I selected to restore then selected to DV TEST from PROD and I get a failure error, I have also tried using the *.bak file that is created daily, any thoughts?
0
manelson05
Asked:
manelson05
1 Solution
 
EyalCommented:
take full backup of the database and restore from it
0
 
Shaun KlineLead Software EngineerCommented:
When you back up a database, the backup contains information about where the data and log files reside for that database. If you are restoring to an existing database, you will need to overwrite the existing database files, as SQL Server will not do this by default. In SSMS, this is done on the options tab on the Restore Database dialog box. If you are creating a new database using the backup, you will need to specify the location of the data and log files as they will default to the location of the database that was backed up. Be very careful if you are using the same SQL Server for both Production and Test (not recommended), as it is very easy to accidentally overwrite a Production database using the Overwrite flag.
0
 
Rajkumar GsSoftware EngineerCommented:
Error message ?
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
Rajkumar GsSoftware EngineerCommented:
I have the same thing that Shaun already explained. Hope that is your issue.

1. Right-Click database properties and copy the location with file name of log and data files into a notepad.
2. Go with restore. Go to options tab, replace the copied location with file name and check over-write.

always I am doing in this manner, during restore.

Raj
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
< and I get a failure error >
please clarify what exact error you get ...
0
 
VipulKadiaCommented:
Do following steps :
(1). Take full backup of your PROD db.
(2). Copy that backup file to LOCAL drive of TEST server.
(3). Now on TEST server, from SQL management studio, select RESTORE option and select the file you pasted on TEST server's drive.
0
 
manelson05Author Commented:
Test and Prod are on the same server, I know this is a bad thing to do, I did not set this up, I inherited this mess.
Production db is called MSG, TEST is TEST.
I backed up MSG then went to TEST and tried to restore FROM MSG, TO TEST and this is the error I received.


SQL.PNG
0
 
manelson05Author Commented:
I have made it this far, will this work to restore TEST(TEST) from PROD (MSG)?
sqlbkup.PNG
0
 
Rajkumar GsSoftware EngineerCommented:

Did you try these suggestions -

Step #1 - VipulKadia: suggestion - http:#36978654 - to backup the database.
Step #2 - Restore database - follow the suggestion posted here - http:#36975930 / http:#36975871

Raj
0
 
manelson05Author Commented:
I was going to select overwrite existing database.
0
 
manelson05Author Commented:
Both databases are on the same server, restoring as mentioned does not work.
0
 
Rajkumar GsSoftware EngineerCommented:
I am also using production and testing database at the same server. Also restored production over testing without any issues. I think, you are missing some steps that we mentioned or not clear about that.

Please make sure you have tried in this way.
1. Take a full backup of your testing database. Please make sure it is not incremental, should be full backup.
 Backup database2. Once backup is completed, right-Click production database and note down the location with filename for log and data files to notepad.
 Restore - 13. Now close properties window and go for restore. Select 'From device' option and browse to backup file.
 Restore - 2
4. Go to 'Options' tab in Restore window and check 'Overwrite existing database' option
5. In same 'Options' tab, from the notepad, copy and paste the location with filename to appropriate log and data. And click on restore button.

If you didn't try this way, please try and let me know
Raj
0
 
Anthony PerkinsCommented:
>>Both databases are on the same server, restoring as mentioned does not work. <<
And "does not work" tells us nothing.
0
 
manelson05Author Commented:
This worked I also performed the operation form PROD to TEST and not TEST from PROD, I also overwrote existing DB on TEST
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now