The provider model is useful for supporting different kinds of objects that support an interface and determining which kind to load at runtime. We don't need it for the database layer because which database to use is an install time decision not a runtime decision and I don't want the overhead of runtime checks for which provider to use for every data access method.
We use a static service facade pattern, using static methods guarantees the methods are threadsafe.
The solution of backing up data by creating insert sql statements can work if you are only concerned about backing up and restoring against 1 single database platform. The statements that would work for MS SQL will not work accross dbs. It makes sens for your goals but for my goals I need a common format to export/import from.
I don't use DataSets myself but in this case they offer a good convenience because they are easy to make, they have the schema in their meta data and they already provide an ability to export to xml. So I'm just saying for my goal it seems this would offer value. If I were only concerned about MS SQL I would not find it attractive because it would be easier to work directly with MS sql tools.