This will, hopefully, be a nice and quick question for someone out there. It continues from my previous question.

I have a .BAK file that I'm trying to restore. The .BAK was created with the NOINIT argument, to try and save previous version, and so has newer data appended (as I understand it). I was wondering if this meant that - upon restore - restore would try to use an old version of the backup?

I.e. I have backed up the DB, added new tables to it, performed another full back-up and dropped the added tables. When I try to restore, I am not getting the 'new' tables back, is this because the restore is finding a version of the database in the .BAK which was created before the 'new tables'? If so, how can I specify to use the latest data?

EDIT: Apologies for not stating sooner, this is using an SQL Server database


1 Answer 1


That's because by not specifying the FILE parameter of RESTORE DATABASE, it is defaulting to 1 which will be the 1st backup set on that media. See the section on this page of Specifying a Backup Set.

Instead of:

restore database YourDb
from disk = 'C:\yourpath\backupfile.bak';

You will want to do:

restore database YourDb
from disk = 'C:\yourpath\backupfile.bak'
with file = <n>;

Where N is the backup set number in the media. N can be obtained from running RESTORE HEADERONLY on the media. The column you will be concerned with is Position to use with the FILE option.


Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.