Post reply

Archiving data files

  • February 7, 2006 at 9:04 pm

    #116142

    Hi everyone,

    I'm new to managing databases so forgive me.  We have a DBA but he is purely Oracle, so I'm looking after the SQL stuff.

    SQL server has been running for ages, and I thought it time to do some cleanup.  I've managed to backup and shrink transaction logs, which were getting huge.  Excellent start.

    Next for one database I'd like to archive the data file completely and start with a new (blank) one. Reason being, the data is historical only and never looked at..(it's the database for our incoming email filter, so there are no users actually querying it).   I just need to keep it in case of auditing 6 months down the track.  The data file is currently nearing on 4gb and is a waste to keep live.

    I can down the database and take a copy of the data file, zip it up and throw it on a DVD... but is there an easy way to truncate the entire data file?  I can delete the database, create a new one and then tell the application it has a new database, which would then recreate all its tables.. but I'd like to know if there's an easier way?

    If I wrote a script that truncated every table, would that reduce the file size (I'd need to perform a shrink after doing that I would think).

    Kind regards

    Dave

  • February 7, 2006 at 9:12 pm

    #619442

    After thinking about this I realise that's not a great approach.    I've found the application in question actually has a purge stored procedure which deletes data from all the big tables

    I'll back up and give it a go.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply