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

  • You might want to mentions which database you are using. Oracle? MySQL? SQL Server? PostgreSQL? Something else? – Brian Campbell Nov 15 '12 at 17:16
  • My answer assumes SQL Server. Can you confirm? – Thomas Stringer Nov 15 '12 at 17:33

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.

  • Many thanks! Worked perfectly. You were right in your assumption that I was using SQL, apologies for leaving that out of the question. Just one last question (for now, at least), am I able to query the results of the restore headeronly function to get the max position automatically? – cprlkleg Nov 16 '12 at 9:06
  • Yes, you can do that but it would require some dynamic SQL and a temp table. Not too difficult, if you'd like some code let me know. – Thomas Stringer Nov 16 '12 at 14:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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