Specifying Appended Backups

In the process of backing up a database and restoring it to another database, a row that existed in the original database wasn't restored to the new database, even though the backup and restore messages indicated success. I used the following commands:

BACKUP DATABASE RECON04 TO DISK = 'f:sqlrestorefromdbrecon04.dat'RESTORE DATABASE RECON10 FROM DISK =   'f:sqlrestorefromdbrecon04.dat'  with replace,  move   'SampleESOP_data' to 'e:mssql7dataRECON10_data.mdf',  move   'SampleESOP_log' to 'e:mssql7logRECON10_log.ldf' 

The date on the backup file was updated, but the data restored to the secondary database doesn't seem to be as current as the data in the source database. The source database backup option is set to append to the existing media. Originally, the source-database owner was different from the new-database owner; I changed the owners to be the same, but that didn't help. I eventually got the restore to work by deleting the directory-level backup file, backing up the database again, then restoring. The only problem I can think of is that when appending backups to the backup set, the backup media is using the oldest backup set. Do you have any suggestions?

Your guess is correct. By default, the restore uses the first file in the backup set; you must explicitly specify appended backups, as the following RESTORE command example from SQL Server Books Online (BOL) shows:

RESTORE DATABASE MyNwind   FROM MyNwind_1   WITH NORECOVERYRESTORE DATABASE MyNwind   FROM MyNwind_1   WITH FILE = 2

This example restores a full database backup followed by a differential backup and shows restoring from the second backup set on the media. In this case, the differential backup is appended to the backup device that contains the full database backup.