Avatar of indikad
indikad
 asked on

Point in time recovery sql server 2008 database from a full backup

I have done a full backup of a database that had been setup to full recovery model.
I don't have a separate transaction log file backups or differential backups
How can I do a point in time recovery using the file backup ?  ( sql server management studio option would be best )
Microsoft SQL ServerMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
indikad

8/22/2022 - Mon
SOLUTION
Anthony Perkins

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Ted Bouskill

To be clear, a full database backup is at a point in time.  It's not all the events preceding the backup or after.  It's a copy at one point in time.  So, if you want any transactions that occurred after the backup, then you have to restore from the transaction log backups.  If you haven't been keeping the transaction log backups then you might not be able to restore to a point in time.
indikad

ASKER
i did a transaction  log backup too after the full database backup - and used that as a source to recover but it did not work.
I ma trying a test scenario  - so this is what I did
1.  made a change to the db at 11.19am
2. Took a full backup at 11.25am
3. Took a Transactio Log backup at 11.49am
4. restrored the full backup to a new db using the full backup  
5.  tried to restore to point in tim ( 11:22 am) using the tranasction log backup but cannot
error is
TITLE: Microsoft SQL Server Management Studio
------------------------------

Restore failed for Server 'KEYBIZ009\SAP'.  (Microsoft.SqlServer.SmoExtended)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.4000.0+((KJ_PCU_Main).120628-0827+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

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

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.4000.0+((KJ_PCU_Main).120628-0827+)&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------
Anthony Perkins

3. Since your change occurred prior to the Full Backup you do not need the Transaction Log backup to do the point-in-time restore.
4. This is the point you should have made the point-in-time restore.

As far as I know you cannot do a point-in-time restore with the GUI, so can you post the code you are using.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER CERTIFIED SOLUTION
Scott Pletcher

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
indikad

ASKER
pardon my ignorance - how can I do a forward recovery ? how should I do the backups please ?
should not the full backup of 11:25am contain the work done at  11:19 am ?
indikad

ASKER
Anthony  -  I am not using code. Trying to get this done on GUI.
so does that mean I need to use code ?
Anthony Perkins

Yes.  Point-in-time restore has to be done using T-SQL.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
indikad

ASKER
Thanks for all of you who replied.