We help IT Professionals succeed at work.

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

914 Views
Last Modified: 2014-05-27
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 )
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2012
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Ted BouskillSenior Software Developer
CERTIFIED EXPERT
Top Expert 2009

Commented:
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.

Author

Commented:
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
------------------------------
CERTIFIED EXPERT
Top Expert 2012

Commented:
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.
Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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 ?

Author

Commented:
Anthony  -  I am not using code. Trying to get this done on GUI.
so does that mean I need to use code ?
CERTIFIED EXPERT
Top Expert 2012

Commented:
Yes.  Point-in-time restore has to be done using T-SQL.

Author

Commented:
Thanks for all of you who replied.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.