Export table back up and restore

Question

Hi Expert,

How i can take table backup and restore again in sql server when more data is available at the time of import

create table table2

(col1 date, col2 char)

insert into table2
values('2022-02-02',22)

then took backup
at the time of restore found 2 more incremental records in table2

insert into table2
values('2022-02-03',22),
('2022-02-04',22)

how it will restore and backup with dates with new records

how it will restore and backup with dates with new records

Answer 1

If you set up backups with full, (differential), log backups (full recover mode), you can do point-of-time restore with your backup files.

Answer 2

I just needs table level backup

Answer 3

Hi Shambhu Rai

How i can take table backup and restore again in sql server when more data is available at the time of import

I am not sure if I understand your request. Do you want to backup only specific table? Or Do you mean backup of the entire database and focus on what happen with data added before the backup, during and after

For first option, there is no build in BACKUP for table but you can BACKUP specific file. So if your table is in that file then you can backup and restore specific file.

For backup of the database and how it is working, there are great tutorials online and a message in the forum will; not cover it. I will give some words but better to read a full post about it

So, in a nutshell, very short:

There are several types of Database Backup

• Full Database Backup
• Differential Database Backup
• Log Backup

You always need to have a full backup first. This provides a starting point for any chain of backups.

Next you can have Differential backup which include all changes till this point in time and/or you can have a log backup which include the transaction from previous log backup.

If you add data to the database after the full backup, and you restore all the log backups including these after the INSERT then your new data will be there. In other worlds one way to restore the database will be to restore the full backup + all the Log backups.

So, option one: restore full backup + all transaction log backups after the full backup

If you restore the full backup and one of the Differential backup then you have all the data at the time that this Differential backup was done. There is no need for previews Differential backups since each Differential backup includes all changes from the full backup to the time it was made.

So, option two: Restore the full backup + one of the Differential backup + all Log backups after that Differential backup

In most cases people use only full backup and log backups, or full backup and last Differential (there is no reason usually to keep older Differential backup since the next one include the changes of previous Differential backup) and the log backup after the Differential backup.

Is this make it more clear and cover your question?

Please check the Doc of the BACKUP for more information:

https://learn.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql?view=sql-server-ver16&WT.mc_id=DP-MVP-5001699

Answer 4

can i take backup based on the date available in table and restore it based on the same date

Answer 5

For first option, there is no build in BACKUP for table but you can BACKUP specific file. So if your table is in that file then you can backup and restore specific file.

Are you talking about mdf file it is 176 gb.. difficult to take backup and there is no space for it