Archiving logging db

I'm fairly new to SQL. I've inherited a logging db that records the actions taken on a web service. One of the first questions I'm being asked to solve is to explain how I should concatenate the db and save older entries to a archived db.

I'm sure this is a pretty simple action - just identify the date I want to archive from and develop a query that copies all entries from before that time period to a separate db. I'm looking for some guidlines for this kind of action, but there's so much info on SQL out there, I'm having trouble parsing out the noise and finding the answers I'm looking for. Can anyone point me in the right direction, or give me advice?

Thanks

Levi

One way to do this is to create an SSIS package:

http://msdn2.microsoft.com/en-us/library/ms169917.aspx

|||

Create a archiving table (Arch_Table) in the same db or different db with same schema.

insert into dbname..Arch_Table

select * from Table where dtColumn <= dateadd(mm, -3, getdate()) -- two archive 3months and older data

while 1 = 1

begin

set rowcount 100 delete 100 rows at a time... if you are using sql 2005 you can use top clause check bol for details

delete from Table where dtColumn <= (select max(dtColumn ) from dbname..Arch_Table )

If @.@.rowcount = 0

Break

end

sql

archive logging the log file

I am new to SQL Server, but I cannot seem to find any way that SQL Server
will automatically move a log file (when it isn't needed anymore) off to
archive. Is the only way I can do this by setting up a batch process and
doing it myself?
Help?I believe you are referring to transaction logs. The proper way to manage
transaction logs for databases in the FULL or BULK_LOGGED recovery model is
to backup the transaction log periodically. This will remove committed data
from the log (keeping the size reasonable) and allow you to use the log
backups in conjunction with database backups as part of your recovery plan.
A common practice is to backup to disk and then archive those backups to
tape. You can setup database maintenance plans to perform both database and
transaction log backups or create and schedule your own backup scripts. See
the Books Online for more information.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"learningdba" <learningdba@.discussions.microsoft.com> wrote in message
news:6BAE1103-01FC-45A4-B57E-B8FCD9AA86E3@.microsoft.com...
>I am new to SQL Server, but I cannot seem to find any way that SQL Server
> will automatically move a log file (when it isn't needed anymore) off to
> archive. Is the only way I can do this by setting up a batch process and
> doing it myself?
> Help?|||Transaction logs are circular files, the same space is reused over and over
again, so there is never a need to "remove the files"... As Dan says, the
way to remove transactions and make space available for re-use is to back up
the log , or use SQL Simple recovery mode.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"learningdba" <learningdba@.discussions.microsoft.com> wrote in message
news:6BAE1103-01FC-45A4-B57E-B8FCD9AA86E3@.microsoft.com...
> I am new to SQL Server, but I cannot seem to find any way that SQL Server
> will automatically move a log file (when it isn't needed anymore) off to
> archive. Is the only way I can do this by setting up a batch process and
> doing it myself?
> Help?|||"learningdba" <learningdba@.discussions.microsoft.com> wrote in message
news:6BAE1103-01FC-45A4-B57E-B8FCD9AA86E3@.microsoft.com...
> I am new to SQL Server, but I cannot seem to find any way that SQL Server
> will automatically move a log file (when it isn't needed anymore) off to
> archive. Is the only way I can do this by setting up a batch process and
> doing it myself?
>
Just to add to what others have said, it sounds like you're coming from an
Oracle background?
In that case, basically it comes down to, SQL Server reuses the same file
and can do on-line backups of it which will "clear it out".
> Help?sql

archive logging the log file

I am new to SQL Server, but I cannot seem to find any way that SQL Server
will automatically move a log file (when it isn't needed anymore) off to
archive. Is the only way I can do this by setting up a batch process and
doing it myself?
Help?
I believe you are referring to transaction logs. The proper way to manage
transaction logs for databases in the FULL or BULK_LOGGED recovery model is
to backup the transaction log periodically. This will remove committed data
from the log (keeping the size reasonable) and allow you to use the log
backups in conjunction with database backups as part of your recovery plan.
A common practice is to backup to disk and then archive those backups to
tape. You can setup database maintenance plans to perform both database and
transaction log backups or create and schedule your own backup scripts. See
the Books Online for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"learningdba" <learningdba@.discussions.microsoft.com> wrote in message
news:6BAE1103-01FC-45A4-B57E-B8FCD9AA86E3@.microsoft.com...
>I am new to SQL Server, but I cannot seem to find any way that SQL Server
> will automatically move a log file (when it isn't needed anymore) off to
> archive. Is the only way I can do this by setting up a batch process and
> doing it myself?
> Help?
|||Transaction logs are circular files, the same space is reused over and over
again, so there is never a need to "remove the files"... As Dan says, the
way to remove transactions and make space available for re-use is to back up
the log , or use SQL Simple recovery mode.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"learningdba" <learningdba@.discussions.microsoft.com> wrote in message
news:6BAE1103-01FC-45A4-B57E-B8FCD9AA86E3@.microsoft.com...
> I am new to SQL Server, but I cannot seem to find any way that SQL Server
> will automatically move a log file (when it isn't needed anymore) off to
> archive. Is the only way I can do this by setting up a batch process and
> doing it myself?
> Help?
|||"learningdba" <learningdba@.discussions.microsoft.com> wrote in message
news:6BAE1103-01FC-45A4-B57E-B8FCD9AA86E3@.microsoft.com...
> I am new to SQL Server, but I cannot seem to find any way that SQL Server
> will automatically move a log file (when it isn't needed anymore) off to
> archive. Is the only way I can do this by setting up a batch process and
> doing it myself?
>
Just to add to what others have said, it sounds like you're coming from an
Oracle background?
In that case, basically it comes down to, SQL Server reuses the same file
and can do on-line backups of it which will "clear it out".

> Help?

archive logging the log file

I am new to SQL Server, but I cannot seem to find any way that SQL Server
will automatically move a log file (when it isn't needed anymore) off to
archive. Is the only way I can do this by setting up a batch process and
doing it myself?
Help?I believe you are referring to transaction logs. The proper way to manage
transaction logs for databases in the FULL or BULK_LOGGED recovery model is
to backup the transaction log periodically. This will remove committed data
from the log (keeping the size reasonable) and allow you to use the log
backups in conjunction with database backups as part of your recovery plan.
A common practice is to backup to disk and then archive those backups to
tape. You can setup database maintenance plans to perform both database and
transaction log backups or create and schedule your own backup scripts. See
the Books Online for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"learningdba" <learningdba@.discussions.microsoft.com> wrote in message
news:6BAE1103-01FC-45A4-B57E-B8FCD9AA86E3@.microsoft.com...
>I am new to SQL Server, but I cannot seem to find any way that SQL Server
> will automatically move a log file (when it isn't needed anymore) off to
> archive. Is the only way I can do this by setting up a batch process and
> doing it myself?
> Help?|||Transaction logs are circular files, the same space is reused over and over
again, so there is never a need to "remove the files"... As Dan says, the
way to remove transactions and make space available for re-use is to back up
the log , or use SQL Simple recovery mode.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"learningdba" <learningdba@.discussions.microsoft.com> wrote in message
news:6BAE1103-01FC-45A4-B57E-B8FCD9AA86E3@.microsoft.com...
> I am new to SQL Server, but I cannot seem to find any way that SQL Server
> will automatically move a log file (when it isn't needed anymore) off to
> archive. Is the only way I can do this by setting up a batch process and
> doing it myself?
> Help?|||"learningdba" <learningdba@.discussions.microsoft.com> wrote in message
news:6BAE1103-01FC-45A4-B57E-B8FCD9AA86E3@.microsoft.com...
> I am new to SQL Server, but I cannot seem to find any way that SQL Server
> will automatically move a log file (when it isn't needed anymore) off to
> archive. Is the only way I can do this by setting up a batch process and
> doing it myself?
>
Just to add to what others have said, it sounds like you're coming from an
Oracle background?
In that case, basically it comes down to, SQL Server reuses the same file
and can do on-line backups of it which will "clear it out".

> Help?