Restore SQL Database with Replace option
<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>