restore to a specific state

Dear all,
I'm using SQL server 2000.
The last backup made was on 02/Jul/2007.
Today is 24/Jul/2007: 2 days ago, some majors changes (insert/update/delete)
were made on the database. and I want to rollback these transactions (and
only those made in the 2 days).
Is there any code, or something to do that can help me?
Thank you in advance.
joujPerform transaction log backup. Then restore your full backup made on
02/Jul/2007 and use just created transaction log backup by restoring it with
option STOP AT (this will allow you to limit the number of restored
transactions just to the date and time you need).
--
Regards
Pawel Potasinski
U¿ytkownik "jouj" <jouj@.discussions.microsoft.com> napisa³ w wiadomo¶ci
news:C419271B-0404-48F9-A973-41AB4F0D964F@.microsoft.com...
> Dear all,
> I'm using SQL server 2000.
> The last backup made was on 02/Jul/2007.
> Today is 24/Jul/2007: 2 days ago, some majors changes
> (insert/update/delete)
> were made on the database. and I want to rollback these transactions (and
> only those made in the 2 days).
> Is there any code, or something to do that can help me?
> Thank you in advance.
> jouj|||Hi,
I followed your steps and on the last one:
RESTORE LOG mydb
FROM Disk = 'F:\BACKUP\mydbLogfile24_07_2007.bak'
WITH RECOVERY, STOPAT = 'Jul 23, 2007 12:00 AM'
I'm getting this error message:
[
The preceding restore operation did not specify WITH NORECOVERY or WITH
STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH
STANDBY for all but the final step.
]
Regards,
jouj
"Pawel Potasinski" wrote:
> Perform transaction log backup. Then restore your full backup made on
> 02/Jul/2007 and use just created transaction log backup by restoring it with
> option STOP AT (this will allow you to limit the number of restored
> transactions just to the date and time you need).
> --
> Regards
> Pawel Potasinski
>
> U¿ytkownik "jouj" <jouj@.discussions.microsoft.com> napisa³ w wiadomo¶ci
> news:C419271B-0404-48F9-A973-41AB4F0D964F@.microsoft.com...
> > Dear all,
> > I'm using SQL server 2000.
> > The last backup made was on 02/Jul/2007.
> >
> > Today is 24/Jul/2007: 2 days ago, some majors changes
> > (insert/update/delete)
> > were made on the database. and I want to rollback these transactions (and
> > only those made in the 2 days).
> > Is there any code, or something to do that can help me?
> >
> > Thank you in advance.
> > jouj
>
>|||1. Backup your transaction log with NORECOVERY option (this will put
database in Restoring state).
2. Restore full backup (created on 02/Jul/2007) with NORECOVERY and REPLACE
options. <-- THIS IS WHAT YOU DID WRONG (did not specify NORECOVERY option)
3. Restore transaction log backup created in step 1 witn RECOVERY and STOP
AT options.
--
Regards
Pawel Potasinski
U¿ytkownik "jouj" <jouj@.discussions.microsoft.com> napisa³ w wiadomo¶ci
news:62D1A0E0-D972-403C-BC39-356BCD56BAAC@.microsoft.com...
> Hi,
> I followed your steps and on the last one:
> RESTORE LOG mydb
> FROM Disk = 'F:\BACKUP\mydbLogfile24_07_2007.bak'
> WITH RECOVERY, STOPAT = 'Jul 23, 2007 12:00 AM'
> I'm getting this error message:
> [
> The preceding restore operation did not specify WITH NORECOVERY or WITH
> STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH
> STANDBY for all but the final step.
> ]
> Regards,
> jouj
> "Pawel Potasinski" wrote:
>> Perform transaction log backup. Then restore your full backup made on
>> 02/Jul/2007 and use just created transaction log backup by restoring it
>> with
>> option STOP AT (this will allow you to limit the number of restored
>> transactions just to the date and time you need).
>> --
>> Regards
>> Pawel Potasinski
>>
>> U?ytkownik "jouj" <jouj@.discussions.microsoft.com> napisa3 w wiadomo?ci
>> news:C419271B-0404-48F9-A973-41AB4F0D964F@.microsoft.com...
>> > Dear all,
>> > I'm using SQL server 2000.
>> > The last backup made was on 02/Jul/2007.
>> >
>> > Today is 24/Jul/2007: 2 days ago, some majors changes
>> > (insert/update/delete)
>> > were made on the database. and I want to rollback these transactions
>> > (and
>> > only those made in the 2 days).
>> > Is there any code, or something to do that can help me?
>> >
>> > Thank you in advance.
>> > jouj
>>|||Dear Mr. Pawel,
Thank you for your help but it did'nt work for me.
I'm getting this message:
The log in this backup set begins at LSN 86445000000038300001, which is too
late to apply to the database. An earlier log backup that includes LSN
72624000011844600001 can be restored.
So I think there was an old backup for the log file made after my backup of
02/07/2007 and the actual log file does not start from the backup time.
Thank you for your efforts,
Regards,
jouj
--
--
G.Haddad
--
"Pawel Potasinski" wrote:
> 1. Backup your transaction log with NORECOVERY option (this will put
> database in Restoring state).
> 2. Restore full backup (created on 02/Jul/2007) with NORECOVERY and REPLACE
> options. <-- THIS IS WHAT YOU DID WRONG (did not specify NORECOVERY option)
> 3. Restore transaction log backup created in step 1 witn RECOVERY and STOP
> AT options.
> --
> Regards
> Pawel Potasinski
>
> U¿ytkownik "jouj" <jouj@.discussions.microsoft.com> napisa³ w wiadomo¶ci
> news:62D1A0E0-D972-403C-BC39-356BCD56BAAC@.microsoft.com...
> > Hi,
> >
> > I followed your steps and on the last one:
> >
> > RESTORE LOG mydb
> > FROM Disk = 'F:\BACKUP\mydbLogfile24_07_2007.bak'
> > WITH RECOVERY, STOPAT = 'Jul 23, 2007 12:00 AM'
> >
> > I'm getting this error message:
> > [
> > The preceding restore operation did not specify WITH NORECOVERY or WITH
> > STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH
> > STANDBY for all but the final step.
> > ]
> >
> > Regards,
> > jouj
> > "Pawel Potasinski" wrote:
> >
> >> Perform transaction log backup. Then restore your full backup made on
> >> 02/Jul/2007 and use just created transaction log backup by restoring it
> >> with
> >> option STOP AT (this will allow you to limit the number of restored
> >> transactions just to the date and time you need).
> >>
> >> --
> >> Regards
> >> Pawel Potasinski
> >>
> >>
> >> U?ytkownik "jouj" <jouj@.discussions.microsoft.com> napisa3 w wiadomo?ci
> >> news:C419271B-0404-48F9-A973-41AB4F0D964F@.microsoft.com...
> >> > Dear all,
> >> > I'm using SQL server 2000.
> >> > The last backup made was on 02/Jul/2007.
> >> >
> >> > Today is 24/Jul/2007: 2 days ago, some majors changes
> >> > (insert/update/delete)
> >> > were made on the database. and I want to rollback these transactions
> >> > (and
> >> > only those made in the 2 days).
> >> > Is there any code, or something to do that can help me?
> >> >
> >> > Thank you in advance.
> >> > jouj
> >>
> >>
> >>
>
>|||This should encourage you to set some solid backup strategy for your
databases.
--
Regards
Pawel Potasinski
U¿ytkownik "jouj" <jouj@.discussions.microsoft.com> napisa³ w wiadomo¶ci
news:B5442BF8-1D64-4583-A939-BB43F87C22CC@.microsoft.com...
> Dear Mr. Pawel,
> Thank you for your help but it did'nt work for me.
> I'm getting this message:
> The log in this backup set begins at LSN 86445000000038300001, which is
> too
> late to apply to the database. An earlier log backup that includes LSN
> 72624000011844600001 can be restored.
> So I think there was an old backup for the log file made after my backup
> of
> 02/07/2007 and the actual log file does not start from the backup time.
> Thank you for your efforts,
> Regards,
> jouj
> --
> --
> G.Haddad
> --
>
> "Pawel Potasinski" wrote:
>> 1. Backup your transaction log with NORECOVERY option (this will put
>> database in Restoring state).
>> 2. Restore full backup (created on 02/Jul/2007) with NORECOVERY and
>> REPLACE
>> options. <-- THIS IS WHAT YOU DID WRONG (did not specify NORECOVERY
>> option)
>> 3. Restore transaction log backup created in step 1 witn RECOVERY and
>> STOP
>> AT options.
>> --
>> Regards
>> Pawel Potasinski
>>
>> U?ytkownik "jouj" <jouj@.discussions.microsoft.com> napisa3 w wiadomo?ci
>> news:62D1A0E0-D972-403C-BC39-356BCD56BAAC@.microsoft.com...
>> > Hi,
>> >
>> > I followed your steps and on the last one:
>> >
>> > RESTORE LOG mydb
>> > FROM Disk = 'F:\BACKUP\mydbLogfile24_07_2007.bak'
>> > WITH RECOVERY, STOPAT = 'Jul 23, 2007 12:00 AM'
>> >
>> > I'm getting this error message:
>> > [
>> > The preceding restore operation did not specify WITH NORECOVERY or WITH
>> > STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or
>> > WITH
>> > STANDBY for all but the final step.
>> > ]
>> >
>> > Regards,
>> > jouj
>> > "Pawel Potasinski" wrote:
>> >
>> >> Perform transaction log backup. Then restore your full backup made on
>> >> 02/Jul/2007 and use just created transaction log backup by restoring
>> >> it
>> >> with
>> >> option STOP AT (this will allow you to limit the number of restored
>> >> transactions just to the date and time you need).
>> >>
>> >> --
>> >> Regards
>> >> Pawel Potasinski
>> >>
>> >>
>> >> U?ytkownik "jouj" <jouj@.discussions.microsoft.com> napisa3 w
>> >> wiadomo?ci
>> >> news:C419271B-0404-48F9-A973-41AB4F0D964F@.microsoft.com...
>> >> > Dear all,
>> >> > I'm using SQL server 2000.
>> >> > The last backup made was on 02/Jul/2007.
>> >> >
>> >> > Today is 24/Jul/2007: 2 days ago, some majors changes
>> >> > (insert/update/delete)
>> >> > were made on the database. and I want to rollback these transactions
>> >> > (and
>> >> > only those made in the 2 days).
>> >> > Is there any code, or something to do that can help me?
>> >> >
>> >> > Thank you in advance.
>> >> > jouj
>> >>
>> >>
>> >>
>>