point in time restore problems

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

point in time restore problems

Author

Topic

pjanil1
Starting Member

10 Posts

Posted - 2003-12-17 : 22:54:41

Dear friends,
I have a problem. I have transaction log backups taken every 2 hours, differential bkps every day night and full bkp once in a month(full recovery model). Transaction log backup is take at 10 am. Due to a wrong update done by a user at 10.30 I was asked to do a point in time restore to that point.
but the current time is 11.15. If I do a point in time restore to 10.30 am , how can I apply other transactions(which are not related to the problem one) which have occured till 11.15 am. how to recover that from the current log and apply it selectively. Matter very urgent. Kindly help
also kindly tell me if i restore filegroups(group 1 and group 2) which all transaction logs i should restore and the sequence. I am confused on that
with regards
Anil

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2003-12-17 : 23:15:55

Here is one way to solve your problem:

1. Create a new database, name it something like DatabaseName_2.
2. Perform your recovery on DatabaseName_2, you will have to use the command line to do this, as the gui doesn't seem to be able to handle a restore from the catalog to a different database.
3. Copy the pertinent data back to your live database.
4. Drop DatabaseName_2 since it is no longer needed.

You might also want to look into a tool called log explorer (http://www.lumigent.com/products/le_sql/le_sql.htm) from lumigent.

Also, Here is what the command line recovery code might look like.

-- Begin recovery operation with the NORECOVERY argument

RESTORE DATABASE DatabaseName_2
FROM DISK = 'c:\Program Files\Microsoft SQL Server\Backup\DatabaseName\DatabaseName_db_200311200131.BAK' 
WITH MOVE 'DatabaseName_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\DatabaseName2_Data.MDF', 
MOVE 'DatabaseName_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\DatabaseName2_Log.LDF', 
NORECOVERY , replace 

RESTORE LOG DatabaseName_2 
FROM DISK = 'c:\Program Files\Microsoft SQL Server\Backup\DatabaseName\DatabaseName_tlog_200311200200.TRN'
WITH MOVE 'DatabaseName_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\DatabaseName2_Log.LDF', 
NORECOVERY, replace
 
-- Play the final log, with the point in time to recover to, and with RECOVERY enabled 
 
RESTORE LOG DatabaseName_2
FROM DISK = 'c:\Program Files\Microsoft SQL Server\Backup\DatabaseName\DatabaseName_tlog_200311200300.TRN'
WITH MOVE 'DatabaseName_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\DatabaseName2_Log.LDF', 
RECOVERY, replace,
STOPAT = 'Nov 20, 2003 2:56 AM'

Of course names, locations, times, etc will be different. You should get the basic idea from what I have posted and in BOL.

-ec

pjanil1
Starting Member

10 Posts

Posted - 2003-12-17 : 23:57:03

Thank you very much for the help.
but my problem is with the restoration of data between 10:30 am and 11.15 am (45 mts). i don't have a backup for that. but online transaction logs are intact. out of the trasaction log data, i want to eliminate few records that occured at 10:30 am(that problem update statement which created all issues) and apply the rest to the database which is restored to point in time at 10:30 am.
Also kinldy tell me about filegroup restoration and associated transaction log restoration
thanks in advance
anil

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-18 : 12:30:22

In order to get the 11:15 data, you need to backup the current transaction log and use that one to do the point in time restore. For how to restore, check out SQL Server Books Online.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources