Taking Database backup for every 4 hrs in SSIS

hi..
i'm trying to retrieve the data from database using ssis package..
database name is employee which contains 48 tables and it is around 20GB of memory.. it is a production
database..

now what i want is to retrieve the data for each four hours..
i.e i want to execute the package for every 4 hrs..

now what i did is i have taken a database transfer task and from the
source i.e production database i want to transfer to my database
which works fine..

but what is the problem here is for every 4 hrs whole DB is replaced in my system
i've given overwrite TRUE..

Here user enter the data into database for every 4 hour is minimum 10 MB of data..
instead for replacing the whole database for every 4 hrs is there any
solution that what user enter data into database only that much amount of data should be append into my system.. is it possible

please help me..

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Subhendu De15y ago
Yes it is possible but you need to work on this.

The rows are basically copied over from source to destination. You can create stored procedure for each table to find the extra added rows and list all the procedures in SSIS and schedule the SSIS using SQL Server job schedular.

In stored procedure, you need to find the extra added rows using join clause. This is one way to transfer recent data from original to backup without dropping the backup.