Have you ever tried to restore over an existing database only to receive the following error message?
Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing 'AdventureWorks2012' database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
We could easily avoid this error by using the WITH REPLACE option in the RESTORE DATABASE command, but we want to know why this error occurs and how SQL Server knows they are not from the same family. The answer is the database_guid.
The database_guid is a GUID that is unique to every database; even across servers. If you restore a backup to another server, the restored database will get a new database_guid. You can view this value for every database by looking in the catalog view
sys.database_recovery_status.
SELECT DB_NAME(database_id), database_guid
FROM master.sys.database_recovery_status;
If the database_guid is different for every database regardless of which SQL Server it’s on, then how does the restore still know the backup and database are from two different families? The answer is the family_guid.
The family_guid is the original database_guid when the database was first created, and does not change for the life of that database. When you issue a RESTORE DATABASE command, SQL Server will compare the family_guid to the database_guid if you are attempting to restore over an existing database. When those numbers don’t match, you will receive that error. You can get the family_guid from the same catalog view.
SELECT DB_NAME(database_id), database_guid, family_guid
FROM master.sys.database_recovery_status
You can use the following code to pull the family_guid from the backup and then compare it to the database you are trying to overwrite.
-- Build a temp table to store the backup file header information.
IF OBJECT_ID('tempdb..#BackupHeader') IS NOT NULL
DROP TABLE #BackupHeader;
GO
CREATE TABLE #BackupHeader (
BackupName nvarchar(128)
,BackupDescription nvarchar(255)
,BackupType smallint
,ExpirationDate datetime
,Compressed bit
,Position smallint
,DeviceType tinyint
,UserName nvarchar(128)
,ServerName nvarchar(128)
,DatabaseName nvarchar(128)
,DatabaseVersion int
,DatabaseCreationDate datetime
,BackupSize numeric(20,0)
,FirstLSN numeric(25,0)
,LastLSN numeric(25,0)
,CheckpointLSN numeric(25,0)
,DatabaseBackupLSN numeric(25,0)
,BackupStartDate datetime
,BackupFinishDate datetime
,SortOrder smallint
,CodePage smallint
,UnicodeLocaleId int
,UnicodeComparisonStyle int
,CompatibilityLevel tinyint
,SoftwareVendorId int
,SoftwareVersionMajor int
,SoftwareVersionMinor int
,SoftwareVersionBuild int
,MachineName nvarchar(128)
,Flags int
,BindingID uniqueidentifier
,RecoveryForkID uniqueidentifier
,Collation nvarchar(128)
,FamilyGUID uniqueidentifier
,HasBulkLoggedData bit
,IsSnapshot bit
,IsReadOnly bit
,IsSingleUser bit
,HasBackupChecksums bit
,IsDamaged bit
,BeginsLogChain bit
,HasIncompleteMetaData bit
,IsForceOffline bit
,IsCopyOnly bit
,FirstRecoveryForkID uniqueidentifier
,ForkPointLSN numeric(25,0) NULL
,RecoveryModel nvarchar(60)
,DifferentialBaseLSN numeric(25,0) NULL
,DifferentialBaseGUID uniqueidentifier
,BackupTypeDescription nvarchar(60)
,BackupSetGUID uniqueidentifier NULL
,CompressedBackupSize bigint
,containment tinyint NOT NULL
);
--SQL Server 2014/2016 have three extra columns in the file header.
IF (@@MICROSOFTVERSION/POWER(2,24) > 11)
ALTER TABLE #BackupHeader
ADD KeyAlgorithm nvarchar(32)
,EncryptorThumbprint varbinary(20)
,EncryptorType nvarchar(32);
GO
-- Insert file header info into the temp table.
INSERT #BackupHeader
EXEC ('RESTORE HEADERONLY FROM DISK = ''C:\SQL_Backup\AdventureWorks2012.bak''');
GO
-- Compare the family_guid values.
SELECT DatabaseName,FamilyGUID FROM #BackupHeader;
GO
SELECT DB_NAME(database_id),family_guid FROM sys.database_recovery_status
WHERE database_id = DB_ID('AdventureWorks2012');
GO
As you can see they do not match, which is why we get the error.
BONUS INFO:
There is one case where the database_guid can be the same even for different databases. If you detach a database, make a copy of the MDF and LDF files, and then re-attach those files as a different database name, the database_guid values will be the same.
SELECT DB_NAME(database_id), database_guid, family_guid
FROM sys.database_recovery_status;
As you can see, the database_guid and family_guid are the same for all three copies of the AdventureWorks database.
