Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
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
add comment

migrated from stackoverflow.com Nov 15 '12 at 17:17

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 8 down vote accepted

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.

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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