How to schedule job or script to automate table backup in particular db as a tablename.sql script file

Question

I want to backup one of table in my DB. Before i use to follow generate script select specific table and save as xxxx.sql file manually.

But due to my requirements i have to backup the table every 5 mins which not possible in manual.
i planned to schedule job or to create script to automate it.

Is there any help from anyone.

Thanks

Answer 1

I do not have done this as never faced the scenario you have one now. I am sure this cannot be attained from SSMS because the generate script does not allow us to save this as SSIS package. If that would have been the case we would have saved it as SSIS package and would have called it using SQL Server agent. We now need to rely on powershell or some tool. Below are some links which can be helpful.

Automatic-Script-SQL-Server-2005-Objects-and-Commi

automatically-script-sql-server-table-data-and-object-schema-directly-to-source-control

how-can-i-automate-the-generate-scripts-task-in-sql-server-management-studio-2

Answer 2

At least is there a way i can backup table values automatically.?

Answer 3

There is no such thing as a table backup. At least not with the built-in tools.

You can script the definition of the table. Make sure you do it right and verify that every dependency you need is there. I'm thinking indexes, constraints permissions etc. Do you have to automate this and do it regularly? That is for you to answer. If yes, then check some of the linked that Shashank suggested.

Then you have the data. There are plenty of ways to export data. BCP, SSIS etc. Pick the one that suits you.

Based on above, you can determine how frequently you need to script the table definition (perhaps as often as the export is done) and how often you have to export the data.

And, test getting that stuff into a new database, as well - so you know your approach works.

Answer 4

Hi @Dappu ，

At least is there a way i can backup table values automatically.?

No. SQLServer does not allow users to backup or restore individual tables.
However if you want to achieve that, you can create a new db and then only create one table in it. It means this db only owned by this special table,now you can backup this db for achieving backup the special table.

About how to backup automatically, you need to do as next:

1. Create and use the SQL Server agent schedule
2. Create and use SQL Server Maintenance plan

And detail steps please reference: create-daily-database-backups-with-unique-names-in-sql-server
More information: how-to-delete-old-database-backup-files-automatically-in-sql-server

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 5

Hi @Dappu ，

Is the reply helpful?

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.