Hi,
If I am adding 3 columns of data type real in a select clause and if any one
of those columns is a null I get a null back instead of reslult of an
addition applied to non null columns. So I used isnull(field1,0) to convert
it to zero so I get some value back instead of a null. i.e.
select isnull(filed1,0)+isnull(field2,0)+isnull
(field3,0) From XYZ
This converts null values to zero and always get a result back even though
the result may be 0 if all values are null.
My question is what if I want to get a null back ONLY if all 3 fields have
value Null otherwise some number after applying addition.
ThanksRick,
Use a CASE expression.
Example:
select case when field1 is null and field2 is null and field3 is null then
null else isnull(filed1,0)+isnull(field2,0)+isnull
(field3,0) end as
col_result From XYZ
AMB
"Rick" wrote:
> Hi,
> If I am adding 3 columns of data type real in a select clause and if any o
ne
> of those columns is a null I get a null back instead of reslult of an
> addition applied to non null columns. So I used isnull(field1,0) to conver
t
> it to zero so I get some value back instead of a null. i.e.
> select isnull(filed1,0)+isnull(field2,0)+isnull
(field3,0) From XYZ
> This converts null values to zero and always get a result back even though
> the result may be 0 if all values are null.
> My question is what if I want to get a null back ONLY if all 3 fields have
> value Null otherwise some number after applying addition.
> Thanks
>|||This isn't probably the answer you want, but something you should think
about anyway.
Why do you ever want NULL for numeric values? Does it ever make
mathematical sense? We made a decision long ago to NEVER allow nulls for
numeric values. We always populate/default our numeric columns to the value
that makes most sense. So, for example, 'price' would always start at zero
rather than null. You will most likely find that if you build in default
values for all your numerics problems such as the one you are posing just
simply go away.
JIM
"Rick" <ricky.arora@.metc.state.mn.us> wrote in message
news:645FC638-123B-4E2F-8077-8A7A547EC503@.microsoft.com...
> Hi,
> If I am adding 3 columns of data type real in a select clause and if any
> one
> of those columns is a null I get a null back instead of reslult of an
> addition applied to non null columns. So I used isnull(field1,0) to
> convert
> it to zero so I get some value back instead of a null. i.e.
> select isnull(filed1,0)+isnull(field2,0)+isnull
(field3,0) From XYZ
> This converts null values to zero and always get a result back even though
> the result may be 0 if all values are null.
> My question is what if I want to get a null back ONLY if all 3 fields have
> value Null otherwise some number after applying addition.
> Thanks
>|||Thank You Alejandro.
"Alejandro Mesa" wrote:
> Rick,
> Use a CASE expression.
> Example:
> select case when field1 is null and field2 is null and field3 is null then
> null else isnull(filed1,0)+isnull(field2,0)+isnull
(field3,0) end as
> col_result From XYZ
>
> AMB
> "Rick" wrote:
>|||Hi James
You are right. What you said makes sense in the retail business for example.
I am in the wastewater industry. I can't just create zeros and average them
for the runtime of an equipment say a motor pump flow. If I get a value Zero
from pump reading only then I will average it otherwise I have to deal with
nulls and nulls are not counted in aggregate functions.
Rick
"james" wrote:
> This isn't probably the answer you want, but something you should think
> about anyway.
> Why do you ever want NULL for numeric values? Does it ever make
> mathematical sense? We made a decision long ago to NEVER allow nulls for
> numeric values. We always populate/default our numeric columns to the val
ue
> that makes most sense. So, for example, 'price' would always start at zer
o
> rather than null. You will most likely find that if you build in default
> values for all your numerics problems such as the one you are posing just
> simply go away.
> JIM
>
> "Rick" <ricky.arora@.metc.state.mn.us> wrote in message
> news:645FC638-123B-4E2F-8077-8A7A547EC503@.microsoft.com...
>
>|||Hmm, not sure I aggree, but then I'm not the expert. Let me try though
I have 3 pumps. Pump one pumps 100gpm, P2 50 gpm and P3 isn't turned on. I
would argue that the average still includes P3, and that is is pumping zero
gpm, not NUL gpm
I'd like to see your logic/formula - but then I probably wouldn't understand
it anyway ;-)
JIM
"Rick" <ricky.arora@.metc.state.mn.us> wrote in message
news:4D91013B-A1A4-42B9-8D89-9EF78ACA6FE9@.microsoft.com...
> Hi James
> You are right. What you said makes sense in the retail business for
> example.
> I am in the wastewater industry. I can't just create zeros and average
> them
> for the runtime of an equipment say a motor pump flow. If I get a value
> Zero
> from pump reading only then I will average it otherwise I have to deal
> with
> nulls and nulls are not counted in aggregate functions.
> Rick
> "james" wrote:
>|||Rick,
I just posted my other reply, and then it occurred to me what the problem
might be. I bet you are using a de-normalized table. And your pumps are
columns in one table, rather than having a pumps table. That is why you
have nulls in your columns.
How off the mark am I?
JIM
"Rick" <ricky.arora@.metc.state.mn.us> wrote in message
news:4D91013B-A1A4-42B9-8D89-9EF78ACA6FE9@.microsoft.com...
> Hi James
> You are right. What you said makes sense in the retail business for
> example.
> I am in the wastewater industry. I can't just create zeros and average
> them
> for the runtime of an equipment say a motor pump flow. If I get a value
> Zero
> from pump reading only then I will average it otherwise I have to deal
> with
> nulls and nulls are not counted in aggregate functions.
> Rick
> "james" wrote:
>|||James,
You assumed that if the pump if off the value is Zero. But thats not the
case all the time. Some times instruments read bogus value of say 2 gpm
(gallons per minute) but you don't want to count this value in an avergae
neither you want a zero.
So I am applying a range condition saying if the pump value is in between
certain range I don't want it in an average. But if it is a zer then count i
t.
Ricky
"james" wrote:
> Rick,
> I just posted my other reply, and then it occurred to me what the problem
> might be. I bet you are using a de-normalized table. And your pumps are
> columns in one table, rather than having a pumps table. That is why you
> have nulls in your columns.
> How off the mark am I?
> JIM
>
> "Rick" <ricky.arora@.metc.state.mn.us> wrote in message
> news:4D91013B-A1A4-42B9-8D89-9EF78ACA6FE9@.microsoft.com...
>
>
Showing posts with label back. Show all posts
Showing posts with label back. Show all posts
Tuesday, March 20, 2012
Monday, March 12, 2012
Question about Delete and Latency
I am in the process of returning a machine running SQL Server back to
our provider. However, I don't want them to retrieve any of our data
stored in the DB. So I have the following 2 options
a) delete the rows from tables
b) remove the MDB file
Which of the options is better?
If I just delete the rows, will the SQL Server delete them from the
MDB file immediately?
If I remove the MDB file, can anyone put it back?
For instance, the Exchange Server use SQL Server and deleting a
mailbox will retain the data for about 14 days. Is there a similar
provision in SQL Server that retains the data. I don't want our
provider to retrieve any of the data.
Many thanks for reading and looking forward to repliesOn 18.05.2007 15:40, soup_or_power@.yahoo.com wrote:
> I am in the process of returning a machine running SQL Server back to
> our provider. However, I don't want them to retrieve any of our data
> stored in the DB. So I have the following 2 options
> a) delete the rows from tables
> b) remove the MDB file
> Which of the options is better?
> If I just delete the rows, will the SQL Server delete them from the
> MDB file immediately?
> If I remove the MDB file, can anyone put it back?
> For instance, the Exchange Server use SQL Server and deleting a
> mailbox will retain the data for about 14 days. Is there a similar
> provision in SQL Server that retains the data. I don't want our
> provider to retrieve any of the data.
> Many thanks for reading and looking forward to replies
Depends in what state you have to give the machine back. If you don't
need to care for OS then the most thorough and simple is probably to
boot the machine using Knoppix or a similar CD/DVD distro and use dd
if=/dev/zero of=/dev/hda (for all disks) to erase all your hard disks.
Other than that there are special tools for safely erasing data, i.e.
you could overwrite your mdf and ldf files with zeros after you
deactivated your DB and before you drop the DB.
Kind regards
robert|||On May 18, 9:04 am, Robert Klemme <shortcut...@.googlemail.com> wrote:
> On 18.05.2007 15:40, soup_or_po...@.yahoo.com wrote:
>
>
> > I am in the process of returning a machine running SQL Server back to
> > our provider. However, I don't want them to retrieve any of our data
> > stored in the DB. So I have the following 2 options
> > a) delete the rows from tables
> > b) remove the MDB file
> > Which of the options is better?
> > If I just delete the rows, will the SQL Server delete them from the
> > MDB file immediately?
> > If I remove the MDB file, can anyone put it back?
> > For instance, the Exchange Server use SQL Server and deleting a
> > mailbox will retain the data for about 14 days. Is there a similar
> > provision in SQL Server that retains the data. I don't want our
> > provider to retrieve any of the data.
> > Many thanks for reading and looking forward to replies
> Depends in what state you have to give the machine back. If you don't
> need to care for OS then the most thorough and simple is probably to
> boot the machine using Knoppix or a similar CD/DVD distro and use dd
> if=/dev/zero of=/dev/hda (for all disks) to erase all your hard disks.
> Other than that there are special tools for safely erasing data, i.e.
> you could overwrite your mdf and ldf files with zeros after you
> deactivated your DB and before you drop the DB.
> Kind regards
> robert- Hide quoted text -
> - Show quoted text -
Hi Robert
Many thanks for your reply. Could you name the tools for safely
erasing data? Also how can I "deactive" the DB?
Regards|||On 18.05.2007 16:22, soup_or_power@.yahoo.com wrote:
> On May 18, 9:04 am, Robert Klemme <shortcut...@.googlemail.com> wrote:
>> On 18.05.2007 15:40, soup_or_po...@.yahoo.com wrote:
>>
>>
>> I am in the process of returning a machine running SQL Server back to
>> our provider. However, I don't want them to retrieve any of our data
>> stored in the DB. So I have the following 2 options
>> a) delete the rows from tables
>> b) remove the MDB file
>> Which of the options is better?
>> If I just delete the rows, will the SQL Server delete them from the
>> MDB file immediately?
>> If I remove the MDB file, can anyone put it back?
>> For instance, the Exchange Server use SQL Server and deleting a
>> mailbox will retain the data for about 14 days. Is there a similar
>> provision in SQL Server that retains the data. I don't want our
>> provider to retrieve any of the data.
>> Many thanks for reading and looking forward to replies
>> Depends in what state you have to give the machine back. If you don't
>> need to care for OS then the most thorough and simple is probably to
>> boot the machine using Knoppix or a similar CD/DVD distro and use dd
>> if=/dev/zero of=/dev/hda (for all disks) to erase all your hard disks.
>> Other than that there are special tools for safely erasing data, i.e.
>> you could overwrite your mdf and ldf files with zeros after you
>> deactivated your DB and before you drop the DB.
>> Kind regards
>> robert- Hide quoted text -
>> - Show quoted text -
> Hi Robert
> Many thanks for your reply. Could you name the tools for safely
> erasing data?
I don't have names. You will have to look for yourself. Sorry.
> Also how can I "deactive" the DB?
EM -> select DB -> all tasks -> detach database
robert|||"Robert Klemme" <shortcutter@.googlemail.com> wrote in message
news:5b5s08F2g0fupU1@.mid.individual.net...
> On 18.05.2007 16:22, soup_or_power@.yahoo.com wrote:
>> On May 18, 9:04 am, Robert Klemme <shortcut...@.googlemail.com> wrote:
>> On 18.05.2007 15:40, soup_or_po...@.yahoo.com wrote:
>>
>>
>> I am in the process of returning a machine running SQL Server back to
>> our provider. However, I don't want them to retrieve any of our data
>> stored in the DB. So I have the following 2 options
>> a) delete the rows from tables
>> b) remove the MDB file
>> Which of the options is better?
>> If I just delete the rows, will the SQL Server delete them from the
>> MDB file immediately?
>> If I remove the MDB file, can anyone put it back?
>> For instance, the Exchange Server use SQL Server and deleting a
>> mailbox will retain the data for about 14 days. Is there a similar
>> provision in SQL Server that retains the data. I don't want our
>> provider to retrieve any of the data.
>> Many thanks for reading and looking forward to replies
>> Depends in what state you have to give the machine back. If you don't
>> need to care for OS then the most thorough and simple is probably to
>> boot the machine using Knoppix or a similar CD/DVD distro and use dd
>> if=/dev/zero of=/dev/hda (for all disks) to erase all your hard disks.
>> Other than that there are special tools for safely erasing data, i.e.
>> you could overwrite your mdf and ldf files with zeros after you
>> deactivated your DB and before you drop the DB.
>> Kind regards
>> robert- Hide quoted text -
>> - Show quoted text -
>> Hi Robert
>> Many thanks for your reply. Could you name the tools for safely
>> erasing data?
> I don't have names. You will have to look for yourself. Sorry.
>> Also how can I "deactive" the DB?
> EM -> select DB -> all tasks -> detach database
>
Note that will leave the MDF and LDF files still on the server.
he's better of DELETING the database (assuming he can't format the drive or
something like that.)
> robert
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||On 19.05.2007 16:08, Greg D. Moore (Strider) wrote:
> "Robert Klemme" <shortcutter@.googlemail.com> wrote in message
> news:5b5s08F2g0fupU1@.mid.individual.net...
>> On 18.05.2007 16:22, soup_or_power@.yahoo.com wrote:
>> On May 18, 9:04 am, Robert Klemme <shortcut...@.googlemail.com> wrote:
>> On 18.05.2007 15:40, soup_or_po...@.yahoo.com wrote:
>>
>>
>> I am in the process of returning a machine running SQL Server back to
>> our provider. However, I don't want them to retrieve any of our data
>> stored in the DB. So I have the following 2 options
>> a) delete the rows from tables
>> b) remove the MDB file
>> Which of the options is better?
>> If I just delete the rows, will the SQL Server delete them from the
>> MDB file immediately?
>> If I remove the MDB file, can anyone put it back?
>> For instance, the Exchange Server use SQL Server and deleting a
>> mailbox will retain the data for about 14 days. Is there a similar
>> provision in SQL Server that retains the data. I don't want our
>> provider to retrieve any of the data.
>> Many thanks for reading and looking forward to replies
>> Depends in what state you have to give the machine back. If you don't
>> need to care for OS then the most thorough and simple is probably to
>> boot the machine using Knoppix or a similar CD/DVD distro and use dd
>> if=/dev/zero of=/dev/hda (for all disks) to erase all your hard disks.
>> Other than that there are special tools for safely erasing data, i.e.
>> you could overwrite your mdf and ldf files with zeros after you
>> deactivated your DB and before you drop the DB.
>> Kind regards
>> robert- Hide quoted text -
>> - Show quoted text -
>> Hi Robert
>> Many thanks for your reply. Could you name the tools for safely
>> erasing data?
>> I don't have names. You will have to look for yourself. Sorry.
>> Also how can I "deactive" the DB?
>> EM -> select DB -> all tasks -> detach database
> Note that will leave the MDF and LDF files still on the server.
> he's better of DELETING the database (assuming he can't format the drive or
> something like that.)
Yes, I know. That was just the explanation of *one* of the steps (see
my earlier posting).
robert
our provider. However, I don't want them to retrieve any of our data
stored in the DB. So I have the following 2 options
a) delete the rows from tables
b) remove the MDB file
Which of the options is better?
If I just delete the rows, will the SQL Server delete them from the
MDB file immediately?
If I remove the MDB file, can anyone put it back?
For instance, the Exchange Server use SQL Server and deleting a
mailbox will retain the data for about 14 days. Is there a similar
provision in SQL Server that retains the data. I don't want our
provider to retrieve any of the data.
Many thanks for reading and looking forward to repliesOn 18.05.2007 15:40, soup_or_power@.yahoo.com wrote:
> I am in the process of returning a machine running SQL Server back to
> our provider. However, I don't want them to retrieve any of our data
> stored in the DB. So I have the following 2 options
> a) delete the rows from tables
> b) remove the MDB file
> Which of the options is better?
> If I just delete the rows, will the SQL Server delete them from the
> MDB file immediately?
> If I remove the MDB file, can anyone put it back?
> For instance, the Exchange Server use SQL Server and deleting a
> mailbox will retain the data for about 14 days. Is there a similar
> provision in SQL Server that retains the data. I don't want our
> provider to retrieve any of the data.
> Many thanks for reading and looking forward to replies
Depends in what state you have to give the machine back. If you don't
need to care for OS then the most thorough and simple is probably to
boot the machine using Knoppix or a similar CD/DVD distro and use dd
if=/dev/zero of=/dev/hda (for all disks) to erase all your hard disks.
Other than that there are special tools for safely erasing data, i.e.
you could overwrite your mdf and ldf files with zeros after you
deactivated your DB and before you drop the DB.
Kind regards
robert|||On May 18, 9:04 am, Robert Klemme <shortcut...@.googlemail.com> wrote:
> On 18.05.2007 15:40, soup_or_po...@.yahoo.com wrote:
>
>
> > I am in the process of returning a machine running SQL Server back to
> > our provider. However, I don't want them to retrieve any of our data
> > stored in the DB. So I have the following 2 options
> > a) delete the rows from tables
> > b) remove the MDB file
> > Which of the options is better?
> > If I just delete the rows, will the SQL Server delete them from the
> > MDB file immediately?
> > If I remove the MDB file, can anyone put it back?
> > For instance, the Exchange Server use SQL Server and deleting a
> > mailbox will retain the data for about 14 days. Is there a similar
> > provision in SQL Server that retains the data. I don't want our
> > provider to retrieve any of the data.
> > Many thanks for reading and looking forward to replies
> Depends in what state you have to give the machine back. If you don't
> need to care for OS then the most thorough and simple is probably to
> boot the machine using Knoppix or a similar CD/DVD distro and use dd
> if=/dev/zero of=/dev/hda (for all disks) to erase all your hard disks.
> Other than that there are special tools for safely erasing data, i.e.
> you could overwrite your mdf and ldf files with zeros after you
> deactivated your DB and before you drop the DB.
> Kind regards
> robert- Hide quoted text -
> - Show quoted text -
Hi Robert
Many thanks for your reply. Could you name the tools for safely
erasing data? Also how can I "deactive" the DB?
Regards|||On 18.05.2007 16:22, soup_or_power@.yahoo.com wrote:
> On May 18, 9:04 am, Robert Klemme <shortcut...@.googlemail.com> wrote:
>> On 18.05.2007 15:40, soup_or_po...@.yahoo.com wrote:
>>
>>
>> I am in the process of returning a machine running SQL Server back to
>> our provider. However, I don't want them to retrieve any of our data
>> stored in the DB. So I have the following 2 options
>> a) delete the rows from tables
>> b) remove the MDB file
>> Which of the options is better?
>> If I just delete the rows, will the SQL Server delete them from the
>> MDB file immediately?
>> If I remove the MDB file, can anyone put it back?
>> For instance, the Exchange Server use SQL Server and deleting a
>> mailbox will retain the data for about 14 days. Is there a similar
>> provision in SQL Server that retains the data. I don't want our
>> provider to retrieve any of the data.
>> Many thanks for reading and looking forward to replies
>> Depends in what state you have to give the machine back. If you don't
>> need to care for OS then the most thorough and simple is probably to
>> boot the machine using Knoppix or a similar CD/DVD distro and use dd
>> if=/dev/zero of=/dev/hda (for all disks) to erase all your hard disks.
>> Other than that there are special tools for safely erasing data, i.e.
>> you could overwrite your mdf and ldf files with zeros after you
>> deactivated your DB and before you drop the DB.
>> Kind regards
>> robert- Hide quoted text -
>> - Show quoted text -
> Hi Robert
> Many thanks for your reply. Could you name the tools for safely
> erasing data?
I don't have names. You will have to look for yourself. Sorry.
> Also how can I "deactive" the DB?
EM -> select DB -> all tasks -> detach database
robert|||"Robert Klemme" <shortcutter@.googlemail.com> wrote in message
news:5b5s08F2g0fupU1@.mid.individual.net...
> On 18.05.2007 16:22, soup_or_power@.yahoo.com wrote:
>> On May 18, 9:04 am, Robert Klemme <shortcut...@.googlemail.com> wrote:
>> On 18.05.2007 15:40, soup_or_po...@.yahoo.com wrote:
>>
>>
>> I am in the process of returning a machine running SQL Server back to
>> our provider. However, I don't want them to retrieve any of our data
>> stored in the DB. So I have the following 2 options
>> a) delete the rows from tables
>> b) remove the MDB file
>> Which of the options is better?
>> If I just delete the rows, will the SQL Server delete them from the
>> MDB file immediately?
>> If I remove the MDB file, can anyone put it back?
>> For instance, the Exchange Server use SQL Server and deleting a
>> mailbox will retain the data for about 14 days. Is there a similar
>> provision in SQL Server that retains the data. I don't want our
>> provider to retrieve any of the data.
>> Many thanks for reading and looking forward to replies
>> Depends in what state you have to give the machine back. If you don't
>> need to care for OS then the most thorough and simple is probably to
>> boot the machine using Knoppix or a similar CD/DVD distro and use dd
>> if=/dev/zero of=/dev/hda (for all disks) to erase all your hard disks.
>> Other than that there are special tools for safely erasing data, i.e.
>> you could overwrite your mdf and ldf files with zeros after you
>> deactivated your DB and before you drop the DB.
>> Kind regards
>> robert- Hide quoted text -
>> - Show quoted text -
>> Hi Robert
>> Many thanks for your reply. Could you name the tools for safely
>> erasing data?
> I don't have names. You will have to look for yourself. Sorry.
>> Also how can I "deactive" the DB?
> EM -> select DB -> all tasks -> detach database
>
Note that will leave the MDF and LDF files still on the server.
he's better of DELETING the database (assuming he can't format the drive or
something like that.)
> robert
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||On 19.05.2007 16:08, Greg D. Moore (Strider) wrote:
> "Robert Klemme" <shortcutter@.googlemail.com> wrote in message
> news:5b5s08F2g0fupU1@.mid.individual.net...
>> On 18.05.2007 16:22, soup_or_power@.yahoo.com wrote:
>> On May 18, 9:04 am, Robert Klemme <shortcut...@.googlemail.com> wrote:
>> On 18.05.2007 15:40, soup_or_po...@.yahoo.com wrote:
>>
>>
>> I am in the process of returning a machine running SQL Server back to
>> our provider. However, I don't want them to retrieve any of our data
>> stored in the DB. So I have the following 2 options
>> a) delete the rows from tables
>> b) remove the MDB file
>> Which of the options is better?
>> If I just delete the rows, will the SQL Server delete them from the
>> MDB file immediately?
>> If I remove the MDB file, can anyone put it back?
>> For instance, the Exchange Server use SQL Server and deleting a
>> mailbox will retain the data for about 14 days. Is there a similar
>> provision in SQL Server that retains the data. I don't want our
>> provider to retrieve any of the data.
>> Many thanks for reading and looking forward to replies
>> Depends in what state you have to give the machine back. If you don't
>> need to care for OS then the most thorough and simple is probably to
>> boot the machine using Knoppix or a similar CD/DVD distro and use dd
>> if=/dev/zero of=/dev/hda (for all disks) to erase all your hard disks.
>> Other than that there are special tools for safely erasing data, i.e.
>> you could overwrite your mdf and ldf files with zeros after you
>> deactivated your DB and before you drop the DB.
>> Kind regards
>> robert- Hide quoted text -
>> - Show quoted text -
>> Hi Robert
>> Many thanks for your reply. Could you name the tools for safely
>> erasing data?
>> I don't have names. You will have to look for yourself. Sorry.
>> Also how can I "deactive" the DB?
>> EM -> select DB -> all tasks -> detach database
> Note that will leave the MDF and LDF files still on the server.
> he's better of DELETING the database (assuming he can't format the drive or
> something like that.)
Yes, I know. That was just the explanation of *one* of the steps (see
my earlier posting).
robert
Question about Delete and Latency
I am in the process of returning a machine running SQL Server back to
our provider. However, I don't want them to retrieve any of our data
stored in the DB. So I have the following 2 options
a) delete the rows from tables
b) remove the MDB file
Which of the options is better?
If I just delete the rows, will the SQL Server delete them from the
MDB file immediately?
If I remove the MDB file, can anyone put it back?
For instance, the Exchange Server use SQL Server and deleting a
mailbox will retain the data for about 14 days. Is there a similar
provision in SQL Server that retains the data. I don't want our
provider to retrieve any of the data.
Many thanks for reading and looking forward to repliesOn 18.05.2007 15:40, soup_or_power@.yahoo.com wrote:
> I am in the process of returning a machine running SQL Server back to
> our provider. However, I don't want them to retrieve any of our data
> stored in the DB. So I have the following 2 options
> a) delete the rows from tables
> b) remove the MDB file
> Which of the options is better?
> If I just delete the rows, will the SQL Server delete them from the
> MDB file immediately?
> If I remove the MDB file, can anyone put it back?
> For instance, the Exchange Server use SQL Server and deleting a
> mailbox will retain the data for about 14 days. Is there a similar
> provision in SQL Server that retains the data. I don't want our
> provider to retrieve any of the data.
> Many thanks for reading and looking forward to replies
Depends in what state you have to give the machine back. If you don't
need to care for OS then the most thorough and simple is probably to
boot the machine using Knoppix or a similar CD/DVD distro and use dd
if=/dev/zero of=/dev/hda (for all disks) to erase all your hard disks.
Other than that there are special tools for safely erasing data, i.e.
you could overwrite your mdf and ldf files with zeros after you
deactivated your DB and before you drop the DB.
Kind regards
robert|||On May 18, 9:04 am, Robert Klemme <shortcut...@.googlemail.com> wrote:
> On 18.05.2007 15:40, soup_or_po...@.yahoo.com wrote:
>
>
>
>
>
>
>
>
> Depends in what state you have to give the machine back. If you don't
> need to care for OS then the most thorough and simple is probably to
> boot the machine using Knoppix or a similar CD/DVD distro and use dd
> if=/dev/zero of=/dev/hda (for all disks) to erase all your hard disks.
> Other than that there are special tools for safely erasing data, i.e.
> you could overwrite your mdf and ldf files with zeros after you
> deactivated your DB and before you drop the DB.
> Kind regards
> robert- Hide quoted text -
> - Show quoted text -
Hi Robert
Many thanks for your reply. Could you name the tools for safely
erasing data? Also how can I "deactive" the DB?
Regards|||On 18.05.2007 16:22, soup_or_power@.yahoo.com wrote:
> On May 18, 9:04 am, Robert Klemme <shortcut...@.googlemail.com> wrote:
> Hi Robert
> Many thanks for your reply. Could you name the tools for safely
> erasing data?
I don't have names. You will have to look for yourself. Sorry.
> Also how can I "deactive" the DB?
EM -> select DB -> all tasks -> detach database
robert|||"Robert Klemme" <shortcutter@.googlemail.com> wrote in message
news:5b5s08F2g0fupU1@.mid.individual.net...
> On 18.05.2007 16:22, soup_or_power@.yahoo.com wrote:
> I don't have names. You will have to look for yourself. Sorry.
>
> EM -> select DB -> all tasks -> detach database
>
Note that will leave the MDF and LDF files still on the server.
he's better of DELETING the database (assuming he can't format the drive or
something like that.)
> robert
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||On 19.05.2007 16:08, Greg D. Moore (Strider) wrote:
> "Robert Klemme" <shortcutter@.googlemail.com> wrote in message
> news:5b5s08F2g0fupU1@.mid.individual.net...
> Note that will leave the MDF and LDF files still on the server.
> he's better of DELETING the database (assuming he can't format the drive o
r
> something like that.)
Yes, I know. That was just the explanation of *one* of the steps (see
my earlier posting).
robert
our provider. However, I don't want them to retrieve any of our data
stored in the DB. So I have the following 2 options
a) delete the rows from tables
b) remove the MDB file
Which of the options is better?
If I just delete the rows, will the SQL Server delete them from the
MDB file immediately?
If I remove the MDB file, can anyone put it back?
For instance, the Exchange Server use SQL Server and deleting a
mailbox will retain the data for about 14 days. Is there a similar
provision in SQL Server that retains the data. I don't want our
provider to retrieve any of the data.
Many thanks for reading and looking forward to repliesOn 18.05.2007 15:40, soup_or_power@.yahoo.com wrote:
> I am in the process of returning a machine running SQL Server back to
> our provider. However, I don't want them to retrieve any of our data
> stored in the DB. So I have the following 2 options
> a) delete the rows from tables
> b) remove the MDB file
> Which of the options is better?
> If I just delete the rows, will the SQL Server delete them from the
> MDB file immediately?
> If I remove the MDB file, can anyone put it back?
> For instance, the Exchange Server use SQL Server and deleting a
> mailbox will retain the data for about 14 days. Is there a similar
> provision in SQL Server that retains the data. I don't want our
> provider to retrieve any of the data.
> Many thanks for reading and looking forward to replies
Depends in what state you have to give the machine back. If you don't
need to care for OS then the most thorough and simple is probably to
boot the machine using Knoppix or a similar CD/DVD distro and use dd
if=/dev/zero of=/dev/hda (for all disks) to erase all your hard disks.
Other than that there are special tools for safely erasing data, i.e.
you could overwrite your mdf and ldf files with zeros after you
deactivated your DB and before you drop the DB.
Kind regards
robert|||On May 18, 9:04 am, Robert Klemme <shortcut...@.googlemail.com> wrote:
> On 18.05.2007 15:40, soup_or_po...@.yahoo.com wrote:
>
>
>
>
>
>
>
>
> Depends in what state you have to give the machine back. If you don't
> need to care for OS then the most thorough and simple is probably to
> boot the machine using Knoppix or a similar CD/DVD distro and use dd
> if=/dev/zero of=/dev/hda (for all disks) to erase all your hard disks.
> Other than that there are special tools for safely erasing data, i.e.
> you could overwrite your mdf and ldf files with zeros after you
> deactivated your DB and before you drop the DB.
> Kind regards
> robert- Hide quoted text -
> - Show quoted text -
Hi Robert
Many thanks for your reply. Could you name the tools for safely
erasing data? Also how can I "deactive" the DB?
Regards|||On 18.05.2007 16:22, soup_or_power@.yahoo.com wrote:
> On May 18, 9:04 am, Robert Klemme <shortcut...@.googlemail.com> wrote:
> Hi Robert
> Many thanks for your reply. Could you name the tools for safely
> erasing data?
I don't have names. You will have to look for yourself. Sorry.
> Also how can I "deactive" the DB?
EM -> select DB -> all tasks -> detach database
robert|||"Robert Klemme" <shortcutter@.googlemail.com> wrote in message
news:5b5s08F2g0fupU1@.mid.individual.net...
> On 18.05.2007 16:22, soup_or_power@.yahoo.com wrote:
> I don't have names. You will have to look for yourself. Sorry.
>
> EM -> select DB -> all tasks -> detach database
>
Note that will leave the MDF and LDF files still on the server.
he's better of DELETING the database (assuming he can't format the drive or
something like that.)
> robert
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||On 19.05.2007 16:08, Greg D. Moore (Strider) wrote:
> "Robert Klemme" <shortcutter@.googlemail.com> wrote in message
> news:5b5s08F2g0fupU1@.mid.individual.net...
> Note that will leave the MDF and LDF files still on the server.
> he's better of DELETING the database (assuming he can't format the drive o
r
> something like that.)
Yes, I know. That was just the explanation of *one* of the steps (see
my earlier posting).
robert
Question about Delete and Latency
I am in the process of returning a machine running SQL Server back to
our provider. However, I don't want them to retrieve any of our data
stored in the DB. So I have the following 2 options
a) delete the rows from tables
b) remove the MDB file
Which of the options is better?
If I just delete the rows, will the SQL Server delete them from the
MDB file immediately?
If I remove the MDB file, can anyone put it back?
For instance, the Exchange Server use SQL Server and deleting a
mailbox will retain the data for about 14 days. Is there a similar
provision in SQL Server that retains the data. I don't want our
provider to retrieve any of the data.
Many thanks for reading and looking forward to replies
On 18.05.2007 15:40, soup_or_power@.yahoo.com wrote:
> I am in the process of returning a machine running SQL Server back to
> our provider. However, I don't want them to retrieve any of our data
> stored in the DB. So I have the following 2 options
> a) delete the rows from tables
> b) remove the MDB file
> Which of the options is better?
> If I just delete the rows, will the SQL Server delete them from the
> MDB file immediately?
> If I remove the MDB file, can anyone put it back?
> For instance, the Exchange Server use SQL Server and deleting a
> mailbox will retain the data for about 14 days. Is there a similar
> provision in SQL Server that retains the data. I don't want our
> provider to retrieve any of the data.
> Many thanks for reading and looking forward to replies
Depends in what state you have to give the machine back. If you don't
need to care for OS then the most thorough and simple is probably to
boot the machine using Knoppix or a similar CD/DVD distro and use dd
if=/dev/zero of=/dev/hda (for all disks) to erase all your hard disks.
Other than that there are special tools for safely erasing data, i.e.
you could overwrite your mdf and ldf files with zeros after you
deactivated your DB and before you drop the DB.
Kind regards
robert
our provider. However, I don't want them to retrieve any of our data
stored in the DB. So I have the following 2 options
a) delete the rows from tables
b) remove the MDB file
Which of the options is better?
If I just delete the rows, will the SQL Server delete them from the
MDB file immediately?
If I remove the MDB file, can anyone put it back?
For instance, the Exchange Server use SQL Server and deleting a
mailbox will retain the data for about 14 days. Is there a similar
provision in SQL Server that retains the data. I don't want our
provider to retrieve any of the data.
Many thanks for reading and looking forward to replies
On 18.05.2007 15:40, soup_or_power@.yahoo.com wrote:
> I am in the process of returning a machine running SQL Server back to
> our provider. However, I don't want them to retrieve any of our data
> stored in the DB. So I have the following 2 options
> a) delete the rows from tables
> b) remove the MDB file
> Which of the options is better?
> If I just delete the rows, will the SQL Server delete them from the
> MDB file immediately?
> If I remove the MDB file, can anyone put it back?
> For instance, the Exchange Server use SQL Server and deleting a
> mailbox will retain the data for about 14 days. Is there a similar
> provision in SQL Server that retains the data. I don't want our
> provider to retrieve any of the data.
> Many thanks for reading and looking forward to replies
Depends in what state you have to give the machine back. If you don't
need to care for OS then the most thorough and simple is probably to
boot the machine using Knoppix or a similar CD/DVD distro and use dd
if=/dev/zero of=/dev/hda (for all disks) to erase all your hard disks.
Other than that there are special tools for safely erasing data, i.e.
you could overwrite your mdf and ldf files with zeros after you
deactivated your DB and before you drop the DB.
Kind regards
robert
Wednesday, March 7, 2012
Question about Back ups
I have a production server that has a lot of data changes through out the
day. I do one full back up at night and do hourly log back ups through out
the major part of the day.
My question is when I do the full back up does it back up the log as well
and does it purge the data in the log? Also, when I do hourly log back ups
should I append the most recent hourly log back up or overwrite it? Not sure
if the log purges when you do a back up?
Thanks for your help?
-ChrisChris
My question is when I do the full back up does it back up the log as well
> and does it purge the data in the log?
Yes , it does backup log file as well, but it does not "clear" the file ( I
mean ,commited tranactions(inactive portions) are still in)
>Also, when I do hourly log back ups
> should I append the most recent hourly log back up or overwrite it?
Well , you could by using backup log file WITH INIT option, however , an
advantage of this option is that in terms of database's crach you don't need
to apply hundred of logs to get the database "healthy", on the other hand
if your last full database is corrupted/lost , you are still will ne able to
restore a backup from the two/three weeks ago and apply all log files
without loosing the data, actually it is possible because whole databse
backup does not truncate log files
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:2992A4FC-E1DB-4108-9DD8-3B4E515CCD43@.microsoft.com...
>I have a production server that has a lot of data changes through out the
> day. I do one full back up at night and do hourly log back ups through out
> the major part of the day.
> My question is when I do the full back up does it back up the log as well
> and does it purge the data in the log? Also, when I do hourly log back ups
> should I append the most recent hourly log back up or overwrite it? Not
> sure
> if the log purges when you do a back up?
> Thanks for your help?
> -Chris
>|||On Wed, 24 May 2006 05:06:02 -0700, Chris wrote:
>I have a production server that has a lot of data changes through out the
>day. I do one full back up at night and do hourly log back ups through out
>the major part of the day.
>My question is when I do the full back up does it back up the log as well
>and does it purge the data in the log?
Hi Chris,
See Uri's reply.
> Also, when I do hourly log back ups
>should I append the most recent hourly log back up or overwrite it? Not sur
e
>if the log purges when you do a back up?
Don't overwrite it. The log file is purged after a log backup. If you do
a full backup at midnight and log backups on the full hour, recovering
from a crash at 4:15 PM requires you to restore
- The last full backup,
- All log backups since midnight up to 4 PM
- The log backup up to 4:15 PM (taken after the crash)
You should only overwrite log backups from before the last full backup,
and you might consider keeping them around for some time as well since
this would be an extra safeguard in case the full backup would be
corrupted.
Hugo Kornelis, SQL Server MVP|||Hugo
> You should only overwrite log backups from before the last full backup,
> and you might consider keeping them around for some time as well since
> this would be an extra safeguard in case the full backup would be
> corrupted.
I meant to use WITH INIT option once in two/three weeks and NOT after every
LOG file that the OP does.
I fully agree with you that we don't need to owerwrite log file backups
after each backup (hourly in his case)
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:3mk8721gfi3lhd36khnhoh4ul8cne0h2u3@.
4ax.com...
> On Wed, 24 May 2006 05:06:02 -0700, Chris wrote:
>
> Hi Chris,
> See Uri's reply.
>
> Don't overwrite it. The log file is purged after a log backup. If you do
> a full backup at midnight and log backups on the full hour, recovering
> from a crash at 4:15 PM requires you to restore
> - The last full backup,
> - All log backups since midnight up to 4 PM
> - The log backup up to 4:15 PM (taken after the crash)
> You should only overwrite log backups from before the last full backup,
> and you might consider keeping them around for some time as well since
> this would be an extra safeguard in case the full backup would be
> corrupted.
> --
> Hugo Kornelis, SQL Server MVP|||So if the log file is purged after the log back up then I want to append the
last back up until I do a full back up. Is there a way to overwrite once,
after the full back up so my log back up file is not growing to an enormous
size?
"Hugo Kornelis" wrote:
> On Wed, 24 May 2006 05:06:02 -0700, Chris wrote:
>
> Hi Chris,
> See Uri's reply.
>
> Don't overwrite it. The log file is purged after a log backup. If you do
> a full backup at midnight and log backups on the full hour, recovering
> from a crash at 4:15 PM requires you to restore
> - The last full backup,
> - All log backups since midnight up to 4 PM
> - The log backup up to 4:15 PM (taken after the crash)
> You should only overwrite log backups from before the last full backup,
> and you might consider keeping them around for some time as well since
> this would be an extra safeguard in case the full backup would be
> corrupted.
> --
> Hugo Kornelis, SQL Server MVP
>|||> So if the log file is purged after the log back up then I want to append then">
> last back up until I do a full back up.
What about backup generations?
> Is there a way to overwrite once,
> after the full back up so my log back up file is not growing to an enormou
s
> size?
You have to code that functionality. Or use maintenance plans. Nothing built
-in at the TSQL level.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:407FDDF9-0D12-4F6E-8E4C-364743B6DC65@.microsoft.com...[vbcol=seagreen]
> So if the log file is purged after the log back up then I want to append t
he
> last back up until I do a full back up. Is there a way to overwrite once,
> after the full back up so my log back up file is not growing to an enormou
s
> size?
>
> "Hugo Kornelis" wrote:
>|||Great Thanks for all of your help !!
"Uri Dimant" wrote:
> Hugo
> I meant to use WITH INIT option once in two/three weeks and NOT after ever
y
> LOG file that the OP does.
> I fully agree with you that we don't need to owerwrite log file backups
> after each backup (hourly in his case)
>
>
>
> "Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
> news:3mk8721gfi3lhd36khnhoh4ul8cne0h2u3@.
4ax.com...
>
>
day. I do one full back up at night and do hourly log back ups through out
the major part of the day.
My question is when I do the full back up does it back up the log as well
and does it purge the data in the log? Also, when I do hourly log back ups
should I append the most recent hourly log back up or overwrite it? Not sure
if the log purges when you do a back up?
Thanks for your help?
-ChrisChris
My question is when I do the full back up does it back up the log as well
> and does it purge the data in the log?
Yes , it does backup log file as well, but it does not "clear" the file ( I
mean ,commited tranactions(inactive portions) are still in)
>Also, when I do hourly log back ups
> should I append the most recent hourly log back up or overwrite it?
Well , you could by using backup log file WITH INIT option, however , an
advantage of this option is that in terms of database's crach you don't need
to apply hundred of logs to get the database "healthy", on the other hand
if your last full database is corrupted/lost , you are still will ne able to
restore a backup from the two/three weeks ago and apply all log files
without loosing the data, actually it is possible because whole databse
backup does not truncate log files
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:2992A4FC-E1DB-4108-9DD8-3B4E515CCD43@.microsoft.com...
>I have a production server that has a lot of data changes through out the
> day. I do one full back up at night and do hourly log back ups through out
> the major part of the day.
> My question is when I do the full back up does it back up the log as well
> and does it purge the data in the log? Also, when I do hourly log back ups
> should I append the most recent hourly log back up or overwrite it? Not
> sure
> if the log purges when you do a back up?
> Thanks for your help?
> -Chris
>|||On Wed, 24 May 2006 05:06:02 -0700, Chris wrote:
>I have a production server that has a lot of data changes through out the
>day. I do one full back up at night and do hourly log back ups through out
>the major part of the day.
>My question is when I do the full back up does it back up the log as well
>and does it purge the data in the log?
Hi Chris,
See Uri's reply.
> Also, when I do hourly log back ups
>should I append the most recent hourly log back up or overwrite it? Not sur
e
>if the log purges when you do a back up?
Don't overwrite it. The log file is purged after a log backup. If you do
a full backup at midnight and log backups on the full hour, recovering
from a crash at 4:15 PM requires you to restore
- The last full backup,
- All log backups since midnight up to 4 PM
- The log backup up to 4:15 PM (taken after the crash)
You should only overwrite log backups from before the last full backup,
and you might consider keeping them around for some time as well since
this would be an extra safeguard in case the full backup would be
corrupted.
Hugo Kornelis, SQL Server MVP|||Hugo
> You should only overwrite log backups from before the last full backup,
> and you might consider keeping them around for some time as well since
> this would be an extra safeguard in case the full backup would be
> corrupted.
I meant to use WITH INIT option once in two/three weeks and NOT after every
LOG file that the OP does.
I fully agree with you that we don't need to owerwrite log file backups
after each backup (hourly in his case)
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:3mk8721gfi3lhd36khnhoh4ul8cne0h2u3@.
4ax.com...
> On Wed, 24 May 2006 05:06:02 -0700, Chris wrote:
>
> Hi Chris,
> See Uri's reply.
>
> Don't overwrite it. The log file is purged after a log backup. If you do
> a full backup at midnight and log backups on the full hour, recovering
> from a crash at 4:15 PM requires you to restore
> - The last full backup,
> - All log backups since midnight up to 4 PM
> - The log backup up to 4:15 PM (taken after the crash)
> You should only overwrite log backups from before the last full backup,
> and you might consider keeping them around for some time as well since
> this would be an extra safeguard in case the full backup would be
> corrupted.
> --
> Hugo Kornelis, SQL Server MVP|||So if the log file is purged after the log back up then I want to append the
last back up until I do a full back up. Is there a way to overwrite once,
after the full back up so my log back up file is not growing to an enormous
size?
"Hugo Kornelis" wrote:
> On Wed, 24 May 2006 05:06:02 -0700, Chris wrote:
>
> Hi Chris,
> See Uri's reply.
>
> Don't overwrite it. The log file is purged after a log backup. If you do
> a full backup at midnight and log backups on the full hour, recovering
> from a crash at 4:15 PM requires you to restore
> - The last full backup,
> - All log backups since midnight up to 4 PM
> - The log backup up to 4:15 PM (taken after the crash)
> You should only overwrite log backups from before the last full backup,
> and you might consider keeping them around for some time as well since
> this would be an extra safeguard in case the full backup would be
> corrupted.
> --
> Hugo Kornelis, SQL Server MVP
>|||> So if the log file is purged after the log back up then I want to append then">
> last back up until I do a full back up.
What about backup generations?
> Is there a way to overwrite once,
> after the full back up so my log back up file is not growing to an enormou
s
> size?
You have to code that functionality. Or use maintenance plans. Nothing built
-in at the TSQL level.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:407FDDF9-0D12-4F6E-8E4C-364743B6DC65@.microsoft.com...[vbcol=seagreen]
> So if the log file is purged after the log back up then I want to append t
he
> last back up until I do a full back up. Is there a way to overwrite once,
> after the full back up so my log back up file is not growing to an enormou
s
> size?
>
> "Hugo Kornelis" wrote:
>|||Great Thanks for all of your help !!
"Uri Dimant" wrote:
> Hugo
> I meant to use WITH INIT option once in two/three weeks and NOT after ever
y
> LOG file that the OP does.
> I fully agree with you that we don't need to owerwrite log file backups
> after each backup (hourly in his case)
>
>
>
> "Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
> news:3mk8721gfi3lhd36khnhoh4ul8cne0h2u3@.
4ax.com...
>
>
Question about Back ups
I have a production server that has a lot of data changes through out the
day. I do one full back up at night and do hourly log back ups through out
the major part of the day.
My question is when I do the full back up does it back up the log as well
and does it purge the data in the log? Also, when I do hourly log back ups
should I append the most recent hourly log back up or overwrite it? Not sure
if the log purges when you do a back up?
Thanks for your help?
-ChrisChris
My question is when I do the full back up does it back up the log as well
> and does it purge the data in the log?
Yes , it does backup log file as well, but it does not "clear" the file ( I
mean ,commited tranactions(inactive portions) are still in)
>Also, when I do hourly log back ups
> should I append the most recent hourly log back up or overwrite it?
Well , you could by using backup log file WITH INIT option, however , an
advantage of this option is that in terms of database's crach you don't need
to apply hundred of logs to get the database "healthy", on the other hand
if your last full database is corrupted/lost , you are still will ne able to
restore a backup from the two/three weeks ago and apply all log files
without loosing the data, actually it is possible because whole databse
backup does not truncate log files
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:2992A4FC-E1DB-4108-9DD8-3B4E515CCD43@.microsoft.com...
>I have a production server that has a lot of data changes through out the
> day. I do one full back up at night and do hourly log back ups through out
> the major part of the day.
> My question is when I do the full back up does it back up the log as well
> and does it purge the data in the log? Also, when I do hourly log back ups
> should I append the most recent hourly log back up or overwrite it? Not
> sure
> if the log purges when you do a back up?
> Thanks for your help?
> -Chris
>|||On Wed, 24 May 2006 05:06:02 -0700, Chris wrote:
>I have a production server that has a lot of data changes through out the
>day. I do one full back up at night and do hourly log back ups through out
>the major part of the day.
>My question is when I do the full back up does it back up the log as well
>and does it purge the data in the log?
Hi Chris,
See Uri's reply.
> Also, when I do hourly log back ups
>should I append the most recent hourly log back up or overwrite it? Not sure
>if the log purges when you do a back up?
Don't overwrite it. The log file is purged after a log backup. If you do
a full backup at midnight and log backups on the full hour, recovering
from a crash at 4:15 PM requires you to restore
- The last full backup,
- All log backups since midnight up to 4 PM
- The log backup up to 4:15 PM (taken after the crash)
You should only overwrite log backups from before the last full backup,
and you might consider keeping them around for some time as well since
this would be an extra safeguard in case the full backup would be
corrupted.
--
Hugo Kornelis, SQL Server MVP|||Hugo
> You should only overwrite log backups from before the last full backup,
> and you might consider keeping them around for some time as well since
> this would be an extra safeguard in case the full backup would be
> corrupted.
I meant to use WITH INIT option once in two/three weeks and NOT after every
LOG file that the OP does.
I fully agree with you that we don't need to owerwrite log file backups
after each backup (hourly in his case)
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:3mk8721gfi3lhd36khnhoh4ul8cne0h2u3@.4ax.com...
> On Wed, 24 May 2006 05:06:02 -0700, Chris wrote:
>>I have a production server that has a lot of data changes through out the
>>day. I do one full back up at night and do hourly log back ups through out
>>the major part of the day.
>>My question is when I do the full back up does it back up the log as well
>>and does it purge the data in the log?
> Hi Chris,
> See Uri's reply.
>> Also, when I do hourly log back ups
>>should I append the most recent hourly log back up or overwrite it? Not
>>sure
>>if the log purges when you do a back up?
> Don't overwrite it. The log file is purged after a log backup. If you do
> a full backup at midnight and log backups on the full hour, recovering
> from a crash at 4:15 PM requires you to restore
> - The last full backup,
> - All log backups since midnight up to 4 PM
> - The log backup up to 4:15 PM (taken after the crash)
> You should only overwrite log backups from before the last full backup,
> and you might consider keeping them around for some time as well since
> this would be an extra safeguard in case the full backup would be
> corrupted.
> --
> Hugo Kornelis, SQL Server MVP|||So if the log file is purged after the log back up then I want to append the
last back up until I do a full back up. Is there a way to overwrite once,
after the full back up so my log back up file is not growing to an enormous
size?
"Hugo Kornelis" wrote:
> On Wed, 24 May 2006 05:06:02 -0700, Chris wrote:
> >I have a production server that has a lot of data changes through out the
> >day. I do one full back up at night and do hourly log back ups through out
> >the major part of the day.
> >
> >My question is when I do the full back up does it back up the log as well
> >and does it purge the data in the log?
> Hi Chris,
> See Uri's reply.
> > Also, when I do hourly log back ups
> >should I append the most recent hourly log back up or overwrite it? Not sure
> >if the log purges when you do a back up?
> Don't overwrite it. The log file is purged after a log backup. If you do
> a full backup at midnight and log backups on the full hour, recovering
> from a crash at 4:15 PM requires you to restore
> - The last full backup,
> - All log backups since midnight up to 4 PM
> - The log backup up to 4:15 PM (taken after the crash)
> You should only overwrite log backups from before the last full backup,
> and you might consider keeping them around for some time as well since
> this would be an extra safeguard in case the full backup would be
> corrupted.
> --
> Hugo Kornelis, SQL Server MVP
>|||> So if the log file is purged after the log back up then I want to append the
> last back up until I do a full back up.
What about backup generations?
> Is there a way to overwrite once,
> after the full back up so my log back up file is not growing to an enormous
> size?
You have to code that functionality. Or use maintenance plans. Nothing built-in at the TSQL level.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:407FDDF9-0D12-4F6E-8E4C-364743B6DC65@.microsoft.com...
> So if the log file is purged after the log back up then I want to append the
> last back up until I do a full back up. Is there a way to overwrite once,
> after the full back up so my log back up file is not growing to an enormous
> size?
>
> "Hugo Kornelis" wrote:
>> On Wed, 24 May 2006 05:06:02 -0700, Chris wrote:
>> >I have a production server that has a lot of data changes through out the
>> >day. I do one full back up at night and do hourly log back ups through out
>> >the major part of the day.
>> >
>> >My question is when I do the full back up does it back up the log as well
>> >and does it purge the data in the log?
>> Hi Chris,
>> See Uri's reply.
>> > Also, when I do hourly log back ups
>> >should I append the most recent hourly log back up or overwrite it? Not sure
>> >if the log purges when you do a back up?
>> Don't overwrite it. The log file is purged after a log backup. If you do
>> a full backup at midnight and log backups on the full hour, recovering
>> from a crash at 4:15 PM requires you to restore
>> - The last full backup,
>> - All log backups since midnight up to 4 PM
>> - The log backup up to 4:15 PM (taken after the crash)
>> You should only overwrite log backups from before the last full backup,
>> and you might consider keeping them around for some time as well since
>> this would be an extra safeguard in case the full backup would be
>> corrupted.
>> --
>> Hugo Kornelis, SQL Server MVP|||Great Thanks for all of your help !!
"Uri Dimant" wrote:
> Hugo
> > You should only overwrite log backups from before the last full backup,
> > and you might consider keeping them around for some time as well since
> > this would be an extra safeguard in case the full backup would be
> > corrupted.
> I meant to use WITH INIT option once in two/three weeks and NOT after every
> LOG file that the OP does.
> I fully agree with you that we don't need to owerwrite log file backups
> after each backup (hourly in his case)
>
>
>
> "Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
> news:3mk8721gfi3lhd36khnhoh4ul8cne0h2u3@.4ax.com...
> > On Wed, 24 May 2006 05:06:02 -0700, Chris wrote:
> >
> >>I have a production server that has a lot of data changes through out the
> >>day. I do one full back up at night and do hourly log back ups through out
> >>the major part of the day.
> >>
> >>My question is when I do the full back up does it back up the log as well
> >>and does it purge the data in the log?
> >
> > Hi Chris,
> >
> > See Uri's reply.
> >
> >> Also, when I do hourly log back ups
> >>should I append the most recent hourly log back up or overwrite it? Not
> >>sure
> >>if the log purges when you do a back up?
> >
> > Don't overwrite it. The log file is purged after a log backup. If you do
> > a full backup at midnight and log backups on the full hour, recovering
> > from a crash at 4:15 PM requires you to restore
> > - The last full backup,
> > - All log backups since midnight up to 4 PM
> > - The log backup up to 4:15 PM (taken after the crash)
> >
> > You should only overwrite log backups from before the last full backup,
> > and you might consider keeping them around for some time as well since
> > this would be an extra safeguard in case the full backup would be
> > corrupted.
> >
> > --
> > Hugo Kornelis, SQL Server MVP
>
>
day. I do one full back up at night and do hourly log back ups through out
the major part of the day.
My question is when I do the full back up does it back up the log as well
and does it purge the data in the log? Also, when I do hourly log back ups
should I append the most recent hourly log back up or overwrite it? Not sure
if the log purges when you do a back up?
Thanks for your help?
-ChrisChris
My question is when I do the full back up does it back up the log as well
> and does it purge the data in the log?
Yes , it does backup log file as well, but it does not "clear" the file ( I
mean ,commited tranactions(inactive portions) are still in)
>Also, when I do hourly log back ups
> should I append the most recent hourly log back up or overwrite it?
Well , you could by using backup log file WITH INIT option, however , an
advantage of this option is that in terms of database's crach you don't need
to apply hundred of logs to get the database "healthy", on the other hand
if your last full database is corrupted/lost , you are still will ne able to
restore a backup from the two/three weeks ago and apply all log files
without loosing the data, actually it is possible because whole databse
backup does not truncate log files
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:2992A4FC-E1DB-4108-9DD8-3B4E515CCD43@.microsoft.com...
>I have a production server that has a lot of data changes through out the
> day. I do one full back up at night and do hourly log back ups through out
> the major part of the day.
> My question is when I do the full back up does it back up the log as well
> and does it purge the data in the log? Also, when I do hourly log back ups
> should I append the most recent hourly log back up or overwrite it? Not
> sure
> if the log purges when you do a back up?
> Thanks for your help?
> -Chris
>|||On Wed, 24 May 2006 05:06:02 -0700, Chris wrote:
>I have a production server that has a lot of data changes through out the
>day. I do one full back up at night and do hourly log back ups through out
>the major part of the day.
>My question is when I do the full back up does it back up the log as well
>and does it purge the data in the log?
Hi Chris,
See Uri's reply.
> Also, when I do hourly log back ups
>should I append the most recent hourly log back up or overwrite it? Not sure
>if the log purges when you do a back up?
Don't overwrite it. The log file is purged after a log backup. If you do
a full backup at midnight and log backups on the full hour, recovering
from a crash at 4:15 PM requires you to restore
- The last full backup,
- All log backups since midnight up to 4 PM
- The log backup up to 4:15 PM (taken after the crash)
You should only overwrite log backups from before the last full backup,
and you might consider keeping them around for some time as well since
this would be an extra safeguard in case the full backup would be
corrupted.
--
Hugo Kornelis, SQL Server MVP|||Hugo
> You should only overwrite log backups from before the last full backup,
> and you might consider keeping them around for some time as well since
> this would be an extra safeguard in case the full backup would be
> corrupted.
I meant to use WITH INIT option once in two/three weeks and NOT after every
LOG file that the OP does.
I fully agree with you that we don't need to owerwrite log file backups
after each backup (hourly in his case)
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:3mk8721gfi3lhd36khnhoh4ul8cne0h2u3@.4ax.com...
> On Wed, 24 May 2006 05:06:02 -0700, Chris wrote:
>>I have a production server that has a lot of data changes through out the
>>day. I do one full back up at night and do hourly log back ups through out
>>the major part of the day.
>>My question is when I do the full back up does it back up the log as well
>>and does it purge the data in the log?
> Hi Chris,
> See Uri's reply.
>> Also, when I do hourly log back ups
>>should I append the most recent hourly log back up or overwrite it? Not
>>sure
>>if the log purges when you do a back up?
> Don't overwrite it. The log file is purged after a log backup. If you do
> a full backup at midnight and log backups on the full hour, recovering
> from a crash at 4:15 PM requires you to restore
> - The last full backup,
> - All log backups since midnight up to 4 PM
> - The log backup up to 4:15 PM (taken after the crash)
> You should only overwrite log backups from before the last full backup,
> and you might consider keeping them around for some time as well since
> this would be an extra safeguard in case the full backup would be
> corrupted.
> --
> Hugo Kornelis, SQL Server MVP|||So if the log file is purged after the log back up then I want to append the
last back up until I do a full back up. Is there a way to overwrite once,
after the full back up so my log back up file is not growing to an enormous
size?
"Hugo Kornelis" wrote:
> On Wed, 24 May 2006 05:06:02 -0700, Chris wrote:
> >I have a production server that has a lot of data changes through out the
> >day. I do one full back up at night and do hourly log back ups through out
> >the major part of the day.
> >
> >My question is when I do the full back up does it back up the log as well
> >and does it purge the data in the log?
> Hi Chris,
> See Uri's reply.
> > Also, when I do hourly log back ups
> >should I append the most recent hourly log back up or overwrite it? Not sure
> >if the log purges when you do a back up?
> Don't overwrite it. The log file is purged after a log backup. If you do
> a full backup at midnight and log backups on the full hour, recovering
> from a crash at 4:15 PM requires you to restore
> - The last full backup,
> - All log backups since midnight up to 4 PM
> - The log backup up to 4:15 PM (taken after the crash)
> You should only overwrite log backups from before the last full backup,
> and you might consider keeping them around for some time as well since
> this would be an extra safeguard in case the full backup would be
> corrupted.
> --
> Hugo Kornelis, SQL Server MVP
>|||> So if the log file is purged after the log back up then I want to append the
> last back up until I do a full back up.
What about backup generations?
> Is there a way to overwrite once,
> after the full back up so my log back up file is not growing to an enormous
> size?
You have to code that functionality. Or use maintenance plans. Nothing built-in at the TSQL level.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:407FDDF9-0D12-4F6E-8E4C-364743B6DC65@.microsoft.com...
> So if the log file is purged after the log back up then I want to append the
> last back up until I do a full back up. Is there a way to overwrite once,
> after the full back up so my log back up file is not growing to an enormous
> size?
>
> "Hugo Kornelis" wrote:
>> On Wed, 24 May 2006 05:06:02 -0700, Chris wrote:
>> >I have a production server that has a lot of data changes through out the
>> >day. I do one full back up at night and do hourly log back ups through out
>> >the major part of the day.
>> >
>> >My question is when I do the full back up does it back up the log as well
>> >and does it purge the data in the log?
>> Hi Chris,
>> See Uri's reply.
>> > Also, when I do hourly log back ups
>> >should I append the most recent hourly log back up or overwrite it? Not sure
>> >if the log purges when you do a back up?
>> Don't overwrite it. The log file is purged after a log backup. If you do
>> a full backup at midnight and log backups on the full hour, recovering
>> from a crash at 4:15 PM requires you to restore
>> - The last full backup,
>> - All log backups since midnight up to 4 PM
>> - The log backup up to 4:15 PM (taken after the crash)
>> You should only overwrite log backups from before the last full backup,
>> and you might consider keeping them around for some time as well since
>> this would be an extra safeguard in case the full backup would be
>> corrupted.
>> --
>> Hugo Kornelis, SQL Server MVP|||Great Thanks for all of your help !!
"Uri Dimant" wrote:
> Hugo
> > You should only overwrite log backups from before the last full backup,
> > and you might consider keeping them around for some time as well since
> > this would be an extra safeguard in case the full backup would be
> > corrupted.
> I meant to use WITH INIT option once in two/three weeks and NOT after every
> LOG file that the OP does.
> I fully agree with you that we don't need to owerwrite log file backups
> after each backup (hourly in his case)
>
>
>
> "Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
> news:3mk8721gfi3lhd36khnhoh4ul8cne0h2u3@.4ax.com...
> > On Wed, 24 May 2006 05:06:02 -0700, Chris wrote:
> >
> >>I have a production server that has a lot of data changes through out the
> >>day. I do one full back up at night and do hourly log back ups through out
> >>the major part of the day.
> >>
> >>My question is when I do the full back up does it back up the log as well
> >>and does it purge the data in the log?
> >
> > Hi Chris,
> >
> > See Uri's reply.
> >
> >> Also, when I do hourly log back ups
> >>should I append the most recent hourly log back up or overwrite it? Not
> >>sure
> >>if the log purges when you do a back up?
> >
> > Don't overwrite it. The log file is purged after a log backup. If you do
> > a full backup at midnight and log backups on the full hour, recovering
> > from a crash at 4:15 PM requires you to restore
> > - The last full backup,
> > - All log backups since midnight up to 4 PM
> > - The log backup up to 4:15 PM (taken after the crash)
> >
> > You should only overwrite log backups from before the last full backup,
> > and you might consider keeping them around for some time as well since
> > this would be an extra safeguard in case the full backup would be
> > corrupted.
> >
> > --
> > Hugo Kornelis, SQL Server MVP
>
>
Saturday, February 25, 2012
Question
Anyone ever created a database with SQL on the back end and ASp.net on the front end as the gui? Anyone got any thoughts on the matter?? Their personal experiencesYup, done it quite often, no problems what so ever. What are you really asking though?
Feel free to PM me any specific questions.|||the reason I was thinking of ASP.Net is because I was thinking of using it as a front end instead of access xp. But I need to learn how to use it first. Anyone know any good books or any good software packages to get me started? Advice is always nice too :):):)
thank you|||well it really depends in how you want to write it. Do you want to use inline code, code behind or an application? Three very different approaches... all with different pros and cons.
Do you know html well enough to be up to the job?
Have you written any asp or vb before?
Your idea is perfectly workable, but to point you in the right direct means you really need to have a base understanding of how asp.Net works in the first place.|||I have created webpages with HTML several times, cascading styles sheets etc. Asp.net is new to me though|||You might want to start with asp then (as oposed to asp.net) or php just to get the ideas down before leaping into the gapping chasm of .Net.|||Personally, I would not recommend classic asp. What is your development background - what languages have you programmed in - c, c++, Java, vb ... ?|||Okie, my reasons for recommending standard asp are as follows.
1. The language is a piece of cake to learn.
2. Because the language is easy you can concentrate on the important things like the theorys and practises behind write web applications.
3. Because you can learn the langauge quickly and thus pick up the concepts faster you can soon decide if a web app is really what you want.
4. If you know a C based language then vb is childs play.
5. If you don't know a C based language then you should be able to pick up vb faster then other languages.
6. The is really only one style for writting classic asp (ignoring com object development) instead of the 3+ for writing asp.
Reasons for not using standard asp.
1. Out dated.
2. Only works with Microsoft.
... others?
Feel free to PM me any specific questions.|||the reason I was thinking of ASP.Net is because I was thinking of using it as a front end instead of access xp. But I need to learn how to use it first. Anyone know any good books or any good software packages to get me started? Advice is always nice too :):):)
thank you|||well it really depends in how you want to write it. Do you want to use inline code, code behind or an application? Three very different approaches... all with different pros and cons.
Do you know html well enough to be up to the job?
Have you written any asp or vb before?
Your idea is perfectly workable, but to point you in the right direct means you really need to have a base understanding of how asp.Net works in the first place.|||I have created webpages with HTML several times, cascading styles sheets etc. Asp.net is new to me though|||You might want to start with asp then (as oposed to asp.net) or php just to get the ideas down before leaping into the gapping chasm of .Net.|||Personally, I would not recommend classic asp. What is your development background - what languages have you programmed in - c, c++, Java, vb ... ?|||Okie, my reasons for recommending standard asp are as follows.
1. The language is a piece of cake to learn.
2. Because the language is easy you can concentrate on the important things like the theorys and practises behind write web applications.
3. Because you can learn the langauge quickly and thus pick up the concepts faster you can soon decide if a web app is really what you want.
4. If you know a C based language then vb is childs play.
5. If you don't know a C based language then you should be able to pick up vb faster then other languages.
6. The is really only one style for writting classic asp (ignoring com object development) instead of the 3+ for writing asp.
Reasons for not using standard asp.
1. Out dated.
2. Only works with Microsoft.
... others?
Subscribe to:
Posts (Atom)