archiving data

Hi all hope this is in the right forum.
Ive got a SQL database which accumulates about 4 million records each year.
This amount causes my PC to slow down when searching, and my hard drive is
getting fuller.
To overcome this problem I have to delete half the records each year (2
million) which takes quite some time.
My question is - is it possible to archive some of the SQL database each
year onto another hard drive or DVD. The archived data should them be removed
from the original SQL data base.
I Would also need to be able to view, sort and search this archived data on
another machine in excell or something.
Hope someone can help.
Thanks
Jon
"jsw" <jsw@.discussions.microsoft.com> wrote in message
news:E55D02B1-FD8D-468A-A716-9CF9A1717363@.microsoft.com...
> Hi all hope this is in the right forum.
> Ive got a SQL database which accumulates about 4 million records each
> year.
> This amount causes my PC to slow down when searching, and my hard drive is
> getting fuller.
>
4 million a YEAR slows you down? Do you have indexes?
Anyway...

> To overcome this problem I have to delete half the records each year (2
> million) which takes quite some time.
>
Yeah, deletions can take time.

> My question is - is it possible to archive some of the SQL database each
> year onto another hard drive or DVD. The archived data should them be
> removed
> from the original SQL data base.
I'd probably use DTS (assuming SQL 2000, SSIS for 2005) to copy out the data
in a format you can use.
Then delete it.
That should solve your problems.
> I Would also need to be able to view, sort and search this archived data
> on
> another machine in excell or something.
> Hope someone can help.
> Thanks
> Jon
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
|||Hi,
Thanks for the reply.
Yes we have clustered indexes on the primary keys.
Sorry but what is DTS.
Can the process be automated on a particular date?
Thanks
"Greg D. Moore (Strider)" wrote:

> "jsw" <jsw@.discussions.microsoft.com> wrote in message
> news:E55D02B1-FD8D-468A-A716-9CF9A1717363@.microsoft.com...
> 4 million a YEAR slows you down? Do you have indexes?
> Anyway...
>
> Yeah, deletions can take time.
>
> I'd probably use DTS (assuming SQL 2000, SSIS for 2005) to copy out the data
> in a format you can use.
> Then delete it.
> That should solve your problems.
>
> --
> Greg Moore
> SQL Server DBA Consulting
> sql (at) greenms.com http://www.greenms.com
>
>
|||"jsw" <jsw@.discussions.microsoft.com> wrote in message
news:AF4D2F53-9995-4745-973D-5E3DC04B1EC3@.microsoft.com...
> Hi,
> Thanks for the reply.
> Yes we have clustered indexes on the primary keys.
> Sorry but what is DTS.
Data Transformation Services.
Look for it under Enterprise Manager.

> Can the process be automated on a particular date?
Yes.
Create a DTS package, and then you can schedule it.

> Thanks
>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com