Why does my sql server database "full backup" not contain recent data?

Question

I just migrated a SQL Server 2008 database while the sales staff went to lunch. I did a full backup and then copied the backup to the new server where I restored it to the new SQL Server installation.

The staff got back from lunch and told me that a few of the recent deals they put in right before lunch were no longer showing up in the system. Upon investigation it seems that the full backup was not including some of the very recent data that was added to the database before the backup was performed.

I suspect there is something I'm not accounting for that relates to the transaction log, but not being a MSSQL specialist, I'm not quite sure what I'm doing wrong. Could someone point me in the right direction?

Answer 1

Did you back up just the database, or with transaction logs as well? My guess is that you didn't backup transaction logs, which will contain recent transactions not yet flushed down to the mdb file.

Answer 2

This is why you never do these things during a work day! Next time do this over the weekend and close the database off to users by putting it in single user mode. They could have added the data after your backup or during it if you didn't lock them out first.

Answer 3

The data may have been entered in an open transaction. Since the backup has to adhere to the ACID model, if a transaction was started before the backup started and was not committed by the time the backup has completed, the transaction will be rolled back upon restoring the database.