Right now, we are backing up data per table into text/CSV files per month. This works fine for fairly small tables. For bigtables, the text files become so big that the restore process for a month of a table can take days to restore.
We are looking for a way that we can partition the data per month and store it somewhere that if it is ever needed it can be restored for a single month or all months required by the customer... the problem is that we have to find a solution that will work for SQL Server 2008 to SQL 2017 for Express to Enterprise editions. Unfortunately, 99% of the databases that our customers have installed are using the simple recovery model.
I have looked into partioning with different filegroups, but couldn't dettach a filegroup (a month worth of data) and reattach it by itself. I've also looked into BCP, but the performance isn't that great.
It would be great if I can get the partitioning with different filegroups to work since it seems the closest thing to what we want to accomplish...
I'm exploring the SQLPackage.exe which I just came across in one of the posts (had never heard of it before)
I'm looking for other ideas I can explore?
I've researched posts on stackExchange but couldn't find a solution to my problem.
