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
SE 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
...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
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.