DB Backup on a compressed drive

Is this a good idea to use compressed drive (Windows NTFS compression) for D
B
backup files?
What is the common practice and what are the risks?
Thanks,
VladVlad,
See http://support.microsoft.com/defaul...b;en-us;231347.
"Performing SQL Server database backups to disk files on compressed volumes
is also not recommended."
HTH
Jerry
"Vlad" <Vlad@.discussions.microsoft.com> wrote in message
news:579552B6-8EF5-4741-8F7E-B04BF8EABEF6@.microsoft.com...
> Is this a good idea to use compressed drive (Windows NTFS compression) for
> DB
> backup files?
> What is the common practice and what are the risks?
> --
> Thanks,
> Vlad|||That article applies to the database files themselves, not the backups.
Personally, I prefer not to use compressed drives for backup. I prefer
using a commercial SQL backup compression tool. This decreases the time to
get the data out of SQL, thus minimizing the impact on the host system.
Since I also prefer backing up directly to a network file share, this has
the additional benefit of reducing the network traffic.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:uU8t$dWuFHA.3236@.TK2MSFTNGP14.phx.gbl...
> Vlad,
> See http://support.microsoft.com/defaul...b;en-us;231347.
> "Performing SQL Server database backups to disk files on compressed
> volumes is also not recommended."
> HTH
> Jerry
> "Vlad" <Vlad@.discussions.microsoft.com> wrote in message
> news:579552B6-8EF5-4741-8F7E-B04BF8EABEF6@.microsoft.com...
>|||Geoff,
From the article:
"Performing SQL Server database backups to disk files on compressed volumes
is also not recommended. The failure modes are still under investigation,
but some evidence indicates that this backup is less reliable than backing
up to regular non-compressed volumes. If backups to compressed volumes are
performed, a complete DBCC should be run before each backup and after each
restore. The time required to run the DBCC should be factored into the
decision to use this backup technique. "
Jerry
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:OCyqJnWuFHA.1132@.TK2MSFTNGP10.phx.gbl...
> That article applies to the database files themselves, not the backups.
> Personally, I prefer not to use compressed drives for backup. I prefer
> using a commercial SQL backup compression tool. This decreases the time
> to get the data out of SQL, thus minimizing the impact on the host system.
> Since I also prefer backing up directly to a network file share, this has
> the additional benefit of reducing the network traffic.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:uU8t$dWuFHA.3236@.TK2MSFTNGP14.phx.gbl...
>sql

DB Backup on a compressed drive

Is this a good idea to use compressed drive (Windows NTFS compression) for DB
backup files?
What is the common practice and what are the risks?
Thanks,
Vlad
Vlad,
See http://support.microsoft.com/default...;en-us;231347.
"Performing SQL Server database backups to disk files on compressed volumes
is also not recommended."
HTH
Jerry
"Vlad" <Vlad@.discussions.microsoft.com> wrote in message
news:579552B6-8EF5-4741-8F7E-B04BF8EABEF6@.microsoft.com...
> Is this a good idea to use compressed drive (Windows NTFS compression) for
> DB
> backup files?
> What is the common practice and what are the risks?
> --
> Thanks,
> Vlad
|||That article applies to the database files themselves, not the backups.
Personally, I prefer not to use compressed drives for backup. I prefer
using a commercial SQL backup compression tool. This decreases the time to
get the data out of SQL, thus minimizing the impact on the host system.
Since I also prefer backing up directly to a network file share, this has
the additional benefit of reducing the network traffic.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:uU8t$dWuFHA.3236@.TK2MSFTNGP14.phx.gbl...
> Vlad,
> See http://support.microsoft.com/default...;en-us;231347.
> "Performing SQL Server database backups to disk files on compressed
> volumes is also not recommended."
> HTH
> Jerry
> "Vlad" <Vlad@.discussions.microsoft.com> wrote in message
> news:579552B6-8EF5-4741-8F7E-B04BF8EABEF6@.microsoft.com...
>
|||Geoff,
From the article:
"Performing SQL Server database backups to disk files on compressed volumes
is also not recommended. The failure modes are still under investigation,
but some evidence indicates that this backup is less reliable than backing
up to regular non-compressed volumes. If backups to compressed volumes are
performed, a complete DBCC should be run before each backup and after each
restore. The time required to run the DBCC should be factored into the
decision to use this backup technique. "
Jerry
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:OCyqJnWuFHA.1132@.TK2MSFTNGP10.phx.gbl...
> That article applies to the database files themselves, not the backups.
> Personally, I prefer not to use compressed drives for backup. I prefer
> using a commercial SQL backup compression tool. This decreases the time
> to get the data out of SQL, thus minimizing the impact on the host system.
> Since I also prefer backing up directly to a network file share, this has
> the additional benefit of reducing the network traffic.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:uU8t$dWuFHA.3236@.TK2MSFTNGP14.phx.gbl...
>

DB Backup on a compressed drive

