Replace existing Database? JFDI!

Working with Shawn McGehee on his forthcoming SQL Server Backup and Restore book, I encountered an example of the way in which Microsoft sometimes seems to set little "traps" for the novice DBA, with the way it names certain options.

I confess that I initially assumed that the WITH REPLACE option of the RESTORE DATABASE command was a simple, necessary indication of the desire to restore over the top of an existing database. So, the following command…

  RESTORE DATABASE TONY
  FROM <'...\tony.bak'>
  WITH REPLACE

...meant, "take the tony.bak backup file and restore it to a database called Tony, and if such a database already exists, replace it with the restored copy". Seems logical enough, right? And this is…sort of…what it means, but it's an explanation that misses a lot of the underlying implications of the use of REPLACE – and this could get the unwary into hot water.

The problem is that to SQL Server, WITH REPLACE is what I'd term a "JFDI" command. It says to SQL Server "When performing this restore operation, don't first check to see if a tail log backup has been done; don't check that the backup matches the target database...Just F****** Do It!"

So, let's say just for the sake of an example that you're restoring over an existing database and moving the data and log files to a new location. However, you've accidentally picked the backup file for a similarly-named but entirely different database. If you omitted the REPLACE option, you'd get a nice warning that the database in the backup doesn't match the one you're restoring over. With it, you won't; you'll just inadvertently overwrite one database with another.

Likewise, when restoring over the top of a FULL (or BULK_LOGGED) recovery model database, without use of REPLACE you might get a nice warning if you've not first performed a tail log backup; if you specify REPLACE then you won't. This is fine if you're 100% sure that you don't want to back up the log first, but could have possibly-dire consequences otherwise.

Of course, I now know that REPLACE is not required in order to replace an existing database and in fact that it's use should be avoided where possible. I'm also not suggesting that these warnings about the use of REPLACE aren't out there; they are (http://msdn.microsoft.com/en-us/library/ms191315.aspx). However, it does seem to me that the naming leads the unwary into a false sense of security. Wouldn't a name such as FORCE_OVERWRITE give a better indication than REPLACE of the true intent of the option? There are times when using applications that endless "warning" messages are annoyance, but when dealing with backup and restore they are a necessary safeguard, and any command that bypasses such safeguards should be named so as to make its "JFDI" nature clear.

I'd love to hear what you think, and if you've any other examples where the naming seems to hide the true nature of a command or option. Also, if you are interested in the Backup and Restore book (shameless plug!) do follow the link and sign up, and we'll let you know when it's ready to download or buy.

Cheers,

Tony.