SQL Server 2017 -Database Restore, Overwrite without REPLACE

Question

Is this just me, or is it a wider issue. -- Turns out it is just me.

In SQL Server 2017 with CU15 (14.0.3162.1) you can restore a database over an existing database without the REPLACE option being used. The 'Overwrite the existing database (WITH REPLACE)' option is now irreverent and is ignored.

Confirmed with SSMS 17.8.1 & 17.9.1, both via the GUI and New Query window.

The backups being restored were from SQL Server 2014 (12.0.6024.0) and local backups (SQL Server 2017) exhibit the same behaviour.

So

RESTORE DATABASE [TMP1] FROM  DISK = N'F:\tmp\TMP1.bak' WITH  FILE = 1,  NOUNLOAD,  STATS = 5

Is the same as

RESTORE DATABASE [TMP1] FROM  DISK = N'F:\tmp\TMP1.bak' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 5

David Dubois · Accepted Answer · 2019-07-30 14:12:01Z

1

The REPLACE option overrides several important safety checks that restore normally performs.

https://learn.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql?view=sql-server-2017

When doing a RESTORE, SQL Server checks for certain conditions that may lead to problems. As long as none of those conditions are applicable, it will perform the backup, even if that means overwriting an existing database. If any of the safety checks fail, it will give an error message. By adding the REPLACE option, you are telling SQL Server that it's okay to overwrite the files regardless of the safety checks.

------------ EDIT: ---------------

How does SQL Server determine whether the database being restored is the same as the database being overwritten? I did not know the answer when I started writing this, so I've done some research. For this purpose SQL Server uses the FamilyGUID. Every time you create a new database it is tattooed with a FamilyGUID. When a database is backed up, the FamilyGUID is carried along. When it comes time to restore, SQL Server compares the FamilyGUID of the backup to the database being overwritten. If they are the same, then there is no problem. If they are different, you need to use REPLACE.

Perhaps in your case, the FamilyGUIDs are the same.

To find the FamilyGUID of the backup, use:

RESTORE headeronly FROM DISK = N'Q:\MyBackup.bak'

To find the FamilyGUID of the database being overwritten, you can use:

SELECT DB_NAME(database_id) as DatabaseName, database_guid, family_guid
FROM master.sys.database_recovery_status

Are they the same?

edited Jul 30, 2019 at 14:12

answered Jul 30, 2019 at 12:29

David Dubois

3,9423 gold badges21 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

R_M Over a year ago

The problem is that without REPLACE present the databases are still being overwritten. Restore normally prevents accidentally overwriting a database with a different database.

David Browne - Microsoft Over a year ago

But it doesn't, and never has, prevented replacing an existing database with an earlier version of the same database, so long as there are no transactions in the transaction log that haven't either been backed-up (FULL recovery), or truncated on checkpoint (SIMPLE recovery).

R_M Over a year ago

The FamilyGUID must have been the same, it wasn't behaviour I was expecting. Good the learn something that I thought I already understood.

Collectives™ on Stack Overflow

SQL Server 2017 -Database Restore, Overwrite without REPLACE

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related