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



  • I'm trying to copy the structure and contents of a small database on an SQL Server instance at work to an instance on my home computer. It seems like this should be a simple, two-minute operation, but when I do what the manual says I should do, only four of nine tables actually get restored on my home computer. There is no apparent rhyme or reason as to why the other five tables got excluded. This is a starter database project with nothing fancy going on, for the time being--just nine simple tables without even any constraints or triggers applied yet.

    I'm not a seasoned DBA. I think I've done this before without incident, but I don't remember for sure. This sort of thing isn't my normal job. Are there any rookie mistakes I might be making--any nonsensical default SSMS settings that I need to override to get this most basic of all DBA tasks to actually work?



  • 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'\.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 = 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.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2