Restoring a database on already existing one

Question

Restoring a database on already existing one

gary1

SSC-Insane

Points: 21152
More actions
January 14, 2010 at 1:14 pm

#219253

Hi,
We have SQL Server 2000 Standard edition with SP3. We have production databases on ServerA and we have restored them to ServerB two months ago
Now, we need to restore the databases from ServerA to ServerB again.
1.I have backed up Mydb on ServerA and copied the BAK file to SevrerB
2.I have restored the Mydb (here Mydb is already exusts on ServerB) and its went fine.
But my question is:
If you restoring a database, which is already exist, you should use REPLACE command or Drop the Mydb and restore.
FOr me, I'm able to restore already existing database 'Mydb' without using replcae command or without dropping it
I'm confused here. Plz calrify me
thanks

Viewing 6 posts - 1 through 5 (of 5 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Is there a slight difference in the database name this time around? Either that, or the replace command was issued without you noticing it, would be my guesses.

- 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

gary1 SSC-Insane Points: 21152 More actions · Answer 2

Is there a slight difference in the database name this time around? Either that, or the replace command was issued without you noticing it, would be my guesses.

NO. Therse is NO difference in database name and I'm NOT using with REPLACE option too.

I did the process one more time as below

On ServerB, 'Mydb' database already exist.

Now, On ServerB,I have connected to Query Analyzer(Start->All Programs->Microsoft SQL Server->Query Analyzer) and ran the below query

USe [Master]

GO

RESTORE DATABASE Mydb

FROM DISK='D:\Backups\Mydb_db_201001140206.BAK'

WITH RECOVERY, STATS = 10,

MOVE 'Mydb' TO 'D:\Program Files\Microsoft SQL Server\MSSQL\Data\Mydb.MDF',

MOVE 'Mydb_log' TO 'D:\Program Files\Microsoft SQL Server\MSSQL\Data\Mydb.LDF'

GO

The database 'Mydb' has been restored succesfully and I verified the date created for Mydb and it's shows the exact time that I have restored.

Thanks

george sibbald SSC Guru Points: 104210 More actions · Answer 3

If the databases name are the same, logical file names are the same and file locations are the same, SQL will presume the backup is for this database and allow you to proceed.

the replace option is there as a safety check to stop you accidently overwriting databases that don't fulfill these criteria. Only use replace if you are sure you want to overwrite an existing database.

see this link from BOL (I know its SQL 2005 but the rules have not changed)

http://msdn.microsoft.com/en-us/library/ms191315(SQL.90).aspx

---------------------------------------------------------------------

gary1 SSC-Insane Points: 21152 More actions · Answer 4

If the databases name are the same, logical file names are the same and file locations are the same, SQL will presume the backup is for this database and allow you to proceed.

Yes, exactly the database name, logical file names and .mdf & .ldf locations are same. So this is the reason right? I got it. Thank you

george sibbald SSC Guru Points: 104210 More actions · Answer 5

george sibbald (1/14/2010)

the replace option is there as a safety check to stop you accidently overwriting databases that don't fulfill these criteria. Only use replace if you are sure you want to overwrite an existing database.

quote]

that wording was not very good, the replace option overrides the safety checks. you most likely still need to use the move option as well to actually overwrite a different database.

---------------------------------------------------------------------