none
RESTORE DATABASE WITH REPLACE

    Question

  • i was in a bit of a rush this morning and had to refresh an existing development database with a copy of live, so i went through the gui's restore database screens, made my selections, then scripted the restore to a new query window. once i verified that i had everything right, i executed the query. right after that, i noticed that i'd forgotten the WITH REPLACE clause, so i was a little surprised to see that the query was still executing. i refreshed the database list and sure enough, the database was being refreshed.

     

    if i tried to restore a copy of our live database onto our development database without the REPLACE clause, it always failed. this time though, it worked. i recently upgraded the server to SP2 and hadn't forgotten the REPLACE clause since then until now. is this a new SP2 "feature"?

     

    SQL05, SP2

    W2k3 Server x64, SP1

     

    from BOL:

    REPLACE specifies that SQL Server should create the specified database and its related files even if another database already exists with the same name. In such a case, the existing database is deleted. When the REPLACE option is not specified, a safety check occurs. This prevents overwriting a different database by accident. The safety check ensures that the RESTORE DATABASE statement does not restore the database to the current server if the following conditions both exist:

    • The database named in the RESTORE statement already exists on the current server, and (note: this condition is TRUE on our server)

    • The database name is different from the database name recorded in the backup set. (this condition is also TRUE on our server)

    REPLACE also allows RESTORE to overwrite an existing file that cannot be verified as belonging to the database being restored. Normally, RESTORE refuses to overwrite pre-existing files. WITH REPLACE can also be used in the same way for the RESTORE LOG option.

    Monday, May 12, 2008 2:25 PM

All replies

  •  

    Lenny,

     

    Does the database backup have the exact same name as the production database?  If so, the file header already matched and SQL probably saw this as a restore of the same database.  Also, you did not need to specify the replace option because there were no transactions in the log that had not been backed up.  When transactions exist in the log that have not been backed up, you must either perform a tail log backup or specify the replace option.

    Monday, May 12, 2008 2:46 PM
    Moderator
  • they have different names and reside on the same server, and they're both HEAVILY used, so i'm almost positive there would have been recent transactions.
    Monday, May 12, 2008 3:01 PM
  • Lenny,

    did you ever get this issue resolved?  If so, can you please post the solution.  Posting the solution benefits everyone who comes across this thread, which will probably be users with the same or a similar issue.

    Thanks,
    Adam
    Saturday, May 09, 2009 4:02 AM
    Moderator
  • Hi all,

    I recently discovered the same issue. I did a restore of a database DB1, made on server S1 to an existing database DB2 on testserver S2. I used the GUI to restore the database from a file and forgot to check the "Overwrite the existing database". Instead of a warning about overwriting an existing database, the RESTORE did proceed wothout any warning.

    SQL2005 SP3 (9.0.4053) on Windows 2003 x86 SP2

    IMHO this is an incorrect behaviour of the RESTORE

    Regards,
    Adri
    Programmers do it Bit by Bit
    Tuesday, November 24, 2009 9:22 AM
  • Hi,

    I know about the "mouseclicks in bit of a rush this morning", however, I saw it before and this time a intentionally did not check the "Overwrite the existing database" option to see what happens. Also, when the RESTORE was running, a DBCC INPUTBUFFER(spid) gave me the complete RESTORE command and there was no REPLACE

    Regards,
    Adri
    Programmers do it Bit by Bit
    Tuesday, November 24, 2009 10:12 AM