restore master database: specific

Showing posts with label specific. Show all posts

Monday, March 26, 2012

restore to a specific transaction in the log

i have been doing tests and i cannot manage to do this. here is what i have done and am trying to do:

I created a full backup of the adventureworks database

I made some updates to tables.

I created a differential backup.

I done an update update to another table, dropped a table, and done another update.

Lets say in a production envrionment i just realised the table was dropped and i wanted to go to the transaction just before the table was dropped. is this possible, or are all transactions in the log going to be lost? All i have found remotely related to this are marked transactions, but they are not what i am looking for. i want a list of transactions in the log and be able to select one as the backup point ( via SSMS or TSQL). i am not looking for a point in time restore.

Thanks all

You can use the RESTORE syntax for point-in-time RESTORE to have the log stop at whatever time or LSN you choose. See the STOPAT clause for the RESTORE statement in books-online.

|||yea, i have since discovered that there is no way to restore to a specific transaction, i have to know the time of the transaction i want and restore to that time. pity, it would be a great feature. thanks for the reply.

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
>> >>
>> >>
>> >>
>>

Tuesday, March 20, 2012

Restore Rows from Table

Is it possible to restore certain Rows from a backup to a specific Table?
Also, is there a log to determine which Rows where deleted when?
TIA,
Brandon Moser
Marketing & Systems
Spence, Driscoll & Company, Inc.
bmoser@.(nospam)spencedriscoll.com
> Is it possible to restore certain Rows from a backup to a specific Table?
No. You can't even restore an entire table on its own, unless you have
performed individual filegroup backups and the table lives on the filegroup
by itself. You could restore the backup to another instance, then copy out
the rows you want.

> Also, is there a log to determine which Rows where deleted when?
http://www.aspfaq.com/2449
http://www.aspfaq.com/
(Reverse address to reply.)
|||> Is it possible to restore certain Rows from a backup to a specific Table?
No. You can't even restore an entire table on its own, unless you have
performed individual filegroup backups and the table lives on the filegroup
by itself. You could restore the backup to another instance, then copy out
the rows you want.

> Also, is there a log to determine which Rows where deleted when?
http://www.aspfaq.com/2449
http://www.aspfaq.com/
(Reverse address to reply.)

Restore Rows from Table

Is it possible to restore certain Rows from a backup to a specific Table?
Also, is there a log to determine which Rows where deleted when?
TIA,
--
Brandon Moser
Marketing & Systems
Spence, Driscoll & Company, Inc.
bmoser@.(nospam)spencedriscoll.com>Is it possible to restore certain Rows from a backup to a
specific Table?
Not unless you have done filegroup backups. You could
restore the whole db to another name, then extract the
data you need.
>Also, is there a log to determine which Rows where
deleted when?
>
The only thing Ive heard of is Log Explorer from Lumigent.
>--Original Message--
>Is it possible to restore certain Rows from a backup to a
specific Table?
>Also, is there a log to determine which Rows where
deleted when?
>TIA,
>--
>Brandon Moser
>Marketing & Systems
>Spence, Driscoll & Company, Inc.
>bmoser@.(nospam)spencedriscoll.com
>
>.
>|||> Is it possible to restore certain Rows from a backup to a specific Table?
No. You can't even restore an entire table on its own, unless you have
performed individual filegroup backups and the table lives on the filegroup
by itself. You could restore the backup to another instance, then copy out
the rows you want.
> Also, is there a log to determine which Rows where deleted when?
http://www.aspfaq.com/2449
--
http://www.aspfaq.com/
(Reverse address to reply.)

Restore Report Services

I am looking for specific documentation on how to restore a mission critical
Report Services environment in the event of a catastrophic failure.
Thanx in advance,
GregOn Sep 8, 12:25 pm, "SurferJoe" <Surfer...@.newsgroup.nospam> wrote:
> I am looking for specific documentation on how to restore a mission critical
> Report Services environment in the event of a catastrophic failure.
> Thanx in advance,
> Greg
This article should help.
http://msdn2.microsoft.com/en-us/library/ms155814.aspx
Regards,
Enrique Martinez
Sr. Software Consultant|||> http://msdn2.microsoft.com/en-us/library/ms155814.aspx
This article and subsequent topics contained therein do not give specific
restore step-by-step instructions.
I am attempting to restore/migrate a report services installation in a
testing environment to see if it will work, so far no success:(
Thanx,
Greg|||Hello Greg,
In the article Martinez provided, you could see that we need to backup the
reporting services databases. From your description, it seems that you want
to move the reporting services to another server. This more like migrate
the reporting services.
There is a step by step instruction for migrating SQL 2000 reporting
services to SQL Server 2005 reporting services. You could also follow the
steps to migrate Reporting Services 2005 to 2005.
Migrating Reporting Services
http://msdn2.microsoft.com/en-us/library/ms143724.aspx
Hope this helps.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||This article speaks better to what I am trying to do;
http://support.microsoft.com/kb/842425
These articles are weak on exact restore procedures;
http://msdn2.microsoft.com/en-us/library/ms155814.aspx
http://msdn2.microsoft.com/en-us/library/ms143724.aspx
I have the databases and symmetric key successfully "restored/migrated" to
the new computer running under a different named instance of SQL server.
The remaining problem is how to get the browser or ASP folder structure
"restored/migrated" to the new computer. It not real clear just where this
information is stored for retrieval by the browser.|||Hello Greg,
I am not sure what ASP folder structure you mean. If you mean the report
manager and report server virtual directory, you don't have to move them
because you need to install reporting services in the new server which will
create those 2 virtual directories.
All the report information is stored in the ReportServer and
ReportServerTemp databases. In the KB article you found, it indicate that
you need to move the database to the new server. So all the information
will move to the new server.
Hope this is clear and if you have any question, please feel free to let me
know.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||In nutshell;
The report services databases symmetric key and the virtual directory
information were all backed up from machine (E).
The report services databases and symmetric key were then restored to
machine (M).
After this "Report Services Configuration Manager" indicated that both
machine (E) & (M) were initialized and no changes could be effected using
"Report Services Configuration Manager".
The instructions contained in this article
http://support.microsoft.com/kb/842425 were followed eliminating machine
name (E) from initialized instances.
Report Services seems to be running correctly now on machine (M) with the
data and symmetric key restored from machine (E).
The Report Services Folder Structure available via IE / Report Manager does
not reflect that of the source server machine (E).|||Hello Joe,
Thanks for the update and sharing. If you have any questions, please feel
free to let me know.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Monday, March 26, 2012

restore to a specific transaction in the log

restore to a specific state

restore to a specific state

restore to a specific state

Tuesday, March 20, 2012

Restore Rows from Table

Restore Rows from Table

Restore Rows from Table

Restore Report Services

restore master database

Blog Archive

About Me