Missing Tables from a Backup and Restore (Migration) on SQL Server [migrated]

Here is probably what happened (and this is a pretty common scenario, so don't be discouraged):

1. You backed up the database at some point in time (say, TO DISK = '\\somelocation\file.bak')
2. You made changes to the database
3. You backed up the database again (to the same '\\somelocation\file.bak')
4. Your restored the backup from that file, which contains multiple backups, without specifying which backup you want.

You can validate by running the following:

RESTORE HEADERONLY FROM DISK = N'<location>\<filename>.bak';

If my guess is correct, this will return multiple rows. The one with the highest Position is (usually) the most recent backup. The one that is restored by default (if you don't specify which one you want) is Position = 1 which is the oldest backup. Nothing will warn you that you're not taking the newest.

If you want to continue trying to restore from this backup, you need to add WITH FILE = <highest position in the above output> to the restore command. Inspect all the rows of the output and verify the BackupStartDate to be sure you're picking the most recent backup.

(I kind of wish the argument was Position = and that there was a magic keyword for "take the newest one, silly.")

But I suggest just taking a new backup to a new filename and possibly trashing the existing one (which is likely much larger than it needs to be if you're only interested in the most recent backup anyway).

If you want to keep multiple copies of the backup over time, then back up with a timestamp in the file name. Then you have individual files and you can immediately correlate exactly when each one was taken.

If you want to overwrite the old backup completely, then use WITH INIT as part of the backup statement. I would suggest you take a new backup with a new filename, use WITH INIT, and then try to restore that one.