T-SQL restore database restores old data only

Question

I had a migration from SQL Server 2014 to SQL Server 2022.

I first created FULL backups from the SQL Server 2014 instance, and then ran RESTORE DATABASE script shown below to do a test migration. Everything worked as planned.

A week later, I dropped all of the databases on the new server, backed up SQL Server 2014 again to the same file names, and then again ran the below script. SQL Server 2022 however did not restore the latest version of the file, and instead restored only the week old data. Only right-clicking in the UI and doing a "restore" from there restored the current version of the data.

Can someone please help me understand why that is to prevent it in the future?

BACKUP DATABASE db1 TO DISK = db1.BAK;  

RESTORE DATABASE [db1]
FROM DISK = N'\\sql2014\migration\db1.BAK' WITH FILE = 1, 
     MOVE N'db1_data' TO N'D:\Data\db1_data.mdf', 
     MOVE N'db1_log' TO N'D:\Log\db1_log.ldf',
     NOUNLOAD,
     REPLACE,
     STATS = 5;
GO

Answer 1

The problem is that you've put multiple backups into a single file.

FILE = 1

This tells SQL Server to restore from the first (read: oldest) file from the backup (which is also the default if you don't specify). You can see the backups and their file numbers by running:

RESTORE HEADERONLY FROM DISK = N'\\sql2014\migration\db1.BAK';

The important columns in the output to look at are Position (which corresponds to the FILE = parameter you want on the restore) and BackupFinishDate (to confirm you are getting the latest). Of course verify that DatabaseName is correct, since you can back up multiple different databases to the same file.

Not that you should ever do that (even for the same database). In the days of tape and minimizing number of files transfered this may have made sense, but on modern systems, you should always put each backup into its own file. Automation can help with this (e.g. Ola's solution) by simply having a template for the filename that injects a timestamp, keeping backup files unique. Short of that, if you always use BACKUP DATABASE ... WITH INIT, FORMAT then you'll know the backup can only contain the latest one.

But that might not be the goal - e.g. if you want to keep the last 7 backups, you'll need timestamps in the name, or you'll need to place them in different folders.

Finally, I just wouldn't use the UI to restore databases. The commands are much more expressive and give you precise control over exactly what you want to do. Use the UI to generate/learn the commands, maybe, but don't next / next / OK.