Back up and restore to main system

Question

I have a software system running in my office with MS SQL Server in back end.I want to backup the database daily and delete all data from database so that only one-day data will be in the database at office. I want to append this daily data to my system at home so that all data including old remains at home pc.

IS it possible to append daily data from office to my home pc?

Answer 1

It's possible, but not just with backup and restore. You've got three separate problems to solve:

Problem 1: How do you delete all but one day at the office?

You can do this with delete statements that touch every table in the system, but if you've got complicated relationships or lots of tables, this can be painful. You have to make sure you only delete tables with changing data, like sales, and not tables that need to be kept, like config tables and customer lists.

If you've got large quantities of data, this can also be painful for the long-running deletes. Table partitioning can be a good fit for scenarios like this when you've got, say, >100GB of data involved.

Problem 2: How do you get the data from office to home?

You could solve this with backup/restore, but given that this is a solution you want to implement permanently, something like replication might make more sense. Transactional replication lets you stream the logged transactions to the second machine, and then your data can look different between the primary machine and the secondary machine. For example, the home machine might have different indexes depending on how much data you're trying to store.

Problem 3: How do you keep adding the historical data at home?

If you use the backup/restore approach, you'll be restoring a new database every day. The historical database should be under a different name, and then write scripts to insert the data from the restored database into the backup database. Again, more tables = more problems here.

If you use the transactional replication approach, this will be much easier.

All three problems are pretty large, so consider breaking this work up into more digestible steps. Short story, though, this isn't a built-in feature.