How to Backup / Restore individual table in managed SQL database.

Question

In our managed SQL database there are times we have to manually manipulate data in our table for our customers. Prior to doing this we would want to confirm we have a backup of the single table. Can we initiate a backup of a single table? If so then the follow up question how to restore that in the event we need to rollback the data?

I can't find documentation that points specifically to this use case. So I am also wondering other possible methods.

Thanks.

Answer 1

For these kinds of changes - I recommend the following code pattern:

Begin Transaction;

--==== Backup data to be changed
Select *
  Into Backup_Table  --name this with a date/time stamp to show when it was done
  From yourTable
 Where {same conditions as applied in insert/update/delete};

--Review data prior to change
Select * From Backup_Table;

--==== Perform the change
Update t
Set t.col = somevalue
Where {same conditions};

--==== Show changed data
Select *
  From yourTable
 Where {same conditions here};

--==== Commit/Rollback transaction
Rollback Transaction;
--Commit Transaction;  --uncomment this line, comment out the rollback when ready to run and commit the changes

The idea here is to perform the update and rollback the changes - validate only those rows you expected to be changed were changed and the expected changes are what you want. Once confirmed - running with commit will create the backup table (for example: yourTable_Backup_20210616) - update the data - and commit the changes to the database.

Answer 2

No, l you can't restore a single table from a backup. Well, you can if that table is a on a filegroup on its own, since you can restore a filegroup. However, if you restore only part of the the database, you also need to apply transaction logs to bring the filegroup in sync with the rest of the database. That is, you cannot use this to look at how a certain table looked yesterday.

If you need to do this, you need to restore the backup to a separate database and copy data over.

Answer 3

Hi @Anonymous ,

>Can we initiate a backup of a single table?

Microsoft has not yet introduced any functionality for restoring just a single table without restoring the entire backup, keeping in mind that it might result in a compromise with the referential integrity of the database.
You can't restore a single table directly from your backup to a database. You could restore your complete backup to new database and then copy your table from there to the desired database.
Please refer to this blog about database Backup and restore: https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/quickstart-backup-restore-database?view=sql-server-ver15
And this article maybe guide you how to restore a single table: https://www.sqlmvp.org/restore-single-table-from-backup/

Best regards,
Seeya

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.