I am configuring a new db server and have a couple best practice questions.
I read somewhere that a server confgured for SQL server should have its OS
and transation log on a RAID 1 array, and the data file on 1 or more RAID 5
arrays.
1.) Is having a RAID 1 array for the OS and transaction log much better than
having just one RAID 5 array that has the OS, transaction log, and data files?
2.) We are recoding our web application to store pictures and files in the
database rather than on the web servers file system. Our application does a
lot of loading and displaying of photos. How resource intensive is saving a
photo and retreiving it from the database? Will it greatly slow down
non-photo transactions? Would I be wise to put all the photos and files (blob
data) on thier own RAID 5 array?
"Dan" wrote in message
news:C73453B5-76E7-4D6C-8A58-9ED1AC11A87A@.microsoft.com...
:I am configuring a new db server and have a couple best practice questions.
:
: I read somewhere that a server confgured for SQL server should have its OS
: and transation log on a RAID 1 array, and the data file on 1 or more RAID
5
: arrays.
:
: 1.) Is having a RAID 1 array for the OS and transaction log much better
than
: having just one RAID 5 array that has the OS, transaction log, and data
files?
:
: 2.) We are recoding our web application to store pictures and files in the
: database rather than on the web servers file system. Our application does
a
: lot of loading and displaying of photos. How resource intensive is saving
a
: photo and retreiving it from the database? Will it greatly slow down
: non-photo transactions? Would I be wise to put all the photos and files
(blob
: data) on thier own RAID 5 array?
Here is the idea:
RAID 1 (mirroring, duplexing) writes fast, reads normal
RAID 5 (distributed data guarding with parity) writes slow, reads very fast
Log files are written to more often than read from. RAID 1 is a performance
increase over RAID 5.
Data files are read more often than written to. RAID 5 is a performance
increase over RAID 1.
Saving a blob in a database is a waste, IMHO. Save the file on the data
drive and store a link to it in the database.
If your goal is performance, blobs are counter productive.
HTH...
Roland Hall
/* This information is distributed in the hope that it will be useful, but
without any warranty; without even the implied warranty of merchantability
or fitness for a particular purpose. */
Technet Script Center - http://www.microsoft.com/technet/scriptcenter/
WSH 5.6 Documentation - http://msdn.microsoft.com/downloads/list/webdev.asp
MSDN Library - http://msdn.microsoft.com/library/default.asp
Showing posts with label couple. Show all posts
Showing posts with label couple. Show all posts
Tuesday, March 27, 2012
best SCSI config
I am configuring a new db server and have a couple best practice questions.
I read somewhere that a server confgured for SQL server should have its OS
and transation log on a RAID 1 array, and the data file on 1 or more RAID 5
arrays.
1.) Is having a RAID 1 array for the OS and transaction log much better than
having just one RAID 5 array that has the OS, transaction log, and data file
s?
2.) We are recoding our web application to store pictures and files in the
database rather than on the web servers file system. Our application does a
lot of loading and displaying of photos. How resource intensive is saving a
photo and retreiving it from the database? Will it greatly slow down
non-photo transactions? Would I be wise to put all the photos and files (blo
b
data) on thier own RAID 5 array?"Dan" wrote in message
news:C73453B5-76E7-4D6C-8A58-9ED1AC11A87A@.microsoft.com...
:I am configuring a new db server and have a couple best practice questions.
:
: I read somewhere that a server confgured for SQL server should have its OS
: and transation log on a RAID 1 array, and the data file on 1 or more RAID
5
: arrays.
:
: 1.) Is having a RAID 1 array for the OS and transaction log much better
than
: having just one RAID 5 array that has the OS, transaction log, and data
files?
:
: 2.) We are recoding our web application to store pictures and files in the
: database rather than on the web servers file system. Our application does
a
: lot of loading and displaying of photos. How resource intensive is saving
a
: photo and retreiving it from the database? Will it greatly slow down
: non-photo transactions? Would I be wise to put all the photos and files
(blob
: data) on thier own RAID 5 array?
Here is the idea:
RAID 1 (mirroring, duplexing) writes fast, reads normal
RAID 5 (distributed data guarding with parity) writes slow, reads very fast
Log files are written to more often than read from. RAID 1 is a performance
increase over RAID 5.
Data files are read more often than written to. RAID 5 is a performance
increase over RAID 1.
Saving a blob in a database is a waste, IMHO. Save the file on the data
drive and store a link to it in the database.
If your goal is performance, blobs are counter productive.
HTH...
Roland Hall
/* This information is distributed in the hope that it will be useful, but
without any warranty; without even the implied warranty of merchantability
or fitness for a particular purpose. */
Technet Script Center - http://www.microsoft.com/technet/scriptcenter/
WSH 5.6 Documentation - http://msdn.microsoft.com/downloads/list/webdev.asp
MSDN Library - http://msdn.microsoft.com/library/default.asp
I read somewhere that a server confgured for SQL server should have its OS
and transation log on a RAID 1 array, and the data file on 1 or more RAID 5
arrays.
1.) Is having a RAID 1 array for the OS and transaction log much better than
having just one RAID 5 array that has the OS, transaction log, and data file
s?
2.) We are recoding our web application to store pictures and files in the
database rather than on the web servers file system. Our application does a
lot of loading and displaying of photos. How resource intensive is saving a
photo and retreiving it from the database? Will it greatly slow down
non-photo transactions? Would I be wise to put all the photos and files (blo
b
data) on thier own RAID 5 array?"Dan" wrote in message
news:C73453B5-76E7-4D6C-8A58-9ED1AC11A87A@.microsoft.com...
:I am configuring a new db server and have a couple best practice questions.
:
: I read somewhere that a server confgured for SQL server should have its OS
: and transation log on a RAID 1 array, and the data file on 1 or more RAID
5
: arrays.
:
: 1.) Is having a RAID 1 array for the OS and transaction log much better
than
: having just one RAID 5 array that has the OS, transaction log, and data
files?
:
: 2.) We are recoding our web application to store pictures and files in the
: database rather than on the web servers file system. Our application does
a
: lot of loading and displaying of photos. How resource intensive is saving
a
: photo and retreiving it from the database? Will it greatly slow down
: non-photo transactions? Would I be wise to put all the photos and files
(blob
: data) on thier own RAID 5 array?
Here is the idea:
RAID 1 (mirroring, duplexing) writes fast, reads normal
RAID 5 (distributed data guarding with parity) writes slow, reads very fast
Log files are written to more often than read from. RAID 1 is a performance
increase over RAID 5.
Data files are read more often than written to. RAID 5 is a performance
increase over RAID 1.
Saving a blob in a database is a waste, IMHO. Save the file on the data
drive and store a link to it in the database.
If your goal is performance, blobs are counter productive.
HTH...
Roland Hall
/* This information is distributed in the hope that it will be useful, but
without any warranty; without even the implied warranty of merchantability
or fitness for a particular purpose. */
Technet Script Center - http://www.microsoft.com/technet/scriptcenter/
WSH 5.6 Documentation - http://msdn.microsoft.com/downloads/list/webdev.asp
MSDN Library - http://msdn.microsoft.com/library/default.asp
best SCSI config
I am configuring a new db server and have a couple best practice questions.
I read somewhere that a server confgured for SQL server should have its OS
and transation log on a RAID 1 array, and the data file on 1 or more RAID 5
arrays.
1.) Is having a RAID 1 array for the OS and transaction log much better than
having just one RAID 5 array that has the OS, transaction log, and data files?
2.) We are recoding our web application to store pictures and files in the
database rather than on the web servers file system. Our application does a
lot of loading and displaying of photos. How resource intensive is saving a
photo and retreiving it from the database? Will it greatly slow down
non-photo transactions? Would I be wise to put all the photos and files (blob
data) on thier own RAID 5 array?"Dan" wrote in message
news:C73453B5-76E7-4D6C-8A58-9ED1AC11A87A@.microsoft.com...
:I am configuring a new db server and have a couple best practice questions.
:
: I read somewhere that a server confgured for SQL server should have its OS
: and transation log on a RAID 1 array, and the data file on 1 or more RAID
5
: arrays.
:
: 1.) Is having a RAID 1 array for the OS and transaction log much better
than
: having just one RAID 5 array that has the OS, transaction log, and data
files?
:
: 2.) We are recoding our web application to store pictures and files in the
: database rather than on the web servers file system. Our application does
a
: lot of loading and displaying of photos. How resource intensive is saving
a
: photo and retreiving it from the database? Will it greatly slow down
: non-photo transactions? Would I be wise to put all the photos and files
(blob
: data) on thier own RAID 5 array?
Here is the idea:
RAID 1 (mirroring, duplexing) writes fast, reads normal
RAID 5 (distributed data guarding with parity) writes slow, reads very fast
Log files are written to more often than read from. RAID 1 is a performance
increase over RAID 5.
Data files are read more often than written to. RAID 5 is a performance
increase over RAID 1.
Saving a blob in a database is a waste, IMHO. Save the file on the data
drive and store a link to it in the database.
If your goal is performance, blobs are counter productive.
HTH...
--
Roland Hall
/* This information is distributed in the hope that it will be useful, but
without any warranty; without even the implied warranty of merchantability
or fitness for a particular purpose. */
Technet Script Center - http://www.microsoft.com/technet/scriptcenter/
WSH 5.6 Documentation - http://msdn.microsoft.com/downloads/list/webdev.asp
MSDN Library - http://msdn.microsoft.com/library/default.asp
I read somewhere that a server confgured for SQL server should have its OS
and transation log on a RAID 1 array, and the data file on 1 or more RAID 5
arrays.
1.) Is having a RAID 1 array for the OS and transaction log much better than
having just one RAID 5 array that has the OS, transaction log, and data files?
2.) We are recoding our web application to store pictures and files in the
database rather than on the web servers file system. Our application does a
lot of loading and displaying of photos. How resource intensive is saving a
photo and retreiving it from the database? Will it greatly slow down
non-photo transactions? Would I be wise to put all the photos and files (blob
data) on thier own RAID 5 array?"Dan" wrote in message
news:C73453B5-76E7-4D6C-8A58-9ED1AC11A87A@.microsoft.com...
:I am configuring a new db server and have a couple best practice questions.
:
: I read somewhere that a server confgured for SQL server should have its OS
: and transation log on a RAID 1 array, and the data file on 1 or more RAID
5
: arrays.
:
: 1.) Is having a RAID 1 array for the OS and transaction log much better
than
: having just one RAID 5 array that has the OS, transaction log, and data
files?
:
: 2.) We are recoding our web application to store pictures and files in the
: database rather than on the web servers file system. Our application does
a
: lot of loading and displaying of photos. How resource intensive is saving
a
: photo and retreiving it from the database? Will it greatly slow down
: non-photo transactions? Would I be wise to put all the photos and files
(blob
: data) on thier own RAID 5 array?
Here is the idea:
RAID 1 (mirroring, duplexing) writes fast, reads normal
RAID 5 (distributed data guarding with parity) writes slow, reads very fast
Log files are written to more often than read from. RAID 1 is a performance
increase over RAID 5.
Data files are read more often than written to. RAID 5 is a performance
increase over RAID 1.
Saving a blob in a database is a waste, IMHO. Save the file on the data
drive and store a link to it in the database.
If your goal is performance, blobs are counter productive.
HTH...
--
Roland Hall
/* This information is distributed in the hope that it will be useful, but
without any warranty; without even the implied warranty of merchantability
or fitness for a particular purpose. */
Technet Script Center - http://www.microsoft.com/technet/scriptcenter/
WSH 5.6 Documentation - http://msdn.microsoft.com/downloads/list/webdev.asp
MSDN Library - http://msdn.microsoft.com/library/default.asp
Saturday, February 25, 2012
Best Export format for long term compatibility
We have a couple of databases about 5G each and around 300 tables in each
databases.We have to keep a yealry backup for 6 years. Also we might be
converting to Oracle in about 2 years. If we want to store the SQL Server
tables information in a compatible format that would be restorable in a
couple of years, what would be the recommended Export format ?
Thanks
Delimited ASCII text is just about the only format that never seems to go
out of style...
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"SQL Server newbie" <SQLServernewbie@.discussions.microsoft.com> wrote in
message news:D55CFF19-39E5-4707-B515-079A7F5EAA4B@.microsoft.com...
> We have a couple of databases about 5G each and around 300 tables in each
> databases.We have to keep a yealry backup for 6 years. Also we might be
> converting to Oracle in about 2 years. If we want to store the SQL Server
> tables information in a compatible format that would be restorable in a
> couple of years, what would be the recommended Export format ?
> Thanks
|||Adam,
Thanks for the info. But when I choose ASCII Text as an output format it
seems to only pickup one table at a time. Is thre any way to specify all
tables or multiple tables during the Export ?
Thanks
"Adam Machanic" wrote:
> Delimited ASCII text is just about the only format that never seems to go
> out of style...
>
> --
> Adam Machanic
> SQL Server MVP
> Author, "Expert SQL Server 2005 Development"
> http://www.apress.com/book/bookDisplay.html?bID=10220
>
> "SQL Server newbie" <SQLServernewbie@.discussions.microsoft.com> wrote in
> message news:D55CFF19-39E5-4707-B515-079A7F5EAA4B@.microsoft.com...
>
|||My suggestion for ASCII text was really more of a joke than anything
else--suggesting the one format you KNOW will still work. But based on what
you posted, I would just stick with a standard SQL Server backup. You can
always re-install a copy of SQL Server and get the data from there, even if
you switch to Oracle.
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"SQL Server newbie" <SQLServernewbie@.discussions.microsoft.com> wrote in
message news:1CA39E77-19FB-47EC-8E22-CBE6348A5647@.microsoft.com...[vbcol=seagreen]
> Adam,
> Thanks for the info. But when I choose ASCII Text as an output format it
> seems to only pickup one table at a time. Is thre any way to specify all
> tables or multiple tables during the Export ?
> Thanks
> "Adam Machanic" wrote:
databases.We have to keep a yealry backup for 6 years. Also we might be
converting to Oracle in about 2 years. If we want to store the SQL Server
tables information in a compatible format that would be restorable in a
couple of years, what would be the recommended Export format ?
Thanks
Delimited ASCII text is just about the only format that never seems to go
out of style...
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"SQL Server newbie" <SQLServernewbie@.discussions.microsoft.com> wrote in
message news:D55CFF19-39E5-4707-B515-079A7F5EAA4B@.microsoft.com...
> We have a couple of databases about 5G each and around 300 tables in each
> databases.We have to keep a yealry backup for 6 years. Also we might be
> converting to Oracle in about 2 years. If we want to store the SQL Server
> tables information in a compatible format that would be restorable in a
> couple of years, what would be the recommended Export format ?
> Thanks
|||Adam,
Thanks for the info. But when I choose ASCII Text as an output format it
seems to only pickup one table at a time. Is thre any way to specify all
tables or multiple tables during the Export ?
Thanks
"Adam Machanic" wrote:
> Delimited ASCII text is just about the only format that never seems to go
> out of style...
>
> --
> Adam Machanic
> SQL Server MVP
> Author, "Expert SQL Server 2005 Development"
> http://www.apress.com/book/bookDisplay.html?bID=10220
>
> "SQL Server newbie" <SQLServernewbie@.discussions.microsoft.com> wrote in
> message news:D55CFF19-39E5-4707-B515-079A7F5EAA4B@.microsoft.com...
>
|||My suggestion for ASCII text was really more of a joke than anything
else--suggesting the one format you KNOW will still work. But based on what
you posted, I would just stick with a standard SQL Server backup. You can
always re-install a copy of SQL Server and get the data from there, even if
you switch to Oracle.
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"SQL Server newbie" <SQLServernewbie@.discussions.microsoft.com> wrote in
message news:1CA39E77-19FB-47EC-8E22-CBE6348A5647@.microsoft.com...[vbcol=seagreen]
> Adam,
> Thanks for the info. But when I choose ASCII Text as an output format it
> seems to only pickup one table at a time. Is thre any way to specify all
> tables or multiple tables during the Export ?
> Thanks
> "Adam Machanic" wrote:
Best Export format for long term compatibility
We have a couple of databases about 5G each and around 300 tables in each
databases.We have to keep a yealry backup for 6 years. Also we might be
converting to Oracle in about 2 years. If we want to store the SQL Server
tables information in a compatible format that would be restorable in a
couple of years, what would be the recommended Export format ?
ThanksDelimited ASCII text is just about the only format that never seems to go
out of style...
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"SQL Server newbie" <SQLServernewbie@.discussions.microsoft.com> wrote in
message news:D55CFF19-39E5-4707-B515-079A7F5EAA4B@.microsoft.com...
> We have a couple of databases about 5G each and around 300 tables in each
> databases.We have to keep a yealry backup for 6 years. Also we might be
> converting to Oracle in about 2 years. If we want to store the SQL Server
> tables information in a compatible format that would be restorable in a
> couple of years, what would be the recommended Export format ?
> Thanks|||Adam,
Thanks for the info. But when I choose ASCII Text as an output format it
seems to only pickup one table at a time. Is thre any way to specify all
tables or multiple tables during the Export ?
Thanks
"Adam Machanic" wrote:
> Delimited ASCII text is just about the only format that never seems to go
> out of style...
>
> --
> Adam Machanic
> SQL Server MVP
> Author, "Expert SQL Server 2005 Development"
> http://www.apress.com/book/bookDisplay.html?bID=10220
>
> "SQL Server newbie" <SQLServernewbie@.discussions.microsoft.com> wrote in
> message news:D55CFF19-39E5-4707-B515-079A7F5EAA4B@.microsoft.com...
> > We have a couple of databases about 5G each and around 300 tables in each
> > databases.We have to keep a yealry backup for 6 years. Also we might be
> > converting to Oracle in about 2 years. If we want to store the SQL Server
> > tables information in a compatible format that would be restorable in a
> > couple of years, what would be the recommended Export format ?
> > Thanks
>|||My suggestion for ASCII text was really more of a joke than anything
else--suggesting the one format you KNOW will still work. But based on what
you posted, I would just stick with a standard SQL Server backup. You can
always re-install a copy of SQL Server and get the data from there, even if
you switch to Oracle.
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"SQL Server newbie" <SQLServernewbie@.discussions.microsoft.com> wrote in
message news:1CA39E77-19FB-47EC-8E22-CBE6348A5647@.microsoft.com...
> Adam,
> Thanks for the info. But when I choose ASCII Text as an output format it
> seems to only pickup one table at a time. Is thre any way to specify all
> tables or multiple tables during the Export ?
> Thanks
> "Adam Machanic" wrote:
>> Delimited ASCII text is just about the only format that never seems to go
>> out of style...
>>
>> --
>> Adam Machanic
>> SQL Server MVP
>> Author, "Expert SQL Server 2005 Development"
>> http://www.apress.com/book/bookDisplay.html?bID=10220
>>
>> "SQL Server newbie" <SQLServernewbie@.discussions.microsoft.com> wrote in
>> message news:D55CFF19-39E5-4707-B515-079A7F5EAA4B@.microsoft.com...
>> > We have a couple of databases about 5G each and around 300 tables in
>> > each
>> > databases.We have to keep a yealry backup for 6 years. Also we might be
>> > converting to Oracle in about 2 years. If we want to store the SQL
>> > Server
>> > tables information in a compatible format that would be restorable in a
>> > couple of years, what would be the recommended Export format ?
>> > Thanks
databases.We have to keep a yealry backup for 6 years. Also we might be
converting to Oracle in about 2 years. If we want to store the SQL Server
tables information in a compatible format that would be restorable in a
couple of years, what would be the recommended Export format ?
ThanksDelimited ASCII text is just about the only format that never seems to go
out of style...
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"SQL Server newbie" <SQLServernewbie@.discussions.microsoft.com> wrote in
message news:D55CFF19-39E5-4707-B515-079A7F5EAA4B@.microsoft.com...
> We have a couple of databases about 5G each and around 300 tables in each
> databases.We have to keep a yealry backup for 6 years. Also we might be
> converting to Oracle in about 2 years. If we want to store the SQL Server
> tables information in a compatible format that would be restorable in a
> couple of years, what would be the recommended Export format ?
> Thanks|||Adam,
Thanks for the info. But when I choose ASCII Text as an output format it
seems to only pickup one table at a time. Is thre any way to specify all
tables or multiple tables during the Export ?
Thanks
"Adam Machanic" wrote:
> Delimited ASCII text is just about the only format that never seems to go
> out of style...
>
> --
> Adam Machanic
> SQL Server MVP
> Author, "Expert SQL Server 2005 Development"
> http://www.apress.com/book/bookDisplay.html?bID=10220
>
> "SQL Server newbie" <SQLServernewbie@.discussions.microsoft.com> wrote in
> message news:D55CFF19-39E5-4707-B515-079A7F5EAA4B@.microsoft.com...
> > We have a couple of databases about 5G each and around 300 tables in each
> > databases.We have to keep a yealry backup for 6 years. Also we might be
> > converting to Oracle in about 2 years. If we want to store the SQL Server
> > tables information in a compatible format that would be restorable in a
> > couple of years, what would be the recommended Export format ?
> > Thanks
>|||My suggestion for ASCII text was really more of a joke than anything
else--suggesting the one format you KNOW will still work. But based on what
you posted, I would just stick with a standard SQL Server backup. You can
always re-install a copy of SQL Server and get the data from there, even if
you switch to Oracle.
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"SQL Server newbie" <SQLServernewbie@.discussions.microsoft.com> wrote in
message news:1CA39E77-19FB-47EC-8E22-CBE6348A5647@.microsoft.com...
> Adam,
> Thanks for the info. But when I choose ASCII Text as an output format it
> seems to only pickup one table at a time. Is thre any way to specify all
> tables or multiple tables during the Export ?
> Thanks
> "Adam Machanic" wrote:
>> Delimited ASCII text is just about the only format that never seems to go
>> out of style...
>>
>> --
>> Adam Machanic
>> SQL Server MVP
>> Author, "Expert SQL Server 2005 Development"
>> http://www.apress.com/book/bookDisplay.html?bID=10220
>>
>> "SQL Server newbie" <SQLServernewbie@.discussions.microsoft.com> wrote in
>> message news:D55CFF19-39E5-4707-B515-079A7F5EAA4B@.microsoft.com...
>> > We have a couple of databases about 5G each and around 300 tables in
>> > each
>> > databases.We have to keep a yealry backup for 6 years. Also we might be
>> > converting to Oracle in about 2 years. If we want to store the SQL
>> > Server
>> > tables information in a compatible format that would be restorable in a
>> > couple of years, what would be the recommended Export format ?
>> > Thanks
Best Export format for long term compatibility
We have a couple of databases about 5G each and around 300 tables in each
databases.We have to keep a yealry backup for 6 years. Also we might be
converting to Oracle in about 2 years. If we want to store the SQL Server
tables information in a compatible format that would be restorable in a
couple of years, what would be the recommended Export format ?
ThanksDelimited ASCII text is just about the only format that never seems to go
out of style...
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"SQL Server newbie" <SQLServernewbie@.discussions.microsoft.com> wrote in
message news:D55CFF19-39E5-4707-B515-079A7F5EAA4B@.microsoft.com...
> We have a couple of databases about 5G each and around 300 tables in each
> databases.We have to keep a yealry backup for 6 years. Also we might be
> converting to Oracle in about 2 years. If we want to store the SQL Server
> tables information in a compatible format that would be restorable in a
> couple of years, what would be the recommended Export format ?
> Thanks|||Adam,
Thanks for the info. But when I choose ASCII Text as an output format it
seems to only pickup one table at a time. Is thre any way to specify all
tables or multiple tables during the Export ?
Thanks
"Adam Machanic" wrote:
> Delimited ASCII text is just about the only format that never seems to go
> out of style...
>
> --
> Adam Machanic
> SQL Server MVP
> Author, "Expert SQL Server 2005 Development"
> http://www.apress.com/book/bookDisplay.html?bID=10220
>
> "SQL Server newbie" <SQLServernewbie@.discussions.microsoft.com> wrote in
> message news:D55CFF19-39E5-4707-B515-079A7F5EAA4B@.microsoft.com...
>|||My suggestion for ASCII text was really more of a joke than anything
else--suggesting the one format you KNOW will still work. But based on what
you posted, I would just stick with a standard SQL Server backup. You can
always re-install a copy of SQL Server and get the data from there, even if
you switch to Oracle.
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"SQL Server newbie" <SQLServernewbie@.discussions.microsoft.com> wrote in
message news:1CA39E77-19FB-47EC-8E22-CBE6348A5647@.microsoft.com...[vbcol=seagreen]
> Adam,
> Thanks for the info. But when I choose ASCII Text as an output format it
> seems to only pickup one table at a time. Is thre any way to specify all
> tables or multiple tables during the Export ?
> Thanks
> "Adam Machanic" wrote:
>
databases.We have to keep a yealry backup for 6 years. Also we might be
converting to Oracle in about 2 years. If we want to store the SQL Server
tables information in a compatible format that would be restorable in a
couple of years, what would be the recommended Export format ?
ThanksDelimited ASCII text is just about the only format that never seems to go
out of style...
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"SQL Server newbie" <SQLServernewbie@.discussions.microsoft.com> wrote in
message news:D55CFF19-39E5-4707-B515-079A7F5EAA4B@.microsoft.com...
> We have a couple of databases about 5G each and around 300 tables in each
> databases.We have to keep a yealry backup for 6 years. Also we might be
> converting to Oracle in about 2 years. If we want to store the SQL Server
> tables information in a compatible format that would be restorable in a
> couple of years, what would be the recommended Export format ?
> Thanks|||Adam,
Thanks for the info. But when I choose ASCII Text as an output format it
seems to only pickup one table at a time. Is thre any way to specify all
tables or multiple tables during the Export ?
Thanks
"Adam Machanic" wrote:
> Delimited ASCII text is just about the only format that never seems to go
> out of style...
>
> --
> Adam Machanic
> SQL Server MVP
> Author, "Expert SQL Server 2005 Development"
> http://www.apress.com/book/bookDisplay.html?bID=10220
>
> "SQL Server newbie" <SQLServernewbie@.discussions.microsoft.com> wrote in
> message news:D55CFF19-39E5-4707-B515-079A7F5EAA4B@.microsoft.com...
>|||My suggestion for ASCII text was really more of a joke than anything
else--suggesting the one format you KNOW will still work. But based on what
you posted, I would just stick with a standard SQL Server backup. You can
always re-install a copy of SQL Server and get the data from there, even if
you switch to Oracle.
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"SQL Server newbie" <SQLServernewbie@.discussions.microsoft.com> wrote in
message news:1CA39E77-19FB-47EC-8E22-CBE6348A5647@.microsoft.com...[vbcol=seagreen]
> Adam,
> Thanks for the info. But when I choose ASCII Text as an output format it
> seems to only pickup one table at a time. Is thre any way to specify all
> tables or multiple tables during the Export ?
> Thanks
> "Adam Machanic" wrote:
>
Friday, February 24, 2012
Best approach for pushing records to MS Access
All,
I am new to DTS/SSIS and have a couple of questions about using it to solve a problem. We have an application running on SQL Server 2005 where status records are written to a status table. I need to be able to send those records over to a status table in a legacy application running on Access.
Originally, I thought about writing a custom c# stored proc and accessing Access from it and then someone pointed me to DTS/SSIS.
Is there a way to exectute the package based on a trigger event that a row was inserted or updated? If not and I take a scheduled approach (every 3 minutes, etc.) do I have to maintain a column for the records that get processed so they are not picked up again.
In general is using SSIS the approach to take? The overall business requirements are straight forward, but I am not sure if SSIS is overkill for this or not.
Thanks,
Steve
If I use an Execute SQL Task on the Control Flow, how do I use that resulting dataset as a Data Source on the Data Flow? I added a variable named 0 and type object, but I cannot figure out how to reference it on the Data Flow designer tab.Monday, February 13, 2012
Beginner: How to group data?
Hello,
Thanks for reviewing my question. I am new to reporting services and
experimented with a couple of reports. I have the following data in a table:
# name slot
1 A1 1
1 B1 2
1 C1 3
2 A2 1
2 B2 2
2 C2 3
I would like to output in this fashison:
1 2
-- --
1 A1 A2
2 B1 B2
3 C1 C2
Should I be looking to use a Matrix objecjt? Do I need to use JOINs to
group rows/columns even though all my data I need is in my table?
Many Thanks
PeterA follow up:
I am not familar with how the reporting mechanism groups the data. I have a
long list of data already contained in a table.
Current list
1 Book A Action
1 Book B Drama
2 Book C Novel
2 Book D Action
Desired output
1 2
Action Book A Book D
Drama Book B
Novel Book C
Can I output the data in the desired output or do I have to do queries with
cross joins or something of that sort?
Many Thanks
Peter
"Peter" wrote:
> Hello,
> Thanks for reviewing my question. I am new to reporting services and
> experimented with a couple of reports. I have the following data in a table:
> # name slot
> 1 A1 1
> 1 B1 2
> 1 C1 3
> 2 A2 1
> 2 B2 2
> 2 C2 3
> I would like to output in this fashison:
> 1 2
> -- --
> 1 A1 A2
> 2 B1 B2
> 3 C1 C2
> Should I be looking to use a Matrix objecjt? Do I need to use JOINs to
> group rows/columns even though all my data I need is in my table?
> Many Thanks
> Peter
>
Thanks for reviewing my question. I am new to reporting services and
experimented with a couple of reports. I have the following data in a table:
# name slot
1 A1 1
1 B1 2
1 C1 3
2 A2 1
2 B2 2
2 C2 3
I would like to output in this fashison:
1 2
-- --
1 A1 A2
2 B1 B2
3 C1 C2
Should I be looking to use a Matrix objecjt? Do I need to use JOINs to
group rows/columns even though all my data I need is in my table?
Many Thanks
PeterA follow up:
I am not familar with how the reporting mechanism groups the data. I have a
long list of data already contained in a table.
Current list
1 Book A Action
1 Book B Drama
2 Book C Novel
2 Book D Action
Desired output
1 2
Action Book A Book D
Drama Book B
Novel Book C
Can I output the data in the desired output or do I have to do queries with
cross joins or something of that sort?
Many Thanks
Peter
"Peter" wrote:
> Hello,
> Thanks for reviewing my question. I am new to reporting services and
> experimented with a couple of reports. I have the following data in a table:
> # name slot
> 1 A1 1
> 1 B1 2
> 1 C1 3
> 2 A2 1
> 2 B2 2
> 2 C2 3
> I would like to output in this fashison:
> 1 2
> -- --
> 1 A1 A2
> 2 B1 B2
> 3 C1 C2
> Should I be looking to use a Matrix objecjt? Do I need to use JOINs to
> group rows/columns even though all my data I need is in my table?
> Many Thanks
> Peter
>
Beginner Questions (and introduction to forum)
I'm new to the forum as well as Report Services. I have created a couple of
sample reports and I'm monkeying with the settings. I have a book on the
way which I hope will help answer some of my questions but in the meantime I
have a couple beginner questions...
1) I want to concatenate a string in the Title of a report with a parameter
I have assigned to the report... Basically I want the title to read "Here's
the Data for 01/01/2003 thru 12/31/2003" where both of the dates in the
title are runtime parameters specified by the user.
2) Is there a way to do conditional formatting and or use constants when
formatting fields... How about records? For instance, make the value in
the field RED if it meets a certain criterion... or, make the record
background blue if the record meets a certain criterion.
Any direction you may provide or answers will be appreciated.
Thanks
REM7600#1: ="Here's the data for " & Parameters!Report_Parameter_0.Value & " to " &
Parameters!Report_Parameter_1.Value [Check
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSMAIN/htm/rsc_ov_using_v1_6foz.asp
for details]
#2: =iif(Trim(Fields!type.Value) = "business", "Red", "Blue") [Check
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_3983.asp
for details]
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"REM7600" <rem7600@.hotmail.com> wrote in message
news:uVjBs%23RaEHA.2892@.TK2MSFTNGP10.phx.gbl...
> I'm new to the forum as well as Report Services. I have created a couple
of
> sample reports and I'm monkeying with the settings. I have a book on the
> way which I hope will help answer some of my questions but in the meantime
I
> have a couple beginner questions...
> 1) I want to concatenate a string in the Title of a report with a
parameter
> I have assigned to the report... Basically I want the title to read
"Here's
> the Data for 01/01/2003 thru 12/31/2003" where both of the dates in the
> title are runtime parameters specified by the user.
> 2) Is there a way to do conditional formatting and or use constants when
> formatting fields... How about records? For instance, make the value in
> the field RED if it meets a certain criterion... or, make the record
> background blue if the record meets a certain criterion.
> Any direction you may provide or answers will be appreciated.
> Thanks
> REM7600
>|||1) Set title value to an expression:
="Here's the Data for " + CStr(Parameters!StartDate.Value) + " thru " +
CStr(Parameters!EndDate.Value)
2) You may set color or background color properties to expressions returning
color name, for example:
=iif(Fields!MyData.Value < 0, "Red", "Blue")
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"REM7600" <rem7600@.hotmail.com> wrote in message
news:uVjBs%23RaEHA.2892@.TK2MSFTNGP10.phx.gbl...
> I'm new to the forum as well as Report Services. I have created a couple
> of
> sample reports and I'm monkeying with the settings. I have a book on the
> way which I hope will help answer some of my questions but in the meantime
> I
> have a couple beginner questions...
> 1) I want to concatenate a string in the Title of a report with a
> parameter
> I have assigned to the report... Basically I want the title to read
> "Here's
> the Data for 01/01/2003 thru 12/31/2003" where both of the dates in the
> title are runtime parameters specified by the user.
> 2) Is there a way to do conditional formatting and or use constants when
> formatting fields... How about records? For instance, make the value in
> the field RED if it meets a certain criterion... or, make the record
> background blue if the record meets a certain criterion.
> Any direction you may provide or answers will be appreciated.
> Thanks
> REM7600
>|||Ravi... I could have sworn I did option 1... I do that in access all the
time. Oh well, I'll follow the KB article referenced and see what I left
out (I'm sure there's something).
Thanks for the help!
REM7600
> #1: ="Here's the data for " & Parameters!Report_Parameter_0.Value & " to "
&
> Parameters!Report_Parameter_1.Value [Check
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSMAIN/htm/rsc_ov_using_v1_6foz.asp
> for details]
> #2: =iif(Trim(Fields!type.Value) = "business", "Red", "Blue") [Check
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_3983.asp
> for details]|||I didn't try doing the Convert on the dates... Maybe that's what I did
wrong.
Thanks for the quick answers!
REM7600
> 1) Set title value to an expression:
> ="Here's the Data for " + CStr(Parameters!StartDate.Value) + " thru " +
> CStr(Parameters!EndDate.Value)
> 2) You may set color or background color properties to expressions
returning
> color name, for example:
> =iif(Fields!MyData.Value < 0, "Red", "Blue")
> --|||Note:
* if you use "+" to concatenate strings no implicit data type conversions
will happen.
* if you use "&" to concatenate strings, implicit conversions will be done
(invoking the default .ToString() implementation).
i.e. both expressions should work:
="Here's the Data for " + CStr(Parameters!StartDate.Value) + " thru " +
CStr(Parameters!EndDate.Value)
="Here's the Data for " & Parameters!StartDate.Value & " thru " &
Parameters!EndDate.Value
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"REM7600" <rem7600@.hotmail.com> wrote in message
news:OnsrcYSaEHA.1840@.TK2MSFTNGP11.phx.gbl...
> I didn't try doing the Convert on the dates... Maybe that's what I did
> wrong.
> Thanks for the quick answers!
> REM7600
>
> > 1) Set title value to an expression:
> > ="Here's the Data for " + CStr(Parameters!StartDate.Value) + " thru " +
> > CStr(Parameters!EndDate.Value)
> >
> > 2) You may set color or background color properties to expressions
> returning
> > color name, for example:
> > =iif(Fields!MyData.Value < 0, "Red", "Blue")
> >
> > --
>
sample reports and I'm monkeying with the settings. I have a book on the
way which I hope will help answer some of my questions but in the meantime I
have a couple beginner questions...
1) I want to concatenate a string in the Title of a report with a parameter
I have assigned to the report... Basically I want the title to read "Here's
the Data for 01/01/2003 thru 12/31/2003" where both of the dates in the
title are runtime parameters specified by the user.
2) Is there a way to do conditional formatting and or use constants when
formatting fields... How about records? For instance, make the value in
the field RED if it meets a certain criterion... or, make the record
background blue if the record meets a certain criterion.
Any direction you may provide or answers will be appreciated.
Thanks
REM7600#1: ="Here's the data for " & Parameters!Report_Parameter_0.Value & " to " &
Parameters!Report_Parameter_1.Value [Check
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSMAIN/htm/rsc_ov_using_v1_6foz.asp
for details]
#2: =iif(Trim(Fields!type.Value) = "business", "Red", "Blue") [Check
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_3983.asp
for details]
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"REM7600" <rem7600@.hotmail.com> wrote in message
news:uVjBs%23RaEHA.2892@.TK2MSFTNGP10.phx.gbl...
> I'm new to the forum as well as Report Services. I have created a couple
of
> sample reports and I'm monkeying with the settings. I have a book on the
> way which I hope will help answer some of my questions but in the meantime
I
> have a couple beginner questions...
> 1) I want to concatenate a string in the Title of a report with a
parameter
> I have assigned to the report... Basically I want the title to read
"Here's
> the Data for 01/01/2003 thru 12/31/2003" where both of the dates in the
> title are runtime parameters specified by the user.
> 2) Is there a way to do conditional formatting and or use constants when
> formatting fields... How about records? For instance, make the value in
> the field RED if it meets a certain criterion... or, make the record
> background blue if the record meets a certain criterion.
> Any direction you may provide or answers will be appreciated.
> Thanks
> REM7600
>|||1) Set title value to an expression:
="Here's the Data for " + CStr(Parameters!StartDate.Value) + " thru " +
CStr(Parameters!EndDate.Value)
2) You may set color or background color properties to expressions returning
color name, for example:
=iif(Fields!MyData.Value < 0, "Red", "Blue")
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"REM7600" <rem7600@.hotmail.com> wrote in message
news:uVjBs%23RaEHA.2892@.TK2MSFTNGP10.phx.gbl...
> I'm new to the forum as well as Report Services. I have created a couple
> of
> sample reports and I'm monkeying with the settings. I have a book on the
> way which I hope will help answer some of my questions but in the meantime
> I
> have a couple beginner questions...
> 1) I want to concatenate a string in the Title of a report with a
> parameter
> I have assigned to the report... Basically I want the title to read
> "Here's
> the Data for 01/01/2003 thru 12/31/2003" where both of the dates in the
> title are runtime parameters specified by the user.
> 2) Is there a way to do conditional formatting and or use constants when
> formatting fields... How about records? For instance, make the value in
> the field RED if it meets a certain criterion... or, make the record
> background blue if the record meets a certain criterion.
> Any direction you may provide or answers will be appreciated.
> Thanks
> REM7600
>|||Ravi... I could have sworn I did option 1... I do that in access all the
time. Oh well, I'll follow the KB article referenced and see what I left
out (I'm sure there's something).
Thanks for the help!
REM7600
> #1: ="Here's the data for " & Parameters!Report_Parameter_0.Value & " to "
&
> Parameters!Report_Parameter_1.Value [Check
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSMAIN/htm/rsc_ov_using_v1_6foz.asp
> for details]
> #2: =iif(Trim(Fields!type.Value) = "business", "Red", "Blue") [Check
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_3983.asp
> for details]|||I didn't try doing the Convert on the dates... Maybe that's what I did
wrong.
Thanks for the quick answers!
REM7600
> 1) Set title value to an expression:
> ="Here's the Data for " + CStr(Parameters!StartDate.Value) + " thru " +
> CStr(Parameters!EndDate.Value)
> 2) You may set color or background color properties to expressions
returning
> color name, for example:
> =iif(Fields!MyData.Value < 0, "Red", "Blue")
> --|||Note:
* if you use "+" to concatenate strings no implicit data type conversions
will happen.
* if you use "&" to concatenate strings, implicit conversions will be done
(invoking the default .ToString() implementation).
i.e. both expressions should work:
="Here's the Data for " + CStr(Parameters!StartDate.Value) + " thru " +
CStr(Parameters!EndDate.Value)
="Here's the Data for " & Parameters!StartDate.Value & " thru " &
Parameters!EndDate.Value
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"REM7600" <rem7600@.hotmail.com> wrote in message
news:OnsrcYSaEHA.1840@.TK2MSFTNGP11.phx.gbl...
> I didn't try doing the Convert on the dates... Maybe that's what I did
> wrong.
> Thanks for the quick answers!
> REM7600
>
> > 1) Set title value to an expression:
> > ="Here's the Data for " + CStr(Parameters!StartDate.Value) + " thru " +
> > CStr(Parameters!EndDate.Value)
> >
> > 2) You may set color or background color properties to expressions
> returning
> > color name, for example:
> > =iif(Fields!MyData.Value < 0, "Red", "Blue")
> >
> > --
>
Sunday, February 12, 2012
Beginner at Backups
I've spent the afternoon reading as much as possible on how to backup and
restore databases except there are a couple of small areas I can't quite get
my head round, so apologies for the beginners question but here goes:
I can set up a disk backup device and then every night run the 'Backup
Database' command to back up our database to a local folder, on top of this
I can run the 'backup log with truncate_only' every 20 minutes to provide
even better restore capability. I have tested these functions and
successfully restored the DB, but in this scenario the backup device is one
file and will simlpy keep growing every day. How can I overcome this? Should
I be creating a new file for every day and then deleting old files? Also,
should each transaction log be backed up to a new file - if not then how do
you know when to start a new backup file for the transaction logs without
the possibility of loosing data?
Cheers, Tom
Hi,
Use the INIT option along with BACKUP database command to reinitialize the
file every time.
Backup database dbname to device_name with INIT
See books online for more detail
Thanks
Hari
MCDBA
"Tom Clark" <tom_clark100@.hotmail.com> wrote in message
news:eTN0zsweEHA.140@.TK2MSFTNGP12.phx.gbl...
> I've spent the afternoon reading as much as possible on how to backup and
> restore databases except there are a couple of small areas I can't quite
get
> my head round, so apologies for the beginners question but here goes:
> I can set up a disk backup device and then every night run the 'Backup
> Database' command to back up our database to a local folder, on top of
this
> I can run the 'backup log with truncate_only' every 20 minutes to provide
> even better restore capability. I have tested these functions and
> successfully restored the DB, but in this scenario the backup device is
one
> file and will simlpy keep growing every day. How can I overcome this?
Should
> I be creating a new file for every day and then deleting old files? Also,
> should each transaction log be backed up to a new file - if not then how
do
> you know when to start a new backup file for the transaction logs without
> the possibility of loosing data?
> Cheers, Tom
>
|||1. With TRUNCATE Only will ONLY truncate the logs (Basically smokes Em)
2. Instead of overwriting the backup file each night, it is fairly typical
to move the OLD Backup to an archive location and save a few previous
backups. OR you can just delete it and replace it with the new backup file.
We typically name our Backup Files with a date time element
...\Backups\DatabaseName\Full\DatabaseNameXX_FULL _MMDDYYYYMMSS.Bak
Where XX is the File Number (We create 4 seperate files for each full backup
to improve backup performance as SQL Server can write to multiple files at
once using multiple threads)
3. We write each transaction backup to a seperate file following the same
naming convention.
...\Backups\DatabaseName\TRXN\DatabaseName_Trnxn_ MMDDYYYYMMSS.Bak
we backup our transaction logs every 15 minutes.
AFTER each successful FULL Backup, we move the Old Transaction Logs to an
Archive Location. in the archive folder we keep 48 hours of logs. We delete
files older than 48 hours.
this may not be perfect, but it works for us and is a good start for you.
Cheers,
Greg Jackson
PDX, Oregon
|||Cheers Greg, this was exactly the kind of answer I was after!
Thinking on though, if you had to do a restore and then apply all the logs
up to the last log backup, would you do this by script (or apply each
transaction log by hand) and if so how? The testing I've done has only been
with a small number of log files and so I have applied them one by one.
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:eb68X3weEHA.4068@.TK2MSFTNGP11.phx.gbl...
> 1. With TRUNCATE Only will ONLY truncate the logs (Basically smokes Em)
> 2. Instead of overwriting the backup file each night, it is fairly
typical
> to move the OLD Backup to an archive location and save a few previous
> backups. OR you can just delete it and replace it with the new backup
file.
> We typically name our Backup Files with a date time element
> ...\Backups\DatabaseName\Full\DatabaseNameXX_FULL_ MMDDYYYYMMSS.Bak
> Where XX is the File Number (We create 4 seperate files for each full
backup
> to improve backup performance as SQL Server can write to multiple files at
> once using multiple threads)
> 3. We write each transaction backup to a seperate file following the same
> naming convention.
> ...\Backups\DatabaseName\TRXN\DatabaseName_Trnxn_M MDDYYYYMMSS.Bak
> we backup our transaction logs every 15 minutes.
> AFTER each successful FULL Backup, we move the Old Transaction Logs to an
> Archive Location. in the archive folder we keep 48 hours of logs. We
delete
> files older than 48 hours.
>
> this may not be perfect, but it works for us and is a good start for you.
>
> Cheers,
>
> Greg Jackson
> PDX, Oregon
>
|||you can do it by hand or by script if you wanted.
I've actually done it by hand most often in past.
GAJ
restore databases except there are a couple of small areas I can't quite get
my head round, so apologies for the beginners question but here goes:
I can set up a disk backup device and then every night run the 'Backup
Database' command to back up our database to a local folder, on top of this
I can run the 'backup log with truncate_only' every 20 minutes to provide
even better restore capability. I have tested these functions and
successfully restored the DB, but in this scenario the backup device is one
file and will simlpy keep growing every day. How can I overcome this? Should
I be creating a new file for every day and then deleting old files? Also,
should each transaction log be backed up to a new file - if not then how do
you know when to start a new backup file for the transaction logs without
the possibility of loosing data?
Cheers, Tom
Hi,
Use the INIT option along with BACKUP database command to reinitialize the
file every time.
Backup database dbname to device_name with INIT
See books online for more detail
Thanks
Hari
MCDBA
"Tom Clark" <tom_clark100@.hotmail.com> wrote in message
news:eTN0zsweEHA.140@.TK2MSFTNGP12.phx.gbl...
> I've spent the afternoon reading as much as possible on how to backup and
> restore databases except there are a couple of small areas I can't quite
get
> my head round, so apologies for the beginners question but here goes:
> I can set up a disk backup device and then every night run the 'Backup
> Database' command to back up our database to a local folder, on top of
this
> I can run the 'backup log with truncate_only' every 20 minutes to provide
> even better restore capability. I have tested these functions and
> successfully restored the DB, but in this scenario the backup device is
one
> file and will simlpy keep growing every day. How can I overcome this?
Should
> I be creating a new file for every day and then deleting old files? Also,
> should each transaction log be backed up to a new file - if not then how
do
> you know when to start a new backup file for the transaction logs without
> the possibility of loosing data?
> Cheers, Tom
>
|||1. With TRUNCATE Only will ONLY truncate the logs (Basically smokes Em)
2. Instead of overwriting the backup file each night, it is fairly typical
to move the OLD Backup to an archive location and save a few previous
backups. OR you can just delete it and replace it with the new backup file.
We typically name our Backup Files with a date time element
...\Backups\DatabaseName\Full\DatabaseNameXX_FULL _MMDDYYYYMMSS.Bak
Where XX is the File Number (We create 4 seperate files for each full backup
to improve backup performance as SQL Server can write to multiple files at
once using multiple threads)
3. We write each transaction backup to a seperate file following the same
naming convention.
...\Backups\DatabaseName\TRXN\DatabaseName_Trnxn_ MMDDYYYYMMSS.Bak
we backup our transaction logs every 15 minutes.
AFTER each successful FULL Backup, we move the Old Transaction Logs to an
Archive Location. in the archive folder we keep 48 hours of logs. We delete
files older than 48 hours.
this may not be perfect, but it works for us and is a good start for you.
Cheers,
Greg Jackson
PDX, Oregon
|||Cheers Greg, this was exactly the kind of answer I was after!
Thinking on though, if you had to do a restore and then apply all the logs
up to the last log backup, would you do this by script (or apply each
transaction log by hand) and if so how? The testing I've done has only been
with a small number of log files and so I have applied them one by one.
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:eb68X3weEHA.4068@.TK2MSFTNGP11.phx.gbl...
> 1. With TRUNCATE Only will ONLY truncate the logs (Basically smokes Em)
> 2. Instead of overwriting the backup file each night, it is fairly
typical
> to move the OLD Backup to an archive location and save a few previous
> backups. OR you can just delete it and replace it with the new backup
file.
> We typically name our Backup Files with a date time element
> ...\Backups\DatabaseName\Full\DatabaseNameXX_FULL_ MMDDYYYYMMSS.Bak
> Where XX is the File Number (We create 4 seperate files for each full
backup
> to improve backup performance as SQL Server can write to multiple files at
> once using multiple threads)
> 3. We write each transaction backup to a seperate file following the same
> naming convention.
> ...\Backups\DatabaseName\TRXN\DatabaseName_Trnxn_M MDDYYYYMMSS.Bak
> we backup our transaction logs every 15 minutes.
> AFTER each successful FULL Backup, we move the Old Transaction Logs to an
> Archive Location. in the archive folder we keep 48 hours of logs. We
delete
> files older than 48 hours.
>
> this may not be perfect, but it works for us and is a good start for you.
>
> Cheers,
>
> Greg Jackson
> PDX, Oregon
>
|||you can do it by hand or by script if you wanted.
I've actually done it by hand most often in past.
GAJ
Beginner at Backups
I've spent the afternoon reading as much as possible on how to backup and
restore databases except there are a couple of small areas I can't quite get
my head round, so apologies for the beginners question but here goes:
I can set up a disk backup device and then every night run the 'Backup
Database' command to back up our database to a local folder, on top of this
I can run the 'backup log with truncate_only' every 20 minutes to provide
even better restore capability. I have tested these functions and
successfully restored the DB, but in this scenario the backup device is one
file and will simlpy keep growing every day. How can I overcome this? Should
I be creating a new file for every day and then deleting old files? Also,
should each transaction log be backed up to a new file - if not then how do
you know when to start a new backup file for the transaction logs without
the possibility of loosing data?
Cheers, TomHi,
Use the INIT option along with BACKUP database command to reinitialize the
file every time.
Backup database dbname to device_name with INIT
See books online for more detail
Thanks
Hari
MCDBA
"Tom Clark" <tom_clark100@.hotmail.com> wrote in message
news:eTN0zsweEHA.140@.TK2MSFTNGP12.phx.gbl...
> I've spent the afternoon reading as much as possible on how to backup and
> restore databases except there are a couple of small areas I can't quite
get
> my head round, so apologies for the beginners question but here goes:
> I can set up a disk backup device and then every night run the 'Backup
> Database' command to back up our database to a local folder, on top of
this
> I can run the 'backup log with truncate_only' every 20 minutes to provide
> even better restore capability. I have tested these functions and
> successfully restored the DB, but in this scenario the backup device is
one
> file and will simlpy keep growing every day. How can I overcome this?
Should
> I be creating a new file for every day and then deleting old files? Also,
> should each transaction log be backed up to a new file - if not then how
do
> you know when to start a new backup file for the transaction logs without
> the possibility of loosing data?
> Cheers, Tom
>|||1. With TRUNCATE Only will ONLY truncate the logs (Basically smokes Em)
2. Instead of overwriting the backup file each night, it is fairly typical
to move the OLD Backup to an archive location and save a few previous
backups. OR you can just delete it and replace it with the new backup file.
We typically name our Backup Files with a date time element
... \Backups\DatabaseName\Full\DatabaseNameX
X_FULL_MMDDYYYYMMSS.Bak
Where XX is the File Number (We create 4 seperate files for each full backup
to improve backup performance as SQL Server can write to multiple files at
once using multiple threads)
3. We write each transaction backup to a seperate file following the same
naming convention.
... \Backups\DatabaseName\TRXN\DatabaseName_
Trnxn_MMDDYYYYMMSS.Bak
we backup our transaction logs every 15 minutes.
AFTER each successful FULL Backup, we move the Old Transaction Logs to an
Archive Location. in the archive folder we keep 48 hours of logs. We delete
files older than 48 hours.
this may not be perfect, but it works for us and is a good start for you.
Cheers,
Greg Jackson
PDX, Oregon|||Cheers Greg, this was exactly the kind of answer I was after!
Thinking on though, if you had to do a restore and then apply all the logs
up to the last log backup, would you do this by script (or apply each
transaction log by hand) and if so how? The testing I've done has only been
with a small number of log files and so I have applied them one by one.
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:eb68X3weEHA.4068@.TK2MSFTNGP11.phx.gbl...
> 1. With TRUNCATE Only will ONLY truncate the logs (Basically smokes Em)
> 2. Instead of overwriting the backup file each night, it is fairly
typical
> to move the OLD Backup to an archive location and save a few previous
> backups. OR you can just delete it and replace it with the new backup
file.
> We typically name our Backup Files with a date time element
> ... \Backups\DatabaseName\Full\DatabaseNameX
X_FULL_MMDDYYYYMMSS.Bak
> Where XX is the File Number (We create 4 seperate files for each full
backup
> to improve backup performance as SQL Server can write to multiple files at
> once using multiple threads)
> 3. We write each transaction backup to a seperate file following the same
> naming convention.
> ... \Backups\DatabaseName\TRXN\DatabaseName_
Trnxn_MMDDYYYYMMSS.Bak
> we backup our transaction logs every 15 minutes.
> AFTER each successful FULL Backup, we move the Old Transaction Logs to an
> Archive Location. in the archive folder we keep 48 hours of logs. We
delete
> files older than 48 hours.
>
> this may not be perfect, but it works for us and is a good start for you.
>
> Cheers,
>
> Greg Jackson
> PDX, Oregon
>|||you can do it by hand or by script if you wanted.
I've actually done it by hand most often in past.
GAJ
restore databases except there are a couple of small areas I can't quite get
my head round, so apologies for the beginners question but here goes:
I can set up a disk backup device and then every night run the 'Backup
Database' command to back up our database to a local folder, on top of this
I can run the 'backup log with truncate_only' every 20 minutes to provide
even better restore capability. I have tested these functions and
successfully restored the DB, but in this scenario the backup device is one
file and will simlpy keep growing every day. How can I overcome this? Should
I be creating a new file for every day and then deleting old files? Also,
should each transaction log be backed up to a new file - if not then how do
you know when to start a new backup file for the transaction logs without
the possibility of loosing data?
Cheers, TomHi,
Use the INIT option along with BACKUP database command to reinitialize the
file every time.
Backup database dbname to device_name with INIT
See books online for more detail
Thanks
Hari
MCDBA
"Tom Clark" <tom_clark100@.hotmail.com> wrote in message
news:eTN0zsweEHA.140@.TK2MSFTNGP12.phx.gbl...
> I've spent the afternoon reading as much as possible on how to backup and
> restore databases except there are a couple of small areas I can't quite
get
> my head round, so apologies for the beginners question but here goes:
> I can set up a disk backup device and then every night run the 'Backup
> Database' command to back up our database to a local folder, on top of
this
> I can run the 'backup log with truncate_only' every 20 minutes to provide
> even better restore capability. I have tested these functions and
> successfully restored the DB, but in this scenario the backup device is
one
> file and will simlpy keep growing every day. How can I overcome this?
Should
> I be creating a new file for every day and then deleting old files? Also,
> should each transaction log be backed up to a new file - if not then how
do
> you know when to start a new backup file for the transaction logs without
> the possibility of loosing data?
> Cheers, Tom
>|||1. With TRUNCATE Only will ONLY truncate the logs (Basically smokes Em)
2. Instead of overwriting the backup file each night, it is fairly typical
to move the OLD Backup to an archive location and save a few previous
backups. OR you can just delete it and replace it with the new backup file.
We typically name our Backup Files with a date time element
... \Backups\DatabaseName\Full\DatabaseNameX
X_FULL_MMDDYYYYMMSS.Bak
Where XX is the File Number (We create 4 seperate files for each full backup
to improve backup performance as SQL Server can write to multiple files at
once using multiple threads)
3. We write each transaction backup to a seperate file following the same
naming convention.
... \Backups\DatabaseName\TRXN\DatabaseName_
Trnxn_MMDDYYYYMMSS.Bak
we backup our transaction logs every 15 minutes.
AFTER each successful FULL Backup, we move the Old Transaction Logs to an
Archive Location. in the archive folder we keep 48 hours of logs. We delete
files older than 48 hours.
this may not be perfect, but it works for us and is a good start for you.
Cheers,
Greg Jackson
PDX, Oregon|||Cheers Greg, this was exactly the kind of answer I was after!
Thinking on though, if you had to do a restore and then apply all the logs
up to the last log backup, would you do this by script (or apply each
transaction log by hand) and if so how? The testing I've done has only been
with a small number of log files and so I have applied them one by one.
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:eb68X3weEHA.4068@.TK2MSFTNGP11.phx.gbl...
> 1. With TRUNCATE Only will ONLY truncate the logs (Basically smokes Em)
> 2. Instead of overwriting the backup file each night, it is fairly
typical
> to move the OLD Backup to an archive location and save a few previous
> backups. OR you can just delete it and replace it with the new backup
file.
> We typically name our Backup Files with a date time element
> ... \Backups\DatabaseName\Full\DatabaseNameX
X_FULL_MMDDYYYYMMSS.Bak
> Where XX is the File Number (We create 4 seperate files for each full
backup
> to improve backup performance as SQL Server can write to multiple files at
> once using multiple threads)
> 3. We write each transaction backup to a seperate file following the same
> naming convention.
> ... \Backups\DatabaseName\TRXN\DatabaseName_
Trnxn_MMDDYYYYMMSS.Bak
> we backup our transaction logs every 15 minutes.
> AFTER each successful FULL Backup, we move the Old Transaction Logs to an
> Archive Location. in the archive folder we keep 48 hours of logs. We
delete
> files older than 48 hours.
>
> this may not be perfect, but it works for us and is a good start for you.
>
> Cheers,
>
> Greg Jackson
> PDX, Oregon
>|||you can do it by hand or by script if you wanted.
I've actually done it by hand most often in past.
GAJ
Beginner at Backups
I've spent the afternoon reading as much as possible on how to backup and
restore databases except there are a couple of small areas I can't quite get
my head round, so apologies for the beginners question but here goes:
I can set up a disk backup device and then every night run the 'Backup
Database' command to back up our database to a local folder, on top of this
I can run the 'backup log with truncate_only' every 20 minutes to provide
even better restore capability. I have tested these functions and
successfully restored the DB, but in this scenario the backup device is one
file and will simlpy keep growing every day. How can I overcome this? Should
I be creating a new file for every day and then deleting old files? Also,
should each transaction log be backed up to a new file - if not then how do
you know when to start a new backup file for the transaction logs without
the possibility of loosing data?
Cheers, TomHi,
Use the INIT option along with BACKUP database command to reinitialize the
file every time.
Backup database dbname to device_name with INIT
See books online for more detail
Thanks
Hari
MCDBA
"Tom Clark" <tom_clark100@.hotmail.com> wrote in message
news:eTN0zsweEHA.140@.TK2MSFTNGP12.phx.gbl...
> I've spent the afternoon reading as much as possible on how to backup and
> restore databases except there are a couple of small areas I can't quite
get
> my head round, so apologies for the beginners question but here goes:
> I can set up a disk backup device and then every night run the 'Backup
> Database' command to back up our database to a local folder, on top of
this
> I can run the 'backup log with truncate_only' every 20 minutes to provide
> even better restore capability. I have tested these functions and
> successfully restored the DB, but in this scenario the backup device is
one
> file and will simlpy keep growing every day. How can I overcome this?
Should
> I be creating a new file for every day and then deleting old files? Also,
> should each transaction log be backed up to a new file - if not then how
do
> you know when to start a new backup file for the transaction logs without
> the possibility of loosing data?
> Cheers, Tom
>|||1. With TRUNCATE Only will ONLY truncate the logs (Basically smokes Em)
2. Instead of overwriting the backup file each night, it is fairly typical
to move the OLD Backup to an archive location and save a few previous
backups. OR you can just delete it and replace it with the new backup file.
We typically name our Backup Files with a date time element
...\Backups\DatabaseName\Full\DatabaseNameXX_FULL_MMDDYYYYMMSS.Bak
Where XX is the File Number (We create 4 seperate files for each full backup
to improve backup performance as SQL Server can write to multiple files at
once using multiple threads)
3. We write each transaction backup to a seperate file following the same
naming convention.
...\Backups\DatabaseName\TRXN\DatabaseName_Trnxn_MMDDYYYYMMSS.Bak
we backup our transaction logs every 15 minutes.
AFTER each successful FULL Backup, we move the Old Transaction Logs to an
Archive Location. in the archive folder we keep 48 hours of logs. We delete
files older than 48 hours.
this may not be perfect, but it works for us and is a good start for you.
Cheers,
Greg Jackson
PDX, Oregon|||Cheers Greg, this was exactly the kind of answer I was after!
Thinking on though, if you had to do a restore and then apply all the logs
up to the last log backup, would you do this by script (or apply each
transaction log by hand) and if so how? The testing I've done has only been
with a small number of log files and so I have applied them one by one.
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:eb68X3weEHA.4068@.TK2MSFTNGP11.phx.gbl...
> 1. With TRUNCATE Only will ONLY truncate the logs (Basically smokes Em)
> 2. Instead of overwriting the backup file each night, it is fairly
typical
> to move the OLD Backup to an archive location and save a few previous
> backups. OR you can just delete it and replace it with the new backup
file.
> We typically name our Backup Files with a date time element
> ...\Backups\DatabaseName\Full\DatabaseNameXX_FULL_MMDDYYYYMMSS.Bak
> Where XX is the File Number (We create 4 seperate files for each full
backup
> to improve backup performance as SQL Server can write to multiple files at
> once using multiple threads)
> 3. We write each transaction backup to a seperate file following the same
> naming convention.
> ...\Backups\DatabaseName\TRXN\DatabaseName_Trnxn_MMDDYYYYMMSS.Bak
> we backup our transaction logs every 15 minutes.
> AFTER each successful FULL Backup, we move the Old Transaction Logs to an
> Archive Location. in the archive folder we keep 48 hours of logs. We
delete
> files older than 48 hours.
>
> this may not be perfect, but it works for us and is a good start for you.
>
> Cheers,
>
> Greg Jackson
> PDX, Oregon
>|||you can do it by hand or by script if you wanted.
I've actually done it by hand most often in past.
GAJ
restore databases except there are a couple of small areas I can't quite get
my head round, so apologies for the beginners question but here goes:
I can set up a disk backup device and then every night run the 'Backup
Database' command to back up our database to a local folder, on top of this
I can run the 'backup log with truncate_only' every 20 minutes to provide
even better restore capability. I have tested these functions and
successfully restored the DB, but in this scenario the backup device is one
file and will simlpy keep growing every day. How can I overcome this? Should
I be creating a new file for every day and then deleting old files? Also,
should each transaction log be backed up to a new file - if not then how do
you know when to start a new backup file for the transaction logs without
the possibility of loosing data?
Cheers, TomHi,
Use the INIT option along with BACKUP database command to reinitialize the
file every time.
Backup database dbname to device_name with INIT
See books online for more detail
Thanks
Hari
MCDBA
"Tom Clark" <tom_clark100@.hotmail.com> wrote in message
news:eTN0zsweEHA.140@.TK2MSFTNGP12.phx.gbl...
> I've spent the afternoon reading as much as possible on how to backup and
> restore databases except there are a couple of small areas I can't quite
get
> my head round, so apologies for the beginners question but here goes:
> I can set up a disk backup device and then every night run the 'Backup
> Database' command to back up our database to a local folder, on top of
this
> I can run the 'backup log with truncate_only' every 20 minutes to provide
> even better restore capability. I have tested these functions and
> successfully restored the DB, but in this scenario the backup device is
one
> file and will simlpy keep growing every day. How can I overcome this?
Should
> I be creating a new file for every day and then deleting old files? Also,
> should each transaction log be backed up to a new file - if not then how
do
> you know when to start a new backup file for the transaction logs without
> the possibility of loosing data?
> Cheers, Tom
>|||1. With TRUNCATE Only will ONLY truncate the logs (Basically smokes Em)
2. Instead of overwriting the backup file each night, it is fairly typical
to move the OLD Backup to an archive location and save a few previous
backups. OR you can just delete it and replace it with the new backup file.
We typically name our Backup Files with a date time element
...\Backups\DatabaseName\Full\DatabaseNameXX_FULL_MMDDYYYYMMSS.Bak
Where XX is the File Number (We create 4 seperate files for each full backup
to improve backup performance as SQL Server can write to multiple files at
once using multiple threads)
3. We write each transaction backup to a seperate file following the same
naming convention.
...\Backups\DatabaseName\TRXN\DatabaseName_Trnxn_MMDDYYYYMMSS.Bak
we backup our transaction logs every 15 minutes.
AFTER each successful FULL Backup, we move the Old Transaction Logs to an
Archive Location. in the archive folder we keep 48 hours of logs. We delete
files older than 48 hours.
this may not be perfect, but it works for us and is a good start for you.
Cheers,
Greg Jackson
PDX, Oregon|||Cheers Greg, this was exactly the kind of answer I was after!
Thinking on though, if you had to do a restore and then apply all the logs
up to the last log backup, would you do this by script (or apply each
transaction log by hand) and if so how? The testing I've done has only been
with a small number of log files and so I have applied them one by one.
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:eb68X3weEHA.4068@.TK2MSFTNGP11.phx.gbl...
> 1. With TRUNCATE Only will ONLY truncate the logs (Basically smokes Em)
> 2. Instead of overwriting the backup file each night, it is fairly
typical
> to move the OLD Backup to an archive location and save a few previous
> backups. OR you can just delete it and replace it with the new backup
file.
> We typically name our Backup Files with a date time element
> ...\Backups\DatabaseName\Full\DatabaseNameXX_FULL_MMDDYYYYMMSS.Bak
> Where XX is the File Number (We create 4 seperate files for each full
backup
> to improve backup performance as SQL Server can write to multiple files at
> once using multiple threads)
> 3. We write each transaction backup to a seperate file following the same
> naming convention.
> ...\Backups\DatabaseName\TRXN\DatabaseName_Trnxn_MMDDYYYYMMSS.Bak
> we backup our transaction logs every 15 minutes.
> AFTER each successful FULL Backup, we move the Old Transaction Logs to an
> Archive Location. in the archive folder we keep 48 hours of logs. We
delete
> files older than 48 hours.
>
> this may not be perfect, but it works for us and is a good start for you.
>
> Cheers,
>
> Greg Jackson
> PDX, Oregon
>|||you can do it by hand or by script if you wanted.
I've actually done it by hand most often in past.
GAJ
Subscribe to:
Comments (Atom)