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

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.

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.

Recovery Model code in SQL 2000

In SQL 2005, sys.databases has a column named recovery_model that stores a code for the type of recovery model used by the database. Where is the recovery_model column in the SQL 2000 master database?

Thanks,

Craig

Code Snippet

select name, Databasepropertyex(name, 'Recovery') recovery from master..sysdatabases

sql

Recordset destination used in a FOREACH?

Hi all,

Can a Recordset destination be used as source for a ForEach loop.

Correct me if i'm wrong but the Recordset is stored in a variable of type Object? So what stops my ForEach loop from itterating?

Regards,

Pieter

Well, there are about 70-80 examples of using a recordset as a foreach enumerator (source) between BOL, google groups, and this MSDN forum. Shorter answer, yes.

Here's a good example from Jamie Thompson, http://blogs.conchango.com/jamiethomson/archive/2005/07/04/1748.aspx, which not only has verbiage, but a .dtsx file as well.

|||Thanks for the reply. After my post i tried a test project and it worked 100%. I then tried again in my actual project and no luck. Since then I rebuilt my entire work project around the test project and it is still working....go figure

Recordset destination used in a FOREACH?

Hi all,

Can a Recordset destination be used as source for a ForEach loop.

Correct me if i'm wrong but the Recordset is stored in a variable of type Object? So what stops my ForEach loop from itterating?

Regards,

Pieter

Well, there are about 70-80 examples of using a recordset as a foreach enumerator (source) between BOL, google groups, and this MSDN forum. Shorter answer, yes.

Here's a good example from Jamie Thompson, http://blogs.conchango.com/jamiethomson/archive/2005/07/04/1748.aspx, which not only has verbiage, but a .dtsx file as well.

|||Thanks for the reply. After my post i tried a test project and it worked 100%. I then tried again in my actual project and no luck. Since then I rebuilt my entire work project around the test project and it is still working....go figure

Recording the time

Can anyone tell me if there is a data type used in oracle for recording the time of an event. The SQL books I have read mention the TIME data type but this is not recognised in oracle.
I have tried the timestamp but I require the time and date in separate columns?At least in my opinion, storing the time and date in different columns is an extraordinarily bad idea. They are both parts of the same thing (a temporal data type), and storing them as two columns makes as much sense as storing money as dollars in one int and the cents in another int... It certainly can be done, but it is a fundamentally bad idea.

If you insist on doing things the hard way, Oracle will gladly let you. Create two separate timestamp columns. Store the date in one of the columns, and the time in the other. Or store both of them in one column, and use the time manipulation functions to extract just the time or date when you only need one of them.

-PatP|||what is the time of an event?

do you mean "this event lasts for two hours and fifteen minutes"?

or do you mean "this event occurs at 3:30 pm every day"?

if the former, use an INTERVAL

if the latter, use DATE (which stores date and time) and ignore the date portion

recording query

Hello,

How can I record certain type of querys ? I would like to record
delete command sent to a specific database, and using a specific
login account.

This "query capture" should run in background because I dont know
the exact time someone will delete a record.

Im using SQL-Server 7.0

Thank you,

Eduardo.eakinto@.buscape.com.br (Eduardo) wrote in message news:<693f7309.0404071351.dc027a@.posting.google.com>...
> Hello,
> How can I record certain type of querys ? I would like to record
> delete command sent to a specific database, and using a specific
> login account.
> This "query capture" should run in background because I dont know
> the exact time someone will delete a record.
> Im using SQL-Server 7.0
> Thank you,
> Eduardo.

Profiler would be the first place to look, or perhaps a trace.

Simon