Restoring backup to new database?

[jsteph]

Hi all,
I have a backup of a database and I want to 'restore' it but only as a temporary test db to get some history that had been deleted from the original. The recovery mode is 'simple', so this is a full backup on a .bkf file. (Sql server 2005)

My worry is that I'll forget to tick some seemingly obscure checkbox and this backup will overwrite the original, live database.

I've created a new, dummy database, for purposes here I'll call it "dbtest". The live one is "dblive".

When I go into dbtest and do 'Restore', and choose the backup file for dblive, the first thing I notice is that it want's to place the .mdf files (there are about a dozen) in the original, dblive's folder. It seems to me that this would be a problem.

Yes I can go into the options and change the destination to point to the folder I created for the dummy dbtest .mdf files...but what else do I need to watch out for? Every move I make it seems sql-server want's to overwrite the original. Do I need to maintain the original's filenames? I'm assuming (!) that I can change the folder so I don't overwrite the live but what about the names, either logical or physical?

Even the option "Overwrite Existing Database" is ambiguous: *Which* existing database? The dummy test one I created or the *real* original that's coming from the .bkf file?

Can anyone clarify exactly what I need to do to place a copy of the live db on the same box but not have it overwrite the live db in any way? Thanks,
--Jim

 

[JohnBates]

Jim,

Using the GUI tool, as you are doing is probably the easist way.

The box 'Restore As' is where you can enter the new db name - dbtest. If you delete your temp dbtest, it will create it. I'm not sure what happens if it already exists.

But you are wise to be cautious - I overwrote a production db once myself.

The above method will work for you.

John

 

[jsteph]

Thanks very much...I ended up testing a script on a non-production db and it worked, so I forged ahead and all was fine,
--Jim

 

[mrdenny]

If the database already exists when you restore over it, the database is dropped then restored.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[jsteph]

mrdenny,
Yes...I created a dummy db for that purpose, because it was ambiguous as to what would happen if the db in the 'restore to' field did not exist--it was trying to place the files in the actual location where the production db were.

But when I went off the Options tab after putting in new file locations, and then made a change in the General tab--just prior to going ahead with the restore I double-checked the Options tab--it had put back the original file locations(!?). That was a fright, so that's why I did the script so I could see with my own eyes where the 'move' statement was actually putting the files.
--Jim