Does anyone have a lead on where I can find out what Back Up Type to use with
which Recovery Model? Something like "standard combinations" or a matrix
that has both elements but how each interacts with each other?
If I pick a Recovery Model and do not perform the correct Back, the database
will not be restored correctly.Rich,
For a quick overview, check
http://www.databasejournal.com/features/mssql/article.php/2232371.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:0F62E5D6-9DD0-4160-98BB-A4F3ED174CEB@.microsoft.com...
> Does anyone have a lead on where I can find out what Back Up Type to use
with
> which Recovery Model? Something like "standard combinations" or a matrix
> that has both elements but how each interacts with each other?
> If I pick a Recovery Model and do not perform the correct Back, the
database
> will not be restored correctly.|||Very brief overview:
Simple
--
Full and differential backups only.
Cannot restore to a point in time, only since last backup.
Bulk-logged
--
Similar to Full, except minimally-logged operations are allowed.
Cannot recover to a point in time if a minimally logged operation has
occurred. Good for large batch processes with bcp, or select..into.
Full
--
Everything is logged including bulk loads. Bulk load operations will be
slower in full, than bulk-logged.
Can restore to a point in time if the transaction log can be backed up
after the failure. Easy to switch to bulk-logged mode.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Rich wrote:
> Does anyone have a lead on where I can find out what Back Up Type to use with
> which Recovery Model? Something like "standard combinations" or a matrix
> that has both elements but how each interacts with each other?
> If I pick a Recovery Model and do not perform the correct Back, the database
> will not be restored correctly.|||Although I agree, a little more refinement for the BULK LOGGED Recovery
Model:
Although you can not restore to a point-in-time, you can back up and restore
transaction logs, where you can restore to any point-in-time prior to the
Bulk Logged information or to the end of any transaction log backup after
the Bulk Logged operation in between FULL and/or Differential backups.
So, if you'd like point-in-time recovery, perform at least a Differential
Backup just after a Bulk Logged operation.
Sincerely,
Anthony Thomas
"Mark Allison" <mark@.no.tinned.meat.mvps.org> wrote in message
news:e7euNGQCFHA.2032@.tk2msftngp13.phx.gbl...
Very brief overview:
Simple
--
Full and differential backups only.
Cannot restore to a point in time, only since last backup.
Bulk-logged
--
Similar to Full, except minimally-logged operations are allowed.
Cannot recover to a point in time if a minimally logged operation has
occurred. Good for large batch processes with bcp, or select..into.
Full
--
Everything is logged including bulk loads. Bulk load operations will be
slower in full, than bulk-logged.
Can restore to a point in time if the transaction log can be backed up
after the failure. Easy to switch to bulk-logged mode.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Rich wrote:
> Does anyone have a lead on where I can find out what Back Up Type to use
with
> which Recovery Model? Something like "standard combinations" or a matrix
> that has both elements but how each interacts with each other?
> If I pick a Recovery Model and do not perform the correct Back, the
database
> will not be restored correctly.
Showing posts with label standard. Show all posts
Showing posts with label standard. Show all posts
Friday, March 30, 2012
Recovery Model VS Back Up Types
Does anyone have a lead on where I can find out what Back Up Type to use with
which Recovery Model? Something like "standard combinations" or a matrix
that has both elements but how each interacts with each other?
If I pick a Recovery Model and do not perform the correct Back, the database
will not be restored correctly.
Rich,
For a quick overview, check
http://www.databasejournal.com/featu...e.php/2232371.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:0F62E5D6-9DD0-4160-98BB-A4F3ED174CEB@.microsoft.com...
> Does anyone have a lead on where I can find out what Back Up Type to use
with
> which Recovery Model? Something like "standard combinations" or a matrix
> that has both elements but how each interacts with each other?
> If I pick a Recovery Model and do not perform the correct Back, the
database
> will not be restored correctly.
|||Very brief overview:
Simple
Full and differential backups only.
Cannot restore to a point in time, only since last backup.
Bulk-logged
Similar to Full, except minimally-logged operations are allowed.
Cannot recover to a point in time if a minimally logged operation has
occurred. Good for large batch processes with bcp, or select..into.
Full
Everything is logged including bulk loads. Bulk load operations will be
slower in full, than bulk-logged.
Can restore to a point in time if the transaction log can be backed up
after the failure. Easy to switch to bulk-logged mode.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Rich wrote:
> Does anyone have a lead on where I can find out what Back Up Type to use with
> which Recovery Model? Something like "standard combinations" or a matrix
> that has both elements but how each interacts with each other?
> If I pick a Recovery Model and do not perform the correct Back, the database
> will not be restored correctly.
|||Although I agree, a little more refinement for the BULK LOGGED Recovery
Model:
Although you can not restore to a point-in-time, you can back up and restore
transaction logs, where you can restore to any point-in-time prior to the
Bulk Logged information or to the end of any transaction log backup after
the Bulk Logged operation in between FULL and/or Differential backups.
So, if you'd like point-in-time recovery, perform at least a Differential
Backup just after a Bulk Logged operation.
Sincerely,
Anthony Thomas
"Mark Allison" <mark@.no.tinned.meat.mvps.org> wrote in message
news:e7euNGQCFHA.2032@.tk2msftngp13.phx.gbl...
Very brief overview:
Simple
Full and differential backups only.
Cannot restore to a point in time, only since last backup.
Bulk-logged
Similar to Full, except minimally-logged operations are allowed.
Cannot recover to a point in time if a minimally logged operation has
occurred. Good for large batch processes with bcp, or select..into.
Full
Everything is logged including bulk loads. Bulk load operations will be
slower in full, than bulk-logged.
Can restore to a point in time if the transaction log can be backed up
after the failure. Easy to switch to bulk-logged mode.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Rich wrote:
> Does anyone have a lead on where I can find out what Back Up Type to use
with
> which Recovery Model? Something like "standard combinations" or a matrix
> that has both elements but how each interacts with each other?
> If I pick a Recovery Model and do not perform the correct Back, the
database
> will not be restored correctly.
which Recovery Model? Something like "standard combinations" or a matrix
that has both elements but how each interacts with each other?
If I pick a Recovery Model and do not perform the correct Back, the database
will not be restored correctly.
Rich,
For a quick overview, check
http://www.databasejournal.com/featu...e.php/2232371.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:0F62E5D6-9DD0-4160-98BB-A4F3ED174CEB@.microsoft.com...
> Does anyone have a lead on where I can find out what Back Up Type to use
with
> which Recovery Model? Something like "standard combinations" or a matrix
> that has both elements but how each interacts with each other?
> If I pick a Recovery Model and do not perform the correct Back, the
database
> will not be restored correctly.
|||Very brief overview:
Simple
Full and differential backups only.
Cannot restore to a point in time, only since last backup.
Bulk-logged
Similar to Full, except minimally-logged operations are allowed.
Cannot recover to a point in time if a minimally logged operation has
occurred. Good for large batch processes with bcp, or select..into.
Full
Everything is logged including bulk loads. Bulk load operations will be
slower in full, than bulk-logged.
Can restore to a point in time if the transaction log can be backed up
after the failure. Easy to switch to bulk-logged mode.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Rich wrote:
> Does anyone have a lead on where I can find out what Back Up Type to use with
> which Recovery Model? Something like "standard combinations" or a matrix
> that has both elements but how each interacts with each other?
> If I pick a Recovery Model and do not perform the correct Back, the database
> will not be restored correctly.
|||Although I agree, a little more refinement for the BULK LOGGED Recovery
Model:
Although you can not restore to a point-in-time, you can back up and restore
transaction logs, where you can restore to any point-in-time prior to the
Bulk Logged information or to the end of any transaction log backup after
the Bulk Logged operation in between FULL and/or Differential backups.
So, if you'd like point-in-time recovery, perform at least a Differential
Backup just after a Bulk Logged operation.
Sincerely,
Anthony Thomas
"Mark Allison" <mark@.no.tinned.meat.mvps.org> wrote in message
news:e7euNGQCFHA.2032@.tk2msftngp13.phx.gbl...
Very brief overview:
Simple
Full and differential backups only.
Cannot restore to a point in time, only since last backup.
Bulk-logged
Similar to Full, except minimally-logged operations are allowed.
Cannot recover to a point in time if a minimally logged operation has
occurred. Good for large batch processes with bcp, or select..into.
Full
Everything is logged including bulk loads. Bulk load operations will be
slower in full, than bulk-logged.
Can restore to a point in time if the transaction log can be backed up
after the failure. Easy to switch to bulk-logged mode.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Rich wrote:
> Does anyone have a lead on where I can find out what Back Up Type to use
with
> which Recovery Model? Something like "standard combinations" or a matrix
> that has both elements but how each interacts with each other?
> If I pick a Recovery Model and do not perform the correct Back, the
database
> will not be restored correctly.
Recovery Model VS Back Up Types
Does anyone have a lead on where I can find out what Back Up Type to use wit
h
which Recovery Model? Something like "standard combinations" or a matrix
that has both elements but how each interacts with each other?
If I pick a Recovery Model and do not perform the correct Back, the database
will not be restored correctly.Rich,
For a quick overview, check
http://www.databasejournal.com/feat...le.php/2232371.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:0F62E5D6-9DD0-4160-98BB-A4F3ED174CEB@.microsoft.com...
> Does anyone have a lead on where I can find out what Back Up Type to use
with
> which Recovery Model? Something like "standard combinations" or a matrix
> that has both elements but how each interacts with each other?
> If I pick a Recovery Model and do not perform the correct Back, the
database
> will not be restored correctly.|||Very brief overview:
Simple
--
Full and differential backups only.
Cannot restore to a point in time, only since last backup.
Bulk-logged
--
Similar to Full, except minimally-logged operations are allowed.
Cannot recover to a point in time if a minimally logged operation has
occurred. Good for large batch processes with bcp, or select..into.
Full
--
Everything is logged including bulk loads. Bulk load operations will be
slower in full, than bulk-logged.
Can restore to a point in time if the transaction log can be backed up
after the failure. Easy to switch to bulk-logged mode.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Rich wrote:
> Does anyone have a lead on where I can find out what Back Up Type to use w
ith
> which Recovery Model? Something like "standard combinations" or a matrix
> that has both elements but how each interacts with each other?
> If I pick a Recovery Model and do not perform the correct Back, the databa
se
> will not be restored correctly.|||Although I agree, a little more refinement for the BULK LOGGED Recovery
Model:
Although you can not restore to a point-in-time, you can back up and restore
transaction logs, where you can restore to any point-in-time prior to the
Bulk Logged information or to the end of any transaction log backup after
the Bulk Logged operation in between FULL and/or Differential backups.
So, if you'd like point-in-time recovery, perform at least a Differential
Backup just after a Bulk Logged operation.
Sincerely,
Anthony Thomas
"Mark Allison" <mark@.no.tinned.meat.mvps.org> wrote in message
news:e7euNGQCFHA.2032@.tk2msftngp13.phx.gbl...
Very brief overview:
Simple
--
Full and differential backups only.
Cannot restore to a point in time, only since last backup.
Bulk-logged
--
Similar to Full, except minimally-logged operations are allowed.
Cannot recover to a point in time if a minimally logged operation has
occurred. Good for large batch processes with bcp, or select..into.
Full
--
Everything is logged including bulk loads. Bulk load operations will be
slower in full, than bulk-logged.
Can restore to a point in time if the transaction log can be backed up
after the failure. Easy to switch to bulk-logged mode.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Rich wrote:
> Does anyone have a lead on where I can find out what Back Up Type to use
with
> which Recovery Model? Something like "standard combinations" or a matrix
> that has both elements but how each interacts with each other?
> If I pick a Recovery Model and do not perform the correct Back, the
database
> will not be restored correctly.
h
which Recovery Model? Something like "standard combinations" or a matrix
that has both elements but how each interacts with each other?
If I pick a Recovery Model and do not perform the correct Back, the database
will not be restored correctly.Rich,
For a quick overview, check
http://www.databasejournal.com/feat...le.php/2232371.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:0F62E5D6-9DD0-4160-98BB-A4F3ED174CEB@.microsoft.com...
> Does anyone have a lead on where I can find out what Back Up Type to use
with
> which Recovery Model? Something like "standard combinations" or a matrix
> that has both elements but how each interacts with each other?
> If I pick a Recovery Model and do not perform the correct Back, the
database
> will not be restored correctly.|||Very brief overview:
Simple
--
Full and differential backups only.
Cannot restore to a point in time, only since last backup.
Bulk-logged
--
Similar to Full, except minimally-logged operations are allowed.
Cannot recover to a point in time if a minimally logged operation has
occurred. Good for large batch processes with bcp, or select..into.
Full
--
Everything is logged including bulk loads. Bulk load operations will be
slower in full, than bulk-logged.
Can restore to a point in time if the transaction log can be backed up
after the failure. Easy to switch to bulk-logged mode.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Rich wrote:
> Does anyone have a lead on where I can find out what Back Up Type to use w
ith
> which Recovery Model? Something like "standard combinations" or a matrix
> that has both elements but how each interacts with each other?
> If I pick a Recovery Model and do not perform the correct Back, the databa
se
> will not be restored correctly.|||Although I agree, a little more refinement for the BULK LOGGED Recovery
Model:
Although you can not restore to a point-in-time, you can back up and restore
transaction logs, where you can restore to any point-in-time prior to the
Bulk Logged information or to the end of any transaction log backup after
the Bulk Logged operation in between FULL and/or Differential backups.
So, if you'd like point-in-time recovery, perform at least a Differential
Backup just after a Bulk Logged operation.
Sincerely,
Anthony Thomas
"Mark Allison" <mark@.no.tinned.meat.mvps.org> wrote in message
news:e7euNGQCFHA.2032@.tk2msftngp13.phx.gbl...
Very brief overview:
Simple
--
Full and differential backups only.
Cannot restore to a point in time, only since last backup.
Bulk-logged
--
Similar to Full, except minimally-logged operations are allowed.
Cannot recover to a point in time if a minimally logged operation has
occurred. Good for large batch processes with bcp, or select..into.
Full
--
Everything is logged including bulk loads. Bulk load operations will be
slower in full, than bulk-logged.
Can restore to a point in time if the transaction log can be backed up
after the failure. Easy to switch to bulk-logged mode.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Rich wrote:
> Does anyone have a lead on where I can find out what Back Up Type to use
with
> which Recovery Model? Something like "standard combinations" or a matrix
> that has both elements but how each interacts with each other?
> If I pick a Recovery Model and do not perform the correct Back, the
database
> will not be restored correctly.
Monday, March 12, 2012
Recover database with multiple filegroups?
Hi,
I'm running SQL2000 Standard on W2K Server.
I have a large database that spreads multiple physical drives and file
groups. The database failed earlier today because one of the filegroups is
bad. This filegroup is located on a bad drive and the database is now in
(suspect) mode, complaining about torn page I/O error.
I'm wondering if it's possible to DELETE this one filegroup from the
database and bring the database back online with other remaining filegroups.
Because this filegroup is bad and is causing the entire database to be in
Suspect Mode. Or is it possible to re-create a new database by attaching all
good filegroups back?
What are my options for bringing this database back online without having to
trash all the data and reload them? It'd be nice if I only lose this one
filegroup.
Thanks!
HHHi
If I understood you correctly you need to use DBCC SHRINKFILE with EMPTYFILE
argument.
For more details please refer to BOL.
"Hung Huynh" <hungh@.wi.rr.com> wrote in message
news:er8g2TSjDHA.360@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I'm running SQL2000 Standard on W2K Server.
> I have a large database that spreads multiple physical drives and file
> groups. The database failed earlier today because one of the filegroups is
> bad. This filegroup is located on a bad drive and the database is now in
> (suspect) mode, complaining about torn page I/O error.
> I'm wondering if it's possible to DELETE this one filegroup from the
> database and bring the database back online with other remaining
filegroups.
> Because this filegroup is bad and is causing the entire database to be in
> Suspect Mode. Or is it possible to re-create a new database by attaching
all
> good filegroups back?
> What are my options for bringing this database back online without having
to
> trash all the data and reload them? It'd be nice if I only lose this one
> filegroup.
> Thanks!
> HH
>|||No. The database is not functional right now. It's in Suspect Mode and need
to be restored/recovered.
Thanks!
HH
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ufLhsQWjDHA.2652@.TK2MSFTNGP09.phx.gbl...
> Hi
> If I understood you correctly you need to use DBCC SHRINKFILE with
EMPTYFILE
> argument.
> For more details please refer to BOL.
>
>
> "Hung Huynh" <hungh@.wi.rr.com> wrote in message
> news:er8g2TSjDHA.360@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > I'm running SQL2000 Standard on W2K Server.
> >
> > I have a large database that spreads multiple physical drives and file
> > groups. The database failed earlier today because one of the filegroups
is
> > bad. This filegroup is located on a bad drive and the database is now in
> > (suspect) mode, complaining about torn page I/O error.
> >
> > I'm wondering if it's possible to DELETE this one filegroup from the
> > database and bring the database back online with other remaining
> filegroups.
> > Because this filegroup is bad and is causing the entire database to be
in
> > Suspect Mode. Or is it possible to re-create a new database by attaching
> all
> > good filegroups back?
> >
> > What are my options for bringing this database back online without
having
> to
> > trash all the data and reload them? It'd be nice if I only lose this one
> > filegroup.
> >
> > Thanks!
> >
> > HH
> >
> >
>
I'm running SQL2000 Standard on W2K Server.
I have a large database that spreads multiple physical drives and file
groups. The database failed earlier today because one of the filegroups is
bad. This filegroup is located on a bad drive and the database is now in
(suspect) mode, complaining about torn page I/O error.
I'm wondering if it's possible to DELETE this one filegroup from the
database and bring the database back online with other remaining filegroups.
Because this filegroup is bad and is causing the entire database to be in
Suspect Mode. Or is it possible to re-create a new database by attaching all
good filegroups back?
What are my options for bringing this database back online without having to
trash all the data and reload them? It'd be nice if I only lose this one
filegroup.
Thanks!
HHHi
If I understood you correctly you need to use DBCC SHRINKFILE with EMPTYFILE
argument.
For more details please refer to BOL.
"Hung Huynh" <hungh@.wi.rr.com> wrote in message
news:er8g2TSjDHA.360@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I'm running SQL2000 Standard on W2K Server.
> I have a large database that spreads multiple physical drives and file
> groups. The database failed earlier today because one of the filegroups is
> bad. This filegroup is located on a bad drive and the database is now in
> (suspect) mode, complaining about torn page I/O error.
> I'm wondering if it's possible to DELETE this one filegroup from the
> database and bring the database back online with other remaining
filegroups.
> Because this filegroup is bad and is causing the entire database to be in
> Suspect Mode. Or is it possible to re-create a new database by attaching
all
> good filegroups back?
> What are my options for bringing this database back online without having
to
> trash all the data and reload them? It'd be nice if I only lose this one
> filegroup.
> Thanks!
> HH
>|||No. The database is not functional right now. It's in Suspect Mode and need
to be restored/recovered.
Thanks!
HH
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ufLhsQWjDHA.2652@.TK2MSFTNGP09.phx.gbl...
> Hi
> If I understood you correctly you need to use DBCC SHRINKFILE with
EMPTYFILE
> argument.
> For more details please refer to BOL.
>
>
> "Hung Huynh" <hungh@.wi.rr.com> wrote in message
> news:er8g2TSjDHA.360@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > I'm running SQL2000 Standard on W2K Server.
> >
> > I have a large database that spreads multiple physical drives and file
> > groups. The database failed earlier today because one of the filegroups
is
> > bad. This filegroup is located on a bad drive and the database is now in
> > (suspect) mode, complaining about torn page I/O error.
> >
> > I'm wondering if it's possible to DELETE this one filegroup from the
> > database and bring the database back online with other remaining
> filegroups.
> > Because this filegroup is bad and is causing the entire database to be
in
> > Suspect Mode. Or is it possible to re-create a new database by attaching
> all
> > good filegroups back?
> >
> > What are my options for bringing this database back online without
having
> to
> > trash all the data and reload them? It'd be nice if I only lose this one
> > filegroup.
> >
> > Thanks!
> >
> > HH
> >
> >
>
Subscribe to:
Posts (Atom)