Trying to restore Database to a certain point in time in SQL Server 2008

I have a .bak I am trying to restore to a particular point in time. I have done lots of full backup restores in sql before but this is my first restore I ever tried to a particular point time (ex. april 1, 1:00pm) in sql. I can see in sql how to limit the possible restore sets by specifying a point in time. But when I choose the transactionlog backup I want to restore ( I am assuming I would want to do restore a transaction log, correct me if I'm wrong) I get the following error..

System.Data.SqlClient.SqlError: The log or differential backup cannot be restored because no files are ready to rollforward

I usually pick the "Overwrite Existing Database(With Replace)" option for this. Can someone tell me if this is still correct, or what the correct process would be or what I need to understand??? What particular backupsets do I need to choose? Any advice would be greatly appreciated.


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.

You need to first restore a full backup that is dated prior to the transaction log backup, and leave it open for transaction log restores.
That should leave it in recovery mode.
Then, you can restore the transaction log backup to the point specified.
I assume that you have a transaction log backup file whcih you want to resstore to a point in time.

I have tried this and this is how you do it.

If your

database name is MyDatabase
transaction log backup file name and path are C:\Share\MyTransactionLog.bak


RESTORE DATABASE MyDatabase FROM DISK =  'C:\Share\MyTransactionLog.bak' WITH STOPAT= 'Jan 18, 2010 4:19 PM', RECOVERY

Let me know if you need more help.

jazzcatoneAuthor Commented:
Ronney, I ran this command and the restore process ran all night and never finished. Had to finally kill the sql service. The DB never restored. Are there any other suggestions??

jazzcatoneAuthor Commented:
After doing some more reading I think I understand what was happening here. Running a command with  leaves the DB in a restoring state. So I tried basically restoring the full back up and then the individual transaction logs. That I thought would give me what I wanted. but I get this error now. It seems it restores like the first 3 transaction logs and then dies with the follwing errors. Any idea on what to do ?

The log in this backup set begins at LSN 174279000000088200001, which is too recent to apply to the database. An earlier log backup that includes LSN 174279000000007900001 can be restored


You are obviously missing one transaction log.

Use the following query to see the backps on your database. Run it on the "msdb" database

select database_name, backup_finish_date, type, [name], user_name, first_lsn, last_lsn, * from backupset
where database_name = '<Your DB Name>'
order by 2


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
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 2008

From novice to tech pro — start learning today.