SQL Server The initial backup restore works, the rest don't

Question

I'm trying to backup and restore a database in Sql Server 2014. The initial backup restore works. But when I make some changes to the database and repeat the backup/reload procedure I get the data of the first backup losing the most recent changes. Below is a script that illustrates the issue I'm facing

CREATE DATABASE sample;

CREATE TABLE list (
    id INT, 
    name VARCHAR(50)
);

--first record is inserted

BACKUP DATABASE sample to DISK='D:\Backup\sample.bak';

truncate table list;


GO
USE master;
GO
ALTER DATABASE sample
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
RESTORE DATABASE sample FROM DISK='D:\Backup\sample.bak' with REPLACE;
GO
ALTER DATABASE sample
SET MULTI_USER;
GO


--restored database contains one record
use sample;
select * from list;

--second record is inserted
insert into list values(2,'item_2');
select * from list;


BACKUP DATABASE sample to DISK='D:\Backup\sample.bak';

GO
USE master;
GO
ALTER DATABASE sample
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
RESTORE DATABASE sample FROM DISK='D:\Backup\sample.bak' with REPLACE;
GO
ALTER DATABASE sample
SET MULTI_USER;
GO

--restored database STILL contains one record
use sample;
select * from list;

Answer 1

The issue is that your backup database sample to disk='...' statement by default APPENDS the new backup to the backup device (backup file). As the result, you then have multiple backups stored in the backup file. When doing restore, you are restoring the first backup.

To solve the problem, you can specify to override the content of the backup file by using the "WITH INIT" parameter:

BACKUP DATABASE sample to DISK='D:\Backup\sample.bak' WITH INIT;

More docs can be found eg. here