title: Restore SQL Database with Replace option
body: <p>I am trying to understand the REPLACE option from various sources and have not clarified myself. I am using SQL Server 2014 version.</p>
  
  <p>What is the difference between below 2 queries? Both are completing without any error. With and Without Replace.</p>
  
  <pre><code>USE [master]
  RESTORE DATABASE [Test] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\MyDatabase.bak'
  WITH FILE = 3,  NOUNLOAD,  STATS = 5
  GO
  </code></pre>
  
  <p>Vs</p>
  
  <pre><code>USE [master]
  RESTORE DATABASE [Test] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\MyDatabase.bak'
  WITH REPLACE, FILE = 3,  NOUNLOAD,  STATS = 5
  GO
  </code></pre>
  
  <p>When I try to Replace with the .mdf and .ldf of an existing database (Test2), I get an error.  My understanding so far is that I can restore the backup of [Test1] db with the name [Test2] (This already exists in my server and Test1 DB also exists).</p>
  
  <p>I got the statement for REPLACE "Restoring over an existing database with a backup taken of another database." from one of Microsoft Link.</p>
  
  <pre><code>USE [master]
  RESTORE DATABASE [Test2] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\MyDatabase.bak'
  WITH REPLACE, FILE = 3,  NOUNLOAD,  STATS = 5
  GO
  </code></pre>
  
  <p>Error:</p>
  
  <pre><code>Msg 1834, Level 16, State 1, Line 2
  The file 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\test1.mdf' cannot be overwritten.  It is being used by database 'test1'.
  Msg 3156, Level 16, State 4, Line 2
   'test1' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\test1.mdf'. Use WITH MOVE to identify a valid location for the file.
  Msg 1834, Level 16, State 1, Line 2
  </code></pre>
  
  <p>What is the purpose of the REPLACE option and what it replaces behind the scene. Can any one please explain with any example?</p>