Purging an SQL table

Question

I have an SQL table which is used for logging purpose(There are lakhs of records in the table). I need to purge the table (Take a back up of the data and need to clear the table data).

Is there a standard way of doing it where I can automate it.?

Solution 1

You can do this within SQL Server Management Studio, by:

right clicking Database > Tasks > Generate Script

You can then select the table you wish to script out and also choose to include any associated objects, such as constraints and indexes.

Attaching an image which will give you the step by step procedure,

image_bkp_procedure

PFB the stackoverflow link which will give you more insight on this,

Table-level backup

And your automation requirement,

You can download bcp utility which copies data between an instance of Microsoft SQL Server and a data file in a user-specified format. Sample syntax to export,

bcp "select * from [MyDatabase].dbo.Customer " queryout "Customer.bcp" -N -S localhost -T -E

You can automate this query by using any scheduling mechanism (UNIX etc)

Solution 2

Simply we can create a job that runs once in a month
--> That backups data in another table like archive table
--> Then deletes data in the main table
Its primitive partitioning I guess, this way it will be more flexible when you need to select data from the past deleted one i.e. now on archive table where you have backed up

Comments

[Chandra Eskay]

How do I automate this? Say, I want to run it once in a month!

[Mari]

@Chandu-Indyaah I ve edited my answer. Try downloading bcp utility