December 13, 2005 at 2:55 am
Hello,
Before asking the actual question I would like to describe the scenario.
I took full backup of my databases with log backup at 1:00 Pm.
At 1:15 one of my procedures got deleted by mistake. I was working on that and
after taking my last backup i.e.1;00 Pm I had mad a lot of changes in that sp.
after deleted this sp I again took the whole backupd of databse.
I was trying to retrive that sp with the last changes.is it possibel to
recover that with all the changes. I have heard abt point-in-time recovery.
Can anybody tell me abt this and how can I retrive this sp with all the latest
changes?? Need response urgently..
Thanks & Regards
Niladri saha
Niladri
Niladri Kumar Saha
December 13, 2005 at 4:20 am
No probs. What you want to do is take another log backup of your database as it stands.
Then you should restore your full backup to a new database (just to be safe), and restore all transaction logs up until the last log backup that you just made. Each of these should be restored with norecovery as per normal transaction log restores.
Finally, you can use the "with stopat=[time]" option and the "recovery" option on your last transaction log when restoring - specify a "stopat" time just before your SP got deleted. Look in SQL Server books online to get the proper syntax...
Good lucK!
December 14, 2005 at 6:04 am
Hello,
you got the answer you need from Ian, just allow me to make one suggestion... To avoid similar problems in future, consider making all changes to stored procedures, views and tables via SQL - i.e. write the Create or Alter statements in QA and save them. If the change touches some vital object, retain all the versions - marked with version number and date - so that you can revert to some older version at any moment. If then one of the procedures gets deleted, you can recreate it in a matter of seconds, without having to restore entire database. This way it is also quite simple to apply the same change to several databases (dev, test, production..).
HTH, Vladan
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply