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.

  • 1
    I'm not sure if this would work for your needs, but what if you moved the data that needs to be archived into a new database, and did a native sql backup. If you ever had to restore the data, restore to a temp database, then move the data back, or let them query directly from the temp database if that works – Patrick Nov 20 '18 at 15:39
  • @Patrick I've thought of that to work around the limitation of SQL Express... to keep them in different databases but as a method of backing with SQL Backup and keeping those files?!?!? not a bad idea... I like it... I will look into that. – JohnG Nov 20 '18 at 16:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.