sql transaction isolation level

I am inserting a record in a table ,
Updating another related table if the insertion was successful
Other wise roll back the transaction

What is the best isolation level to use?  I want to have the best performance.

Concerning restoring my database If I did full backup will I loose anything.

Concerning restoring my database if i did partial backup what i should do so as not to loose anything
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DBAduck - Ben MillerPrincipal ConsultantCommented:
I think that the normal isolation level that is Read Committed is just fine for what you are doing. Just be aware of how long the transaction is held.  You could do Read Committed Snapshot, but that requires some configuration and it would not make your insert, update, stuff more efficient, but it would not necessarily block others just wanting to read from those tables.

As far as the backup and restore of a database.  If you backup a database full, and then restore it, the only thing you can lose is anything that happened in between the full backup and the restore.  If you backup the database and take log backups, then you can get up to the point that the database is at before you restore.

For the restoring the partial backup, I am assuming that you mean a Differential or you could mean that you backed up a file or filegroup, and you stand to lose the data that is committed after the backup and before the restore.

To not lose anything I would say that you would take a full backup and then log backups and when you get to the point of restore, you can restore up to a certain point by backing up the last log backup and then restoring the full and then the logs up to the point in time.

Hope that was not to jumbled.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
You just leave the isolation level on the default (Read Committed). This will prevent other queries from reading your new data (in the first and second table inserts and updates)  until your whole transaction is committed. It will also use minimal locks on both your updates and other queries so will be safe and perform well.

You will not lose data if you restore because transaction must be committed for them to be included in the backup file. Partial backups are the same, anything committed to the transaction log will make it into partial backups.
Isolation level default Read Committed is fine for your example.
But you need to use

      //YOUR STATEMENTS insert and updates
sohairzaki2005Author Commented:
Thanks to you all
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.