Overwrite option when restoring

Question

Overwrite option when restoring

Kwisatz78

SSChampion

Points: 12478
More actions
January 6, 2011 at 9:21 am

#144529

Hi all
I have a query about the overwrite option when restoring DBs. I have 2 instances on my server, they both have databases named Test stored in different file locations.
I have transaction log and full backups for the database on instance 1. I took the latest full backup and restored it to my 2nd instance, and checked the overwrite option, however I forgot to change the file path. The database then restored.
I realised my mistake and so assumed that my original Test database files would have been overwritten, but when I investigate it seemed that this had not actually happened and all I had done was to replace the database correctly on my 2nd instance.
Is this expected behaviour, is the reason the files didn't overwrite because I was trying to overwrite a database in another instance?
Thanks

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

If it can't find the expected file paths, it will usually put them in the default location. In that case, it won't overwrite ones in a different location.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Kwisatz78 SSChampion Points: 12478 More actions · Answer 2

well the databases reside on the same server. Basically the file paths were:

E:\Data\db.mdf and E:\Test\Data\db.mdf

There is no permission problems that I can see so does your theory still stand? Also whats the default location, do you mean the path of the existing database thats being overwritten? There is no locations set in the Database Settings of the server

Kwisatz78 SSChampion Points: 12478 More actions · Answer 3

Ok well for anyone interested it I have done some more local testing and it seems that if you are trying to overwrite a database using a backup of a database taken from another file location\instance then SQL seems to do a check to say:

Do the specified files in the backup have same path as the database being overwritten, if so overwrite them else replace the files of the database with the ones in the backup (but keeping original file names)