SQL2005 Piecemeal Recovery  
Author Message
Ikrananka





PostPosted: Wed Oct 11 11:31:03 CDT 2006 Top

SQL Server >> SQL2005 Piecemeal Recovery

Hi,

I have a question about SQL2005 piecemeal recovery.

This gives the option to get the database online while corrupt
files/filesgroups remain offline. But when restoring these corrupt
read-write files/filesgroups, would they need to be rolled forward to the
same point-in-time that the primary filesgroup was restored?

Does that mean that you couldn't leave a read-write filegroup offline during
a piecemeal restore and then afterwards restore this filegroup back to the
previous night's backup, even though the primary filegroup and other
secondary file groups were restored to the point the database was taken
offline? Would this flag up as leaving the database in an inconsistent state?

So, if you have a corrupt read-write file/filegroup, do you need to restore
the whole database to the point just before the single file corruption???

Thanks
Wendy

SQL Server49  
 
 
jbellnewsposts





PostPosted: Wed Oct 11 11:31:03 CDT 2006 Top

SQL Server >> SQL2005 Piecemeal Recovery Hi

From BOL (http://msdn2.microsoft.com/en-us/library/ms177425.aspx)

"Each filegroup-restore sequence restores and recovers one or more offline
filegroups to a point consistent with the database"

For Simple recovery:
"While the database is online, you can use one or more online file restores
to restore and recover offline read-only files that were read-only at the
time of backup. The timing of the online file restores depends on when you
want to have the data online.
Whether you must restore data to a file depends on the following:

Valid read-only files that are consistent with the database can be brought
online directly by recovering them without restoring any data. "

Similary for Full recovery

"If a specific read-only file is undamaged and consistent with the database,
the file does not have to be restored"

HTH

John




> Hi,
>
> I have a question about SQL2005 piecemeal recovery.
>
> This gives the option to get the database online while corrupt
> files/filesgroups remain offline. But when restoring these corrupt
> read-write files/filesgroups, would they need to be rolled forward to the
> same point-in-time that the primary filesgroup was restored?
>
> Does that mean that you couldn't leave a read-write filegroup offline during
> a piecemeal restore and then afterwards restore this filegroup back to the
> previous night's backup, even though the primary filegroup and other
> secondary file groups were restored to the point the database was taken
> offline? Would this flag up as leaving the database in an inconsistent state?
>
> So, if you have a corrupt read-write file/filegroup, do you need to restore
> the whole database to the point just before the single file corruption???
>
> Thanks
> Wendy
 
 
Woo





PostPosted: Wed Oct 11 11:38:02 CDT 2006 Top

SQL Server >> SQL2005 Piecemeal Recovery The question I have is with read-write filegroups not read-only filegroups.

Can I restore a read-write file group to a point earlier than the restore of
the primary filegroup and other secondary filegroups?

Wendy





> Hi
>
> From BOL (http://msdn2.microsoft.com/en-us/library/ms177425.aspx)
>
> "Each filegroup-restore sequence restores and recovers one or more offline
> filegroups to a point consistent with the database"
>
> For Simple recovery:
> "While the database is online, you can use one or more online file restores
> to restore and recover offline read-only files that were read-only at the
> time of backup. The timing of the online file restores depends on when you
> want to have the data online.
> Whether you must restore data to a file depends on the following:
>
> Valid read-only files that are consistent with the database can be brought
> online directly by recovering them without restoring any data. "
>
> Similary for Full recovery
>
> "If a specific read-only file is undamaged and consistent with the database,
> the file does not have to be restored"
>
> HTH
>
> John
>
>

>
> > Hi,
> >
> > I have a question about SQL2005 piecemeal recovery.
> >
> > This gives the option to get the database online while corrupt
> > files/filesgroups remain offline. But when restoring these corrupt
> > read-write files/filesgroups, would they need to be rolled forward to the
> > same point-in-time that the primary filesgroup was restored?
> >
> > Does that mean that you couldn't leave a read-write filegroup offline during
> > a piecemeal restore and then afterwards restore this filegroup back to the
> > previous night's backup, even though the primary filegroup and other
> > secondary file groups were restored to the point the database was taken
> > offline? Would this flag up as leaving the database in an inconsistent state?
> >
> > So, if you have a corrupt read-write file/filegroup, do you need to restore
> > the whole database to the point just before the single file corruption???
> >
> > Thanks
> > Wendy
 
 
jbellnewsposts





PostPosted: Wed Oct 11 12:04:02 CDT 2006 Top

SQL Server >> SQL2005 Piecemeal Recovery Hi

The read-write files have to be consistent with the database ie all restored
to the same point in time as indicated by the first quote.

John



> The question I have is with read-write filegroups not read-only filegroups.
>
> Can I restore a read-write file group to a point earlier than the restore of
> the primary filegroup and other secondary filegroups?
>
> Wendy
>
>
>

>
> > Hi
> >
> > From BOL (http://msdn2.microsoft.com/en-us/library/ms177425.aspx)
> >
> > "Each filegroup-restore sequence restores and recovers one or more offline
> > filegroups to a point consistent with the database"
> >
> > For Simple recovery:
> > "While the database is online, you can use one or more online file restores
> > to restore and recover offline read-only files that were read-only at the
> > time of backup. The timing of the online file restores depends on when you
> > want to have the data online.
> > Whether you must restore data to a file depends on the following:
> >
> > Valid read-only files that are consistent with the database can be brought
> > online directly by recovering them without restoring any data. "
> >
> > Similary for Full recovery
> >
> > "If a specific read-only file is undamaged and consistent with the database,
> > the file does not have to be restored"
> >
> > HTH
> >
> > John
> >
> >

> >
> > > Hi,
> > >
> > > I have a question about SQL2005 piecemeal recovery.
> > >
> > > This gives the option to get the database online while corrupt
> > > files/filesgroups remain offline. But when restoring these corrupt
> > > read-write files/filesgroups, would they need to be rolled forward to the
> > > same point-in-time that the primary filesgroup was restored?
> > >
> > > Does that mean that you couldn't leave a read-write filegroup offline during
> > > a piecemeal restore and then afterwards restore this filegroup back to the
> > > previous night's backup, even though the primary filegroup and other
> > > secondary file groups were restored to the point the database was taken
> > > offline? Would this flag up as leaving the database in an inconsistent state?
> > >
> > > So, if you have a corrupt read-write file/filegroup, do you need to restore
> > > the whole database to the point just before the single file corruption???
> > >
> > > Thanks
> > > Wendy
 
 
Tibor





PostPosted: Wed Oct 11 12:07:55 CDT 2006 Top

SQL Server >> SQL2005 Piecemeal Recovery > Can I restore a read-write file group to a point earlier than the restore of
> the primary filegroup and other secondary filegroups?

To the best of my knowledge: No.

SQL Server has never let you get an inconsistent database. What you have to do is restore that
filegroup from an old backup and then re-apply your transaction log backups up to current time.

I.e., this protects you from physical problems
Not "oh, I deleted all rows in the customers table" problems.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/




> The question I have is with read-write filegroups not read-only filegroups.
>
> Can I restore a read-write file group to a point earlier than the restore of
> the primary filegroup and other secondary filegroups?
>
> Wendy
>
>
>

>
>> Hi
>>
>> From BOL (http://msdn2.microsoft.com/en-us/library/ms177425.aspx)
>>
>> "Each filegroup-restore sequence restores and recovers one or more offline
>> filegroups to a point consistent with the database"
>>
>> For Simple recovery:
>> "While the database is online, you can use one or more online file restores
>> to restore and recover offline read-only files that were read-only at the
>> time of backup. The timing of the online file restores depends on when you
>> want to have the data online.
>> Whether you must restore data to a file depends on the following:
>>
>> Valid read-only files that are consistent with the database can be brought
>> online directly by recovering them without restoring any data. "
>>
>> Similary for Full recovery
>>
>> "If a specific read-only file is undamaged and consistent with the database,
>> the file does not have to be restored"
>>
>> HTH
>>
>> John
>>
>>

>>
>> > Hi,
>> >
>> > I have a question about SQL2005 piecemeal recovery.
>> >
>> > This gives the option to get the database online while corrupt
>> > files/filesgroups remain offline. But when restoring these corrupt
>> > read-write files/filesgroups, would they need to be rolled forward to the
>> > same point-in-time that the primary filesgroup was restored?
>> >
>> > Does that mean that you couldn't leave a read-write filegroup offline during
>> > a piecemeal restore and then afterwards restore this filegroup back to the
>> > previous night's backup, even though the primary filegroup and other
>> > secondary file groups were restored to the point the database was taken
>> > offline? Would this flag up as leaving the database in an inconsistent state?
>> >
>> > So, if you have a corrupt read-write file/filegroup, do you need to restore
>> > the whole database to the point just before the single file corruption???
>> >
>> > Thanks
>> > Wendy

 
 
Woo





PostPosted: Thu Oct 12 01:14:02 CDT 2006 Top

SQL Server >> SQL2005 Piecemeal Recovery Thanks, thats what I thought.




> Hi
>
> The read-write files have to be consistent with the database ie all restored
> to the same point in time as indicated by the first quote.
>
> John
>

>
> > The question I have is with read-write filegroups not read-only filegroups.
> >
> > Can I restore a read-write file group to a point earlier than the restore of
> > the primary filegroup and other secondary filegroups?
> >
> > Wendy
> >
> >
> >

> >
> > > Hi
> > >
> > > From BOL (http://msdn2.microsoft.com/en-us/library/ms177425.aspx)
> > >
> > > "Each filegroup-restore sequence restores and recovers one or more offline
> > > filegroups to a point consistent with the database"
> > >
> > > For Simple recovery:
> > > "While the database is online, you can use one or more online file restores
> > > to restore and recover offline read-only files that were read-only at the
> > > time of backup. The timing of the online file restores depends on when you
> > > want to have the data online.
> > > Whether you must restore data to a file depends on the following:
> > >
> > > Valid read-only files that are consistent with the database can be brought
> > > online directly by recovering them without restoring any data. "
> > >
> > > Similary for Full recovery
> > >
> > > "If a specific read-only file is undamaged and consistent with the database,
> > > the file does not have to be restored"
> > >
> > > HTH
> > >
> > > John
> > >
> > >

> > >
> > > > Hi,
> > > >
> > > > I have a question about SQL2005 piecemeal recovery.
> > > >
> > > > This gives the option to get the database online while corrupt
> > > > files/filesgroups remain offline. But when restoring these corrupt
> > > > read-write files/filesgroups, would they need to be rolled forward to the
> > > > same point-in-time that the primary filesgroup was restored?
> > > >
> > > > Does that mean that you couldn't leave a read-write filegroup offline during
> > > > a piecemeal restore and then afterwards restore this filegroup back to the
> > > > previous night's backup, even though the primary filegroup and other
> > > > secondary file groups were restored to the point the database was taken
> > > > offline? Would this flag up as leaving the database in an inconsistent state?
> > > >
> > > > So, if you have a corrupt read-write file/filegroup, do you need to restore
> > > > the whole database to the point just before the single file corruption???
> > > >
> > > > Thanks
> > > > Wendy
 
 
Woo





PostPosted: Thu Oct 12 01:15:02 CDT 2006 Top

SQL Server >> SQL2005 Piecemeal Recovery Thanks, thats what I thought.




> > Can I restore a read-write file group to a point earlier than the restore of
> > the primary filegroup and other secondary filegroups?
>
> To the best of my knowledge: No.
>
> SQL Server has never let you get an inconsistent database. What you have to do is restore that
> filegroup from an old backup and then re-apply your transaction log backups up to current time.
>
> I.e., this protects you from physical problems
> Not "oh, I deleted all rows in the customers table" problems.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
>


> > The question I have is with read-write filegroups not read-only filegroups.
> >
> > Can I restore a read-write file group to a point earlier than the restore of
> > the primary filegroup and other secondary filegroups?
> >
> > Wendy
> >
> >
> >

> >
> >> Hi
> >>
> >> From BOL (http://msdn2.microsoft.com/en-us/library/ms177425.aspx)
> >>
> >> "Each filegroup-restore sequence restores and recovers one or more offline
> >> filegroups to a point consistent with the database"
> >>
> >> For Simple recovery:
> >> "While the database is online, you can use one or more online file restores
> >> to restore and recover offline read-only files that were read-only at the
> >> time of backup. The timing of the online file restores depends on when you
> >> want to have the data online.
> >> Whether you must restore data to a file depends on the following:
> >>
> >> Valid read-only files that are consistent with the database can be brought
> >> online directly by recovering them without restoring any data. "
> >>
> >> Similary for Full recovery
> >>
> >> "If a specific read-only file is undamaged and consistent with the database,
> >> the file does not have to be restored"
> >>
> >> HTH
> >>
> >> John
> >>
> >>

> >>
> >> > Hi,
> >> >
> >> > I have a question about SQL2005 piecemeal recovery.
> >> >
> >> > This gives the option to get the database online while corrupt
> >> > files/filesgroups remain offline. But when restoring these corrupt
> >> > read-write files/filesgroups, would they need to be rolled forward to the
> >> > same point-in-time that the primary filesgroup was restored?
> >> >
> >> > Does that mean that you couldn't leave a read-write filegroup offline during
> >> > a piecemeal restore and then afterwards restore this filegroup back to the
> >> > previous night's backup, even though the primary filegroup and other
> >> > secondary file groups were restored to the point the database was taken
> >> > offline? Would this flag up as leaving the database in an inconsistent state?
> >> >
> >> > So, if you have a corrupt read-write file/filegroup, do you need to restore
> >> > the whole database to the point just before the single file corruption???
> >> >
> >> > Thanks
> >> > Wendy
>
>