April 1, 2010 at 10:54 pm
Hi Friends,
I have a db in sql server 2005, It is in FULL Recovery Mode & the log backup is taken automatically by using maintenance plan on every 5 minutes. And full backup is taken once in a week. One of my colleague deleted few records from a table that is being updated very frequently. After that delete operation so many insert and update operations have been done. Later on he (my colleague) came to know that he has deleted wrong entries from table. He doesn't remember the time of delete operation. Is it possible to do point-in-time recovery ? If it is please suggest me how to do.
April 2, 2010 at 12:23 am
Refer this :
Restore Process
To restore the database you can either do this from a query window or from the SQL Server management tools.
These are the commands to restore the database and one transaction log to Apr 23, 2007 05:31:00 PM. The first full backup restore is done using the NORECOVERY option, so the database stays in a loading state and additional backups can be restored. The second restore command restores the transaction log to the point in time = 'Apr 23, 2007 05:31:00 PM'. In addition, we are using the RECOVERY option to put the database back into a useable state after the restore of the transaction log..
RESTORE DATABASE DBUtil
FROM DISK = 'C:\Backup\dbutil.bak'
WITH NORECOVERY
RESTORE LOG DBUtil
FROM DISK = 'C:\Backup\dbutil_log.trn'
WITH RECOVERY,
STOPAT = 'Apr 23, 2007 05:31:00 PM'
If we needed to restore additional transaction log backups this can be done by using the NORECOVERY option for each of the other transaction logs and the STOPAT parameter would only be used for the very last file to be restored.
RESTORE DATABASE DBUtil
FROM DISK = 'C:\Backup\dbutil.bak'
WITH NORECOVERY
RESTORE LOG DBUtil
FROM DISK = 'C:\Backup\dbutil_log_1.trn'
WITH NORECOVERY
RESTORE LOG DBUtil
FROM DISK = 'C:\Backup\dbutil_log_2.trn'
WITH NORECOVERY
RESTORE LOG DBUtil
FROM DISK = 'C:\Backup\dbutil_log_3.trn'
WITH RECOVERY,
STOPAT = 'Apr 23, 2007 05:31:00 PM'
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply