# What would happen if I didn't change the destination db file name during restore?

Our production database file name is m2mdata01.mdf and we have regularly a daily back up.
Besides, we have a sandbox db. The sandbox db file name is m2mdata50.mdf.
Just today, when I did a restore to the Sandbox from the yesterday's production db backup file I forgot to change the "Restore database files as" from m2mdata01.mbf to m2mdata50.mbf (see the screen shot #1). After the restore was done, I was kind of panic since I thought the subject mistake will cause the live production db to be overwritten by the yesterday's production db file. But when I went to the folder of D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA
I didn't see the production db file got overwritten as its date stamped two days ago. (please see the screen shot #2) Why is that? Did the production db file get overwritten or not?
To be honest, I'm very confused. Can you help?
###### Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
I don't see your screen shots; but let me try to help based on what you said.
Did you use the GUI to restore the database or did you write the T-SQL yourself?

If you used the GUI and right clicked on the m2mdata50 (Sandbox) database and restored the m2mdata01 backup (Prod) database to it, then the physical file name would be what ever that sandbox database name is.  So it would restore the contents of m2mdata01 into m2mdata50.

That said, your internal database name can be different than your physical database file name.
Hope this makes sense.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Senior DeveloperCommented:
Please check where your database files are located:

CREATE TABLE #database_files ( database_name SYSNAME, logical_name SYSNAME, physical_name NVARCHAR(MAX) );

EXECUTE sp_MSforeachdb 'INSERT INTO #database_files SELECT ''?'', name, physical_name FROM ?.sys.database_files;';

SELECT	*
FROM	#database_files
ORDER BY 1, 2, 3;

DROP TABLE #database_files;

Senior DBACommented:
SQL typically detects that the file is in use and won't overwrite it.  But you can override that with certain options.

I'd strongly urge you to use RESTORE commands to do restores rather than the GUI.  Sooner or later, the GUI will cause you a major problem because of something like that.

You will need a separate MOVE statement for every file in the db.  But once you get it coded it will typically stay the same, and thus you will do consistent restores.  And if something odd does come up, you will also be able to go back and verify what the specific command that actually ran was, unlike with the gui.

RESTORE DATABASE new_db_from
FROM DISK = 'x:\full\path\to\backup\file\old_db.bak'
WITH MOVE 'old_db' TO 'd:\full\path\to\data\file\new_db.mdf',
MOVE 'old_db_log' TO 'e:\full\path\to\log\file\new_db_log.ldf'
--,MOVE 'secondary_file_name' TO '...', MOVE '
Author Commented:
Here is with the two screen shots:
m2mdata50.docx
Author Commented:
I used GUI
Senior DBACommented:
Yes, you used the gui for your last restore.  It's likely that SQL didn't do the restore because the file was already in use; sometimes the gui doesn't accurately relay such error messages (sometimes it will just "hang" after an error and it seems that nothing is happening).

That's why I suggested using a RESTORE command instead.  It's safer and easier to determine success or failure of the restore.
Commented:
It looks like you restored, or attempted to restore, your production database with that backup.
Under the General Tab did you select your Sandbox database as the destination?

I would also agree with ScottPletcher as well.  And he is quite correct that you should get used to using SQL Coding to do this and not rely on the GUI.
Author Commented:
Thunder724,

If you used the GUI and right clicked on the m2mdata50 (Sandbox) database and restored the m2mdata01 backup (Prod) database to it, then the physical file name would be what ever that sandbox database name is.  So it would restore the contents of m2mdata01 into m2mdata50.
I did what exactly you described. So the restore got into the production db (m2mdata01) for sure but question for physical file -- did the restore actually went into the file m2mdata50.mdf instead of m2mdata01.mdf in the DATA folder? Please help.
Senior DBACommented:
Probably not.  But when you use the gui, you can't tell for sure until you check the SQL Server error log and/or the msdb.dbo.restorehistory table.
Commented:
When you ran this did it complete successfully?
Can you or someone else identify any data loss?

Based on the file modification dates I would lean towards no data loss, and you did not restore over your production.
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.