Sql-server – SQL Recover from .bak file with NOINIT

backuprestoresql server

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

Best Answer

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';
go

You will want to do:

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

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.

Related Question