April 7, 2008 at 12:51 pm
I'm doing overnight backup of database and log as part of SQL Server maintenance.
When I tried to restore my database from bak and trn files with SQL Studio and here is the error I'm receiving:"The Specified STOPAT time is too early.All or part of database is already rolled forward beyond that point."
I'm sure that the backup I'm using is the overnight backup for this day.
What is the reason that this error occurs?
Here are the steps I was doing:
1.Restore from device-bak file with second option checked(Leave the database non operational,and do not rollback uncommitted transactions)
2.Restore Transaction Log from file-trn file with point of time checked and set to time I need to be restored to.
April 7, 2008 at 12:59 pm
Two way
SSMS
1) In Restore dialog use
To a point in time option to choose the time.
2) with Restore database t-sql use stopat parameter.
April 7, 2008 at 1:34 pm
- probably your log-backup is created before the end of the full backup.
- many of us start with making a log-backup before making a full backup.
If that is the case for you , only restore the full backup.
- how many backups does the backup file contain ?
use restore headeronly from disk='yourpathandfilename'
(in a query panel)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 7, 2008 at 1:45 pm
Do you think that if I do backup now first of the database and then of log file I'll be able to restore this database on different PC to the desired point of time ?
What will be the best way to do it? Can I use Management Studio?
April 7, 2008 at 1:49 pm
What time did the full backup start and finish?
What time did the log backup run?
What time are you trying to stop at?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 7, 2008 at 1:54 pm
When I run restore headeronly from disk=' ' it shows only one file name,backup type 1,position 1,devicetype 2,
April 7, 2008 at 2:01 pm
properties of bak file created March 26 at 12:00:20 AM Modified at 12:01:10 AM
properties of trn file created March 26 at 12:00:22 AM Modified at 12:01:27 AM
I'm want to go back to March 25 at 3:05:00 PM
April 7, 2008 at 2:04 pm
Lubo Petrov (4/7/2008)
properties of bak file created March 26 at 12:00:20 AM Modified at 12:01:10 AMproperties of trn file created March 26 at 12:00:22 AM Modified at 12:01:27 AM
I'm want to go back to March 25 at 3:05:00 PM
You need to start with a full backup taken prior to March 25th at 3:05:00 PM. You then need the transaction logs created after that backup and the include the point in time you are trying to stop at.
😎
April 7, 2008 at 2:22 pm
Lubo Petrov (4/7/2008)
properties of bak file created March 26 at 12:00:20 AM Modified at 12:01:10 AM
A full database backup contains the contents of the DB at the point in time it was take. With that backup file, you cannot restore the data to a point before the backup file was created.
As Lyne said, you''ll be needing the previous night's full DB backup and the tran logs from then until after 15h05
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 7, 2008 at 2:22 pm
OK I just restored database from the backups taken on 25(before the time I have to go back to).I do have have backups of log and data from the next day.What I should do next?
April 7, 2008 at 2:27 pm
Problem Solved.Thank you everyone.You're wonderful.
April 7, 2008 at 2:28 pm
Hopefully you included the norecovery option in the restore of the full backup. if not, you will have to redo the restore of the full backup.
How many transaction log backup files do you have and what are the times they were each taken? With that info, we should be able to help you come up with the restore log statements you need.
Edit: Guess you already have it taken care of!!
😎
April 7, 2008 at 2:28 pm
Restore your transaction log backups, starting with the one you took right after the full backup on the 25th. Restore them in order, with the NoRecovery option and with the StopAt command.
Once you're restored all the logs and you're sure that the DB is at the point you want, run the following
Restore Database <Your Database Name> WITH RECOVERY
This will put the DB into a usable state. You will not be able to restore any more tran log backups after this, so be sure you're restored all the ones you need to.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 9, 2008 at 7:59 am
Ok, Make it Simple.
Restore recent full backup and try to restore the all diff backups. After restoring all diff. backup try to restore all Trn backups until las trn backup.
If this is the point in time then use T-SQL and use the stop at parameter.
It will resolve your issue.
Manoj
MCP, MCTS (GDBA/EDA)
April 9, 2008 at 8:00 am
Ok, Make it Simple.
Restore recent full backup and try to restore the all diff backups. After restoring all diff. backup try to restore all Trn backups until las trn backup.
If this is the point in time then use T-SQL and use the stop at parameter.
It will resolve your issue.
Manoj
MCP, MCTS (GDBA/EDA)
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply