I have been asked to truncate a large table in MS SQL Server 2008. The data is not needed but might be needed once every two years. It will NEVER have to be changed, only viewed.

The question is, since I don't need the data on a day-to-day basis, what do I do with it to protect and back it up?

Please keep in mind that I will need to have it accessible maybe once every two years, and it is FINE for us if the recovery process takes a few hours.

The entire table is about 3 million rows and I need to truncate it to about 1 million rows.


How do you plan on accessing the table data ?

If you're using something a bit flexible (your own code or simply SQl queries) then an easy way out is to copy the needed table to another database and the detach it from the server. Afterward, truncate the table (or delete the unwanted rows, although you might want to check your T-Log size of you do that).

When you'll need it, you can always remount the relevant database from storage in read only mode. If you need to run queries on multiple backups at once, you can create a view regrouping all the data.

If you're using a software that won't allow you to customize how that table is accessed, you can still copy the table to another database on its own but restoring that backup can be problematic because you'll need to merge the existing context (the other table states) with the old data. Plus, the data structure might have changed making it next to impossible to restore.

So in that case, I would suggest that you simply take a backup of the full database and store that (preferably along with a version of the software that accesses it).

  • thanks so much this is a great answer. what do you think of just storing what i dont need in a .BAK file? – Alex Gordon Nov 23 '11 at 16:43
  • It all depends on the application and youre resources: if you have the necessary infrastructure to duplicate the environment when you'll need it or if the application can be retargeted to a different DB, you can do it all with backups, no problem. – Stephane Nov 24 '11 at 12:48

3 million rows isn't very many, especially for sql server. Also, you cannot truncate a table and keep rows. Truncate empties completely.

Also deleting is slow, even if you have the time.

I would right click on your table, script as, create new table. Rename it, to like Table_Backup11232011, make sure to rename your primary keys as well...

insert into Table_Backup11232011 select * from oldtable

truncate table oldtable

Now, you have a backup, and a fresh table. 3 million rows will only cost you few hundred megabytes probably, which is nothing.

  • 1
    3 million is tiny. mobil phoens do it - database servers dont even realize there is any data with that tiny amount. – TomTom Nov 24 '11 at 4:16

Archiving/Accessibility: Here's a thought... 1) Perform your data deletion 2) Place tyhat table in its own SQL File Group 3) Burn the file to a read-only device (Space allowin) 4) Use ALTER TABLE to redefine whaere the file is and define it as Read-only 5) Party on, Garth!

Now the data is off of your hard drives, unalterable but still available when needed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.