Backup of particular table

Question

Hello all How can we take backup and restore a particular table using script. When I take backup using ssms, it takes full database backup as .bak file. Any suggestions?

Kalyan Kondapuram · Answer

Hi Devendra , Actually there is no option for MS SQL SEVER to take scheduled backups for tables alone . Its a full backup of database that has to be done either through schedule or manually . As far as I understood from you requirement is to take only particular table backup and restore when ever necessary and not complete DB backup right . Correct me if its not . Best way is to schedule particular table backup through SSMS Job on daily basis and use it when ever required . If data is very huge there are mutliple ways like Delta loading using Date column or unique key to ensure recent data is available and load that particular data only !! Best way to take backup : Select * into Backup_OriginalTable from OriginalTable

Sanwar Ranwa · Answer

Hello Devendra Please check these Links and follow steps https://sqlbak.com/blog/how-to-backup-a-table-in-sql-server/ https://solutioncenter.apexsql.com/how-to-restore-specific-tables-from-a-sql-server-database-backup/ https://dba.stackexchange.com/questions/102745/how-can-i-take-backup-of-particular-tables-in-sql-server-2008-using-t-sql-script

Sourav Mukherjee · Answer

Hi Devendra, Table level backup is not possible in SQL Server. Instead you can do 2 things:- 1. create another table and insert all records select * into from 2. Use bcp/export utility and take bkp of all records of the table. Also script out table script. Hope this helps