I am a developer, not a DBA. I have a simple need, and a simple
solution, but I expect my solution is far from as efficient or elegant
as it could be. I would like to archive a group of records from a log
table that I have, and then delete said records from the table. I
would like to write them to a disk file in such a way that they could
be recovered if necessary. I have a simple query to select the
records I'd like to archive that looks something like this:
select * from mylogtable
where ((level = 'Debug') and (timestamp < '9/7/2007'))
or (timestamp < '9/1/2007')
Yes, there are indices on "timestamp" and "level". The dates in the
query should change as I run this job, and I'd probably run it
something like once a week.
I can do this simply by running the query in "SQL Server Management
Studio" and sending the results to a file. Is there a better way?
Ideally I'd like to schedule this task but that's secondary to running
it efficiently.
Thanks!
Brad.You could think using Partitioning in large environments (this is a new
feature of SQL Server 2005 and it only can be used in Enterprise Edition).
http://msdn2.microsoft.com/en-us/library/ms345146.aspx
But for your environment, Files and Filesgroups would be enough I guess?
If you want to learn more about Files and Files Groups, you can visit the
following page:
http://msdn2.microsoft.com/en-us/library/ms189126.aspx
--
Ekrem Önsoy
"Bradley Plett" <plettb@.newsgroup.nospam> wrote in message
news:d69be3l84tlaphq7dljbvfh314u4j8mc06@.4ax.com...
>I am a developer, not a DBA. I have a simple need, and a simple
> solution, but I expect my solution is far from as efficient or elegant
> as it could be. I would like to archive a group of records from a log
> table that I have, and then delete said records from the table. I
> would like to write them to a disk file in such a way that they could
> be recovered if necessary. I have a simple query to select the
> records I'd like to archive that looks something like this:
> select * from mylogtable
> where ((level = 'Debug') and (timestamp < '9/7/2007'))
> or (timestamp < '9/1/2007')
> Yes, there are indices on "timestamp" and "level". The dates in the
> query should change as I run this job, and I'd probably run it
> something like once a week.
> I can do this simply by running the query in "SQL Server Management
> Studio" and sending the results to a file. Is there a better way?
> Ideally I'd like to schedule this task but that's secondary to running
> it efficiently.
> Thanks!
> Brad.|||I'm still no expert, but I think the "bcp" utility is what I was
looking for. :-)
Brad.
On Mon, 10 Sep 2007 14:19:29 -0600, Bradley Plett
<plettb@.newsgroup.nospam> wrote:
>I am a developer, not a DBA. I have a simple need, and a simple
>solution, but I expect my solution is far from as efficient or elegant
>as it could be. I would like to archive a group of records from a log
>table that I have, and then delete said records from the table. I
>would like to write them to a disk file in such a way that they could
>be recovered if necessary. I have a simple query to select the
>records I'd like to archive that looks something like this:
>select * from mylogtable
>where ((level = 'Debug') and (timestamp < '9/7/2007'))
>or (timestamp < '9/1/2007')
>Yes, there are indices on "timestamp" and "level". The dates in the
>query should change as I run this job, and I'd probably run it
>something like once a week.
>I can do this simply by running the query in "SQL Server Management
>Studio" and sending the results to a file. Is there a better way?
>Ideally I'd like to schedule this task but that's secondary to running
>it efficiently.
>Thanks!
>Brad.|||That or use DTS.
"Bradley Plett" <plettb@.newsgroup.nospam> wrote in message
news:8khbe35v1m0rttv6mbre1vuob46fidcblg@.4ax.com...
> I'm still no expert, but I think the "bcp" utility is what I was
> looking for. :-)
> Brad.
> On Mon, 10 Sep 2007 14:19:29 -0600, Bradley Plett
> <plettb@.newsgroup.nospam> wrote:
>>I am a developer, not a DBA. I have a simple need, and a simple
>>solution, but I expect my solution is far from as efficient or elegant
>>as it could be. I would like to archive a group of records from a log
>>table that I have, and then delete said records from the table. I
>>would like to write them to a disk file in such a way that they could
>>be recovered if necessary. I have a simple query to select the
>>records I'd like to archive that looks something like this:
>>select * from mylogtable
>>where ((level = 'Debug') and (timestamp < '9/7/2007'))
>>or (timestamp < '9/1/2007')
>>Yes, there are indices on "timestamp" and "level". The dates in the
>>query should change as I run this job, and I'd probably run it
>>something like once a week.
>>I can do this simply by running the query in "SQL Server Management
>>Studio" and sending the results to a file. Is there a better way?
>>Ideally I'd like to schedule this task but that's secondary to running
>>it efficiently.
>>Thanks!
>>Brad.|||Hi Brad,
Yes, for your requirement, I believe that bcp utility is the most simple
and efficient way. If you have a large amount of data to be transferred, I
recommend that you use this way.
For detail information, you may refer to the segment "F. Copying data from
a query to a data file" from this article:
bcp Utility
http://msdn2.microsoft.com/en-us/library/ms162802.aspx
You may also want the following reference:
Importing and Exporting Bulk Data by Using the bcp Utility
http://technet.microsoft.com/en-us/library/aa337544.aspx
Additionally I would like to introduce a new feature of SQL Server 2005 for
data transfer. You can also use a SSIS package to do this. Please see the
following steps:
1. Open SQL Server Intelligence Development Studio, create a SSIS project;
2. Switch to the Data Flow pane, drag an OLEDB or DataReader source, double
click the source and create an OLEDB (for OLEDB Source) or ADO.NET (for
DataReader source) connection for it;
3. Input T-SQL statement to the SqlCommand field in Component Properties
and click OK.
4. Drag a Flat File Destination to the Data Flow pane, and drag the output
green line of the source to the Flat File Destination;
5. Double click the Flat File Destination, create a Flat File connection
for specifying an empty file and define the text format;
6. Save the package and execute it.
You may want to get familiar with SSIS from the following references:
An Introduction to SQL Server 2005 Integration Services
http://www.microsoft.com/technet/prodtechnol/sql/2005/intro2is.mspx
Managing and Deploying SQL Server Integration Services
http://www.microsoft.com/technet/prodtechnol/sql/2005/mgngssis.mspx
Hope this helps. If you have any other questions or concerns, please feel
free to let us know. Have a great day!
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||I misunderstood your question, sorry.
--
Ekrem Önsoy
"Bradley Plett" <plettb@.newsgroup.nospam> wrote in message
news:8khbe35v1m0rttv6mbre1vuob46fidcblg@.4ax.com...
> I'm still no expert, but I think the "bcp" utility is what I was
> looking for. :-)
> Brad.
> On Mon, 10 Sep 2007 14:19:29 -0600, Bradley Plett
> <plettb@.newsgroup.nospam> wrote:
>>I am a developer, not a DBA. I have a simple need, and a simple
>>solution, but I expect my solution is far from as efficient or elegant
>>as it could be. I would like to archive a group of records from a log
>>table that I have, and then delete said records from the table. I
>>would like to write them to a disk file in such a way that they could
>>be recovered if necessary. I have a simple query to select the
>>records I'd like to archive that looks something like this:
>>select * from mylogtable
>>where ((level = 'Debug') and (timestamp < '9/7/2007'))
>>or (timestamp < '9/1/2007')
>>Yes, there are indices on "timestamp" and "level". The dates in the
>>query should change as I run this job, and I'd probably run it
>>something like once a week.
>>I can do this simply by running the query in "SQL Server Management
>>Studio" and sending the results to a file. Is there a better way?
>>Ideally I'd like to schedule this task but that's secondary to running
>>it efficiently.
>>Thanks!
>>Brad.
