Link to home
Start Free TrialLog in
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 )
SOLUTION
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
Avatar of indikad
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
------------------------------
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.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of 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 ?
Avatar of indikad

ASKER

Anthony  -  I am not using code. Trying to get this done on GUI.
so does that mean I need to use code ?
Yes.  Point-in-time restore has to be done using T-SQL.
Avatar of indikad

ASKER

Thanks for all of you who replied.