Database Administration/Maintenance - Guidance Needed

I have been working SQL server for many years as a web developer, however in my current position we are lacking an expert in maintenance and admninistration as the company has recently switched over to SQL Server 2005.

I therefore need to develop a greater understanding of the best practices with regards to administration and am a bit lost as there are so many options which seem like they might match my requirements. As I have never looked beyond the basic administration/maintenance side of things before I have no idea which options to investigate.

Basically I want to clear any records older than 3 months old on a scheduled/automatic basis. There seem to be various options that could do this, however I want to know what the best option is, rather than waste a lot of time and risk problems by resorting to trial and error.

We are already backing up this data, however I am not sure if I should combine these tasks. It is highly unlikely that we will ever have to restore these older records, however we do need to store the data for legal reasons.

I dont expect anyone to tell me everything, as I am happy to investigate, I just need a nudge in the right direction.

Thanks

maw

Hi there,

Do you have an easy way of distinguishing what records you want to delete (e.g. the records have a timestamp field associated with them)?

I would define a SQL Agent job that runs as per your desired schedule and executes some T-SQL that will delete the records as required.

In terms of backups, I would be tempted to take a "before" and "after" backup of the data. You can work the "before" backup into your regularly scheduled backup routine. For example (times are example only):

2:00am Regularly scheduled backup job runs and takes DB backup

2:30am Job to cull records older than 3 months fires & as part of the job execution, after all the cull logic has fired a BACKUP DATABASE statement is issued.

Thus, your job to cull records older than 3 months would look like:

/*
BEGIN: T-SQL statements for job
*/
-- Logic for deleting records
DELETE....
DELETE....

-- Backup after changes
BACKUP DATABASE Test_DB
TO DISK = 'C:\Backup_After_Delete.bak'
./*
END: T-SQL statements for job
*/

Hope that helps a bit, but sorry if it didn't
|||

Thanks Nate, thats exactly what I needed to know.

I had already read up a little bit on setting it up as a job, however I wasnt sure it was the best method.

I do have timestamp fields set up, so this should work perfectly.

Thanks again for your help.