Replacing a Database

If you’re restoring a database to a new server, use the REPLACE option. The REPLACE option removes the normal restore safety check and lets you overwrite an existing database, even one with a different name from the database that you’re restoring. For instance, suppose that Database D was backed up on Server A and will be restored onto Server B. First, create a “placeholder database” on Server B—the size and name are irrelevant. Next, restore the backup of Database D using the REPLACE option on top of this placeholder database. If you’re restoring a database in place—that is, Database D on Server A was backed up and will be restored back onto Server A, then you don’t need to use the REPLACE option. By default, the restore operation has built-in safety checks; for instance, you can’t restore over an existing database with a backup of another database. Nor can you restore a database that’s been using the full or bulk-logged recovery model if no tail-log backup exists.

If you need to restore a database but you haven’t been able to successfully take a tail-log backup (e.g., if the transaction log file was corrupted), then using the REPLACE option might be your only option for a successful restore. Or if you’ve backed up the production version of a database and you want to restore it on your test and development system, then you must use the REPLACE option. Even though the database name is the same on the production and development instance, they’re different databases to SQL Server.

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.