Archive large dataset

[sladd]

I need to archive data from a rather large dataset to reclaim disk space. I want to creaste script with a data parameter, archive the data older than the date parameter to a seperate database, delete the data from the source then backup the arvhived database to tape. Below is my script. I would like to minimize the load placed on the server during execution - is this the best way to do this. Will these transactions be logged?? Is there a way to tell SQL to not log this process? below is my code:

Declare @PurgeDate as datetime

Set @PurgeDate = '01-01-2007'


--Archive Agent
Insert into dbo.Agent
Select Top 10 *
from hds.dbo.agent
where HDS_Sampledate < @PurgeDate


--Archive Agent_Month
Insert into dbo.Agent_Month
Select Top 10 *
from hds.dbo.agent_month
where HDS_Sampledate < @PurgeDate


--Archive DistributionFlat
Insert into dbo.DistributionFlat
Select Top 10 *
from hds.dbo.DistributionFlat
where HDS_Sampledate < @PurgeDate


--Archive HDSTableInfo
Insert into dbo.HDSTableInfo
Select Top 10 *
from hds.dbo.HDSTableInfo
where HDS_Sampledate < @PurgeDate


--Archive Policy
Insert into dbo.Policy
Select Top 10 *
from hds.dbo.Policy
where HDS_Sampledate < @PurgeDate


--Archive Policy_Month
Insert into dbo.Policy_Month
Select Top 10 *
from hds.dbo.Policy_Month
where HDS_Sampledate < @PurgeDate

 

[mrdenny]

All Transactions in SQL Server are logged. There is no way to do a non-logged transaction.

You've got a couple of options to keep the logging to a minimum.

1. Change the database recovery to SIMPLE before running this command, then change it back to FULL after words. (Be sure to do a full backup as soon as the command has run as your existing transaction log backups are worthless for recovery if you need to recove beyond the time you changed the recovery mode.)

2. Backup the Transaction log between commands.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com