question

RussellWaliszewski-8558 avatar image
0 Votes"
RussellWaliszewski-8558 asked RussellWaliszewski-8558 commented

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

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.

sql-server-general
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @RussellWaliszewski-8558,

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it dosn't help, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

0 Votes 0 ·

All answers confirmed what I suspected. I accepted the answer which I felt addressed my need.

Thanks to all.

0 Votes 0 ·
JeffreyWilliams-3310 avatar image
0 Votes"
JeffreyWilliams-3310 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SeeyaXi-msft avatar image
0 Votes"
SeeyaXi-msft answered ErlandSommarskog commented

Hi @RussellWaliszewski-8558,


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://docs.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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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.

Actually, "not yet" is not correct. Microsoft had this feature in SQL 6.5. But they dropped in SQL 7, exactly for the reasons you say. I seem to recall some horror stories with that feature.

0 Votes 0 ·