August 26, 2004 at 7:23 am
I'm new to the site and am sure this must have been discussed before - but I can't find anything.
When I restore a log to a point in time in Enterprise Manager it Teminates Abnormally stating that 'The preceding restore operation did not specify WITH NORECOVERY or WITH STANDBY. It says this is needed for all but the final step.
What does this mean, and what do I do.
August 26, 2004 at 8:16 am
It means just what it says....an example will explain better:
RESTORE DATABASE mydb
FROM mydb_backup
WITH NO_RECOVERY
RESTORE LOG mydb
FROM mydblog_backup1
WITH NO_RECOVERY
RESTORE LOG mydb
FROM mydblog_backup2
WITH RECOVERY,
STOPAT = '2004-08-26 12:00:00'
Refer to the BOL for more information, use the Index tab and enter RESTORE DATABASE.
-SQLBill
What's the BOL?
BOL=Books OnLine=Microsoft SQL Server's HELP
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine
September 8, 2004 at 5:52 am
i think also that you can inser a checkpoint in your transaction .. so you can restore back to this checkpoint
read more on this in BOL, if this is important to you
also if you need any help in backup we are here
Alamir Mohamed
Alamir_mohamed@yahoo.com
September 8, 2004 at 7:45 am
Hi SQLBill
Thanks very much for replying.
What you have described is, I guess, run from Query Analyser. Can I restore a logfile in Enterprise Manager without incurring the error message I described?
Thanks again
Paul
September 8, 2004 at 7:49 am
Thanks Alamir
Maybe its me, but I can't find a logical way forward from BOL.
What SQL Bill has described is, I guess, run from Query Analyser. Can I restore a logfile in ENTERPRISE MANAGER without incurring the error message I described? I need an easy way to do this, which I can explain to others.
Thanks again
Paul
September 8, 2004 at 8:38 am
Paul,
While in the Restore database dialog, click on the options tab, and select
"Leave database nonoperational but able to restore additional transaction logs."
for all but your last restore. When you restore your last transaction log backup (the one with the "point in time" that you are restoring to), then select
"Leave database operational. No addtional transaction logs can be restored."
This will mimic what SQLBILL has shown you for Query Analyzer.
Steve
September 8, 2004 at 10:21 am
Hi Steve
Thanks very much for your help.
CM is the name of my database.
What I have done (all in Enterprise Manager) is to create a two devices:
CM_DB
CM_Log
CM_DB is backed up once at midnight with the command - backup database CM to cm_db with format
CM_Log is backed up once at midnight with the command - backup log cm to cm_log with format (with format truncates the log I believe)
CM_Log is further backed up once each quarter hour with the command - backup log cm to cm_log
To Restore:
In Enterprise Manager, I restored to the device cm_db, and, on the Options tab, I chose to Force Restore over the Existing Database. That would give me correct data up until the previous midnight.
I then selected to restore the device cm_log:
I selected "Leave database operational. No addtional transaction logs can be restored" on the Options tab as you suggested.
On the General tab I selected Transaction Log (but have tried all the others as well).
I have run it at this point. Also I've tried it having selected a backup set from View Contents. But, whatever I do, I get the error message I originally described.
I'm obviously missing something. Can you suggest what???
Many thanks again
Paul
September 8, 2004 at 11:39 am
Paul,
You MUST select
"Leave database nonoperational but able to restore additional transaction logs." on EVERY restore except the last one.
This means that you have to do that on the original restore in which you restore the full database backup. Then you will need to restore all the transaction log backups that were taken after the full restore up to the time you wish to restore to. If you are doing this from enterprise manager, just select all the transaction logs that fall under the heading of the full backup. I don't generally use enterprise manager for restores, so I'm not positive about this, but I think you can select all the logs at one time and enter your point in time to restore to, selecting "Leave database operational..."
If doing it from query analyzer, you would restore the full backup using the "WITH NORECOVERY" clause, then EVERY transaction log taken after the full up to the one right before the one that has the time you are restoring to, also using the "WITH NORECOVERY" clause. Then you would restore that last transaction log using the "WITH STOPAT" clause and the "WITH RECOVERY" clause.
Hope this helps,
Steve
September 9, 2004 at 1:18 am
restore Backup from enterprise manager
to restore many backups , open enterprise manager and do this :
1- Right-click on your database and choose (All tasks) >> restore Database
2- in general tape .. select "from device" .. click on (select device) .. and go to your full backup (first one)
3-choose Restore backup set >> database Complete (which mean full backup)
4- go to option tape
5- check (force restore over existing database) .. to force restore
6- in recovery complition state >> (leave database NON operational but able to restore additional transaction logs
7- click OK.. and restore begin
after this, you will then see database is offline (gray)
to make any extra log backups (or differintial backup), do this :
- make step 1 and 2 described above.. and choose your log backup
-choose Restore backup set >> transaction log (which mean transaction log backup)
- make step 4 and 5 described above
- make step 6 described above if you will make additional log
or recovery complition state >> (leave database operational. No additional transaction logs can be restores) if this is the last one .. and then you will see database back to yellow (active again)
- click OK.. and restore begin
by the way, you have to make restore in the same orser that the backup do the files
I hope this help u
Alamir Mohamed
Alamir_mohamed@yahoo.com
September 9, 2004 at 2:59 am
Hi Steve and Alamir
Thanks so much for your help. I now have it working. Although I still have a question:
When I choose a backup set from the list in Enterprise Manager, I can only choose one at a time. So, I have sets 1-20 (e.g.), for each quarter hour from 7 a.m.
If I want to restore bacup set 14 at 10:30 a.m., I have to install each one previous to set 14 in turn. And each time it throws me out of the Restore dialog screen.
Is there any way of restoring the backup sets as a batch, i.e. choosing all up to 14, and then 15 as the final backup set - to put the database back on line.
Thanks again to you both
September 9, 2004 at 2:39 pm
Hmm. I haven't used enterprise manager for my restores in a very long time, but, without actually restoring anything, I selected a transaction log backup to restore, and Enterprise manager automatically selected the previous backups. So, I'm not sure why you are experiencing this.
Steve
September 10, 2004 at 2:33 am
Thanks, Steve.
I can't work this one out either. Are you using SQL Server 2000? Enterprise Manager (Microsoft Mangagement Consol 1.2 v5 Build 2195 Service Pack 4 ??
Regards
Paul
September 10, 2004 at 8:09 am
Yes. SQL 2000 svc pack 3a, Windows XP Professional, svc pack 1, MMC 2.0. But I'm pretty sure that it has worked this way on other (earlier builds) machines as well.
I encountered some really bizarre problems in Books Online yesterday. The expandable text (grey boxes) were empty when I expanded them, and if I selected "view source" nothing would happen at all. I have a test server in my office, turned around to it, and tried the same things, and it worked fine. I reinstalled Windows sp1, reinstalled SQL sp3a, reinstalled Books Online, all to no avail. Eventually, I cleared my Internet Explorer cache, reset IE to its defaults, and ran AD-Aware. Not sure which fixed it, but one of them did. I say all this to point out that with Microsoft, you never know what might be affecting what.
Steve
September 10, 2004 at 9:44 am
Many thanks for all your help, Steve. Maybe I'll eventually find the prob...
All the best
Paul
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply