3

What is the difference when using or not using "With Replace" when restoring database. I know that With Replace, I would get identical database as backup file even log file would be replaced/overwritten and current content / transactions would be broken.

But, what is actual difference; I read somewhere that some configuration for database like recovery model is stored in Master database; With Replace - it can be overwritten! What else? I cannot find much on what is the use of With Replace? Why it is used a lot, most of the developer restore databases using "With Replace".

I want to clear my concept of restore database.

Thanks

0

For a database using the full or bulk-logged recovery model, in most cases you must back up the tail of the log before restoring the database. Restoring a database without first backing up the tail of the log results in an error, unless the RESTORE DATABASE statement contains either the WITH REPLACE or the WITH STOPAT clause, which must specify a time or transaction that occurred after the end of the data backup. For more information about tail-log backups, see Tail-Log Backups (SQL Server).

MSDN Source on Replace

A tail-log backup captures any log records that have not yet been backed up (the tail of the log) to prevent work loss and to keep the log chain intact. Before you can recover a SQL Server database to its latest point in time, you must back up the tail of its transaction log. The tail-log backup will be the last backup of interest in the recovery plan for the database.

MSDN Source on Tail-Log Backup

What is the difference when using or not using "With Replace" when restoring database.

If you have a database called DatabaseA and you have another database called DatabaseB, let's say you want A to be a copy of B. You would take a full backup of B and then you can restore over A with B. It would have an issue without replace. In one circumstance you may run into it saying: The backup set holds a backup of a database other than the existing 'DatabaseA' database. In another circumstance you may run into it stating you need the tail-log. More on those scenarios here.

I know that With Replace, I would get identical database as backup file even log file would be replaced/overwritten and current content / transactions would be broken.

This is correct, you are overwriting the existing database with the backup file. The chain of transactions is now based on the backup restored.

But, what is actual difference; I read somewhere that some configuration for database like recovery model is stored in Master database; With Replace - it can be overwritten! What else?

Basically, if you did a backup restore of DatabaseB over DatabaseA, it becomes DatabaseB but named as DatabaseA.

I cannot find much on what is the use of With Replace? Why it is used a lot, most of the developer restore databases using "With Replace".

It sounds exactly like it is, it is used when you want to replace the existing DB with the DB restored from the backup.

  • Thanks for good answer but I am still not clear. For example; If I have DatabaseB with its backup. The current size of DatabaseB is 10MB and backup file is 20GB. In this case, what is right choice and why? Should I Overwrite with Replace or not? – newbeedeveloper Aug 28 '18 at 21:01
  • This is all about intent. Imagine your backup is a snapshot in time. At the moment the backup is taken with the data inside it, that is the state of the database in that backup. If your intent is to make the current database match what the database was in the backup, then you would replace the existing database with the database from the backup. Anything that was in the database you are restoring over is gone and replaced with whatever was in the backup. If that's your intent, you should overwrite it. If not, then you should restore as a differently named database. – Shaulinator Aug 28 '18 at 21:05
  • I have a server with many databases that are exact replicas that come from a "perfect" database. Developers have their own instance to develop off of and can switch to shared databases to test changes. Sometimes I need to fix those environments because someone does something that blows it up. In those cases, it's easier to REPLACE the existing testing database with a copy from the "perfect" database. That is one example of when I'd use it. – Shaulinator Aug 28 '18 at 21:13
  • Likewise, maybe I accidentally dropped a table but I need to keep my current database in tact. I would restore a copy of the database with a different name, and then find the table that was dropped inside the restored database. I would re-create the table from the restored database into the main database, and import the records from the backup. I could then drop the newly named / restored database because I retrieved the one table I needed from it. – Shaulinator Aug 28 '18 at 21:15
  • Thanks sir. Exactly, as you said (If your intent is to make the current database match what the database was in the backup, then..), so what would be different in database if I do not use "With Replace". I tried 3 things; (1)I inserted dummy rows, I tried restore without "With Replace", my dummy rows were removed after restore. – newbeedeveloper Aug 28 '18 at 21:19
2

Using WITH REPLACE allows you to overwrite the DB without backing up the tail log, which means you can lose commited work.

Additionally, if you use WITH REPLACE you can, and will, overwrite whatever database you are restoring on top of. This means you could restore a database over another database that is used for something completely different, which can be quite dangerous if you aren't careful.

From the docs:

REPLACE should be used rarely and only after careful consideration. Restore normally prevents accidentally overwriting a database with a different database. If the database specified in a RESTORE statement already exists on the current server and the specified database family GUID differs from the database family GUID recorded in the backup set, the database is not restored. This is an important safeguard.

For example, you backup Server1.DB1 and then restore it to Server2.DB2 but DB2 in this case is NOT an old restore from DB1. It's actually a completely different database used for something completely different. WITH REPLACE would replace DB2 with DB1 despite them not being the "same" database.

  • You example ( you backup Server1.DB1 and then restore it to Server2.DB2) is nice, I have already read it but I am sure there is other difference too; I want to know what is the other benefit/advantage of using With Replace? If we just take only this example then we should never use "With Replace" when restoring from same database's backup copy – newbeedeveloper Aug 28 '18 at 21:04
  • The advantage is you don't have to backup the tail log, but this comes at the price of you losing committed data as I stated above. It also allows you restore the DB to with a name of a DB that already exists without having to drop the database first, hence the replace – scsimon Aug 28 '18 at 21:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.