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

• Aaron Bertrand: Well, does db1.BAK contain more than one backup? Won't FILE = 1 be the oldest one? What does RESTORE HEADERONLY / RESTORE FILELISTONLY tell you about this file? Also, always use WITH INIT, FORMAT when creating backups because I've never understood why anyone - in the last two decades anyway - would want to combine multiple backups into a single file.
• Aaron Bertrand: Also, I really, really, really recommend against "clicking in the UI" to perform important tasks like restoring databases. This leads a lot of people astray because of some of the defaults inherent in those dialogs. Especially when you already know the syntax to do it exactly the way you want. Though I would always explicitly put RECOVERY or NORECOVERY depending on whether you just want to restore the backup or you have further actions (like restoring further logs).
• shaolinmonk: Aaron you are so right.. File=1 is the oldest one. What a silly mistake. Do you happen to know if I just dont include the File # will SQL by default restore the latest version?
• shaolinmonk: If you want credit go ahead and post a solution and I'll mark you. I forgot that SQL backups append to the existing backup with a different header. Silly mistake! Appreciate your quick response.
• Dan Guzman: @shaolinmonk, the documentation states the backup set file number: "When not specified, the default is 1".

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.