Daily Backups for a single table in Microsoft SQL Server

Question

Hello,

I have a table in a database that I would like to backup daily, and keep the backups of the last two weeks. It's important that only this single table will be backed up.

I couldn't find a way of creating a maintenance plan or a job that will backup a single table, so I thought of creating a stored procedure job that will run the logic I mentioned above by copying rows from my table to a database on a different server, and deleting old rows from that destination database.

Unfortunately, I'm not sure if that's even possible.

Any ideas how can I accomplish what I'm trying to do would be greatly appreciated.

Thank you.

Answer 1

You can create a DTS/SSIS package to do this.

Answer 2

You back up an entire database.

A table consists of entries in system tables (sys.objects) with permissions assigned (sys.database_permissions), indexes (sys.indexes) + allocated 8k data pages. What about foreign key consistency for example?

Upshot: There is no "table" to back up as such.

If you insist, then bcp the contents out and backup that file. YMMV for restore.

Answer 3

I've never done this, but I think you can create another file group in your database, and then move the table to this filegroup. Then you can schedule backups just for this file group. I'm not saying this will work, but it's worth your time investigating it.

To get you started...

http://decipherinfosys.wordpress.com/2007/08/14/moving-tables-to-a-different-filegroup-in-sql-2005/

http://msdn.microsoft.com/en-us/library/ms179401.aspx