Install a test database on the same sql server?

[360degreehosting]

Hi,

I need some help to install a test database on the same SQL server. I need to bring the data along from the live database as well. I did it once successfully but I can remember and we have too many people using the db now for me to make a mistake.

I have tried to create a new db and Restore a backup of the live db into the new db but when i look at the logical and physical names in the properties it's referencing the live db info not the one i'm in trying to restore to. When I try to change the logical/physical names i get errors that are over my head.

Can anyone post a simple step-by-step process to taking the live db and making an exact copy in a test db on the same server please?

Thank you,
Steve

 

[mutley1]

Steve,

What errors do you get and what version of SQL are you using?

If it's 2000 then try this.

1. Open Enterprise Manager and expand down the server until you see the Databases folder.
2. Left click to highlight it then right click on it and select All tasks then Restore database.
3. In "Restore database as" enter a name for your new test database (obviously one that does not already exist).
4. In the restore section below with 3 radio buttons, click on "from device" (assuming your backup is on that server).
5. If there is anything in the box that comes up, click on "select device", then "remove", then "add" and browse through to find your backup.
6.Once selected, click OK twice so you are back at the main restore window with 2 tabs (general and options). Click on the options tab and rename the "Move to physical file name" to another MDF / LDF name (e.g. if it is d:\blah\blah\database_data.MDF call it d:\blah\blah\database_test_data.mdf). Do this for the data and log files ensuring whatever physical file name you use does not already exist.
7. If you are not applying transaction logs after the make sure the radio button "Leave database operational....etc....." is checked.


HTH,

M.