Taking Database backup for every 4 hrs in SSIS

[vinayak.v]

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.. how to do it..

[buddylee17]

Your best bet is probably log shipping, where you restore the full database to the other server once, then at the interval you specify, the transaction log is backed up and restored to the other db. You'll need to be in full or bulk-logged recovery model for this. You'll also need to leave the secondary server in standby (making it read only).

You could also do a differential backup every 4 hours. You'll still have to restore from the full and the differential, but it will definitely cut down on both the impact to the production environment and the amount of network traffic. The differential will only be the amount changed since the last full backup. So if your full backup is 20GB and 10% of the database has changed over 4 hours, the differential will be around 2GB and will not take nearly as long to backup.