1

Last night I made a full backup of my DB in SQL server management, when I restore the DB instead of being complete - it is missing all its data from the last few month.

The full backup was taken last night and is market as - 07/05/2014 16:54 However the information in the tables has restored itself to 24/02/2014

any help would be greatly appreciated -

2
  • Did it face some constraint issue regarding Disk Space? What is the approximate size of your DB? in MB or GB??
    – Chandan
    May 8, 2014 at 11:57
  • I don't think so, the DB is only 66.264 KB and we have lots of space on the server
    – user3572895
    May 8, 2014 at 12:02

1 Answer 1

5

Does your backup file contain multiple backups (known as backup sets), and are you restoring just the earliest backup set from the backup file?

To see the backup sets in a file run the following and note the Position column, which denotes the backup set:

RESTORE HEADERONLY FROM DISK='mybackup.bak'

To restore a specific backup set from a backup:

RESTORE DATABASE ... WITH FILE = <position from previous statement>

Why does this happen?

If you use the same filename every time in your backup script (e.g. BACKUP DATABASE MyDb TO DISK='backup.bak'), and that file is not deleted or changed between backups, then your backup is added to the backup sets in that file. If you then run RESTORE DATABASE ... without a WITH FILE=<position> clause, you will always restore the first backup set in the backup media - usually your earliest one.

The details:

The RESTORE HEADERONLY command is useful for a backup file (if you are looking at it on a different server to the one it was created on). You can also see the backups on the server that created them by running the command:

SELECT backup_set_id, media_set_id, position, backup_finish_date 
FROM msdb.dbo.backupset

If you want to check which backup set you just restored from a multi-file media then you can run:

SELECT * FROM msdb.dbo.restorehistory

Note the backup_set_id which corresponds to the msdb backupset table and in turn to the media file and position within that file.

References:

From MSDN: RESTORE Arguments (Transact-SQL):

Backup Set Options

FILE ={ backup_set_file_number | @backup_set_file_number }

...

When not specified, the default is 1.

7
  • Thanks for your answer - I regularly take full backups but the date its restoring to seems like the date I first began to back up on. I was unaware that a manual made .bak file could contain the previous versions - could you please expand on your answer?
    – user3572895
    May 8, 2014 at 12:04
  • Do you always back up to the same filename?
    – Andy Brown
    May 8, 2014 at 12:06
  • Hi Andy thank you for your reply - yes I do always backup to the same file name
    – user3572895
    May 8, 2014 at 12:12
  • I think that's your problem. See my expanded answer: use the msdb queries to verify that your media has multiple files in the media file, that your restores are only restoring the one at the first position, and that that one is the very earliest one you made.
    – Andy Brown
    May 8, 2014 at 12:16
  • Hi Andy - thank you very much you are totally correct, It is restoring from the first backup instead of the latest - I'm just working through the rest of your reply and will get back to you shortly.
    – user3572895
    May 8, 2014 at 12:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.