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?
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?
Eyal
take full backup of the database and restore from it
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.
Error message ?
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
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
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
< and I get a failure error >
please clarify what exact error you get ...
please clarify what exact error you get ...
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.
(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.
ASKER
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
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
ASKER
I have made it this far, will this work to restore TEST(TEST) from PROD (MSG)?
sqlbkup.PNG
sqlbkup.PNG
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
ASKER
I was going to select overwrite existing database.
ASKER
Both databases are on the same server, restoring as mentioned does not work.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>Both databases are on the same server, restoring as mentioned does not work. <<
And "does not work" tells us nothing.
And "does not work" tells us nothing.
ASKER
This worked I also performed the operation form PROD to TEST and not TEST from PROD, I also overwrote existing DB on TEST