Is this a good idea to use compressed drive (Windows NTFS compression) for DB
backup files?
What is the common practice and what are the risks?
--
Thanks,
VladVlad,
See http://support.microsoft.com/default.aspx?scid=kb;en-us;231347.
"Performing SQL Server database backups to disk files on compressed volumes
is also not recommended."
HTH
Jerry
"Vlad" <Vlad@.discussions.microsoft.com> wrote in message
news:579552B6-8EF5-4741-8F7E-B04BF8EABEF6@.microsoft.com...
> Is this a good idea to use compressed drive (Windows NTFS compression) for
> DB
> backup files?
> What is the common practice and what are the risks?
> --
> Thanks,
> Vlad|||That article applies to the database files themselves, not the backups.
Personally, I prefer not to use compressed drives for backup. I prefer
using a commercial SQL backup compression tool. This decreases the time to
get the data out of SQL, thus minimizing the impact on the host system.
Since I also prefer backing up directly to a network file share, this has
the additional benefit of reducing the network traffic.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:uU8t$dWuFHA.3236@.TK2MSFTNGP14.phx.gbl...
> Vlad,
> See http://support.microsoft.com/default.aspx?scid=kb;en-us;231347.
> "Performing SQL Server database backups to disk files on compressed
> volumes is also not recommended."
> HTH
> Jerry
> "Vlad" <Vlad@.discussions.microsoft.com> wrote in message
> news:579552B6-8EF5-4741-8F7E-B04BF8EABEF6@.microsoft.com...
>> Is this a good idea to use compressed drive (Windows NTFS compression)
>> for DB
>> backup files?
>> What is the common practice and what are the risks?
>> --
>> Thanks,
>> Vlad
>|||Geoff,
From the article:
"Performing SQL Server database backups to disk files on compressed volumes
is also not recommended. The failure modes are still under investigation,
but some evidence indicates that this backup is less reliable than backing
up to regular non-compressed volumes. If backups to compressed volumes are
performed, a complete DBCC should be run before each backup and after each
restore. The time required to run the DBCC should be factored into the
decision to use this backup technique. "
Jerry
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:OCyqJnWuFHA.1132@.TK2MSFTNGP10.phx.gbl...
> That article applies to the database files themselves, not the backups.
> Personally, I prefer not to use compressed drives for backup. I prefer
> using a commercial SQL backup compression tool. This decreases the time
> to get the data out of SQL, thus minimizing the impact on the host system.
> Since I also prefer backing up directly to a network file share, this has
> the additional benefit of reducing the network traffic.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:uU8t$dWuFHA.3236@.TK2MSFTNGP14.phx.gbl...
>> Vlad,
>> See http://support.microsoft.com/default.aspx?scid=kb;en-us;231347.
>> "Performing SQL Server database backups to disk files on compressed
>> volumes is also not recommended."
>> HTH
>> Jerry
>> "Vlad" <Vlad@.discussions.microsoft.com> wrote in message
>> news:579552B6-8EF5-4741-8F7E-B04BF8EABEF6@.microsoft.com...
>> Is this a good idea to use compressed drive (Windows NTFS compression)
>> for DB
>> backup files?
>> What is the common practice and what are the risks?
>> --
>> Thanks,
>> Vlad
>>
>

Datetime format regardless regional settings

Hi,

in tsql there is a common format for datetime (regardless regional settings)?

I use : 'mm/dd/yyyy' to access cols in database,

but someone says that the right one is: yyyy-mm-dd.

Any suggestion about that?

Thank a lot

I believe you want the ISO format -- 112; that is yymmdd. Can somebody double check this?

Dave

|||

yes dave.. 112 is the ansi unseperated date format.....

so best option is to get all ur date in this format: select convert(varchar, getdate(), 112)

|||

Hi,

I tried thid on nortwind DB (date are: dd/mm/yyyy)

select * from orders where orderdate < '19960704'

this does work, no data are retrieved,

but works for:

select * from orders where orderdate < '07/04/1996'

select * from orders where orderdate < '1996-07-04'

both select one row (if nortwind is the original one)

?

any suggestion?

All extract the same regardless the regional settings?

|||

Fast:

I tried all three queries against my copy of the northwind database. The lowest date that I have in the orders table is the date '07/04/1996'. For this reason, I do not get any rows returned with any of the select statements. However, when I change the "less than" operator to a "less than or equal to" operator I get the expected row. I am not getting the same results as you. I am afraid I can't help on this one. Sorry fo adding to confusion.

Dave

|||

hmm..works fine with me...the lowest date in northwind is..1996-07-04 00:00:00.000..

i get 0 rows for all the queries u mentiones...and 1 each when i use <= ..

neways...wat do u want to achieve exactally....see 112 is the ansi date format, which shud be used for date conversion and comparisons as its the standard....select convert(varchar(10), getdate(), 112) ...

yyyy-mm-dd +time is how sql server present it as..

|||

Sorry for having written too fast,

all three select retrieve 1 record if date is minor than 5 july 1996,

I have tried all queries with several regional settings and everything work.

If anyone has other suggestion, is welcome

Besides in internet there are this interestings articles:

http://classicasp.aspfaq.com/date-time-routines-manipulation/how-do-i-delimit/format-dates-for-database-entry.html

and

http://www.karaszi.com/SQLServer/info_datetime.asp

Thank