А как боролись с объемом файлов данных которые порождает BCP - ? клали в папки типа Compressed folders?

Подскажите пожалуйста способы наиболее эффективного бэкапа для партицированных таблиц, которые используют файловые группы в циклическом режиме
Т.е. заниматься частым архивированием только активной партиции
+ иметь возможность truncate данные одной партиции и восстановить ее без манипуляций со switch
Из
https://docs.microsoft.com/ru-ru/sql/t-sql/statements/backup-transact-sql?view=sql-server-ver15

https://blog.coeo.com/five-cool-things-you-can-do-using-partitioning

Я понял что можно переводить в режим read only только файловые группы и архивировать именно файловые группы, но я не вижу команд для архивирования просто партиций . Просто при циклическом переключении файловых групп неудобно их переводить в Read only и обратно (нужно следить скриптом)
SQL Server 2019

Да именно бэкап. База является буфером который наполняется последовательно , и поэтому хотелось сократить время резервного копирования + иметь возможность оперативно убирать \восстанавливать старые партиции.
Но видимо придется делать обычную схему - полный бэкап раз в неделю + инкрементальный + дневной бэкап логов.
А с архивными партициями управляться через BCP , конечно неудобно что в MS SQL нет удобного механизма подключения и отключения архивных партиций а только через BCP + Switch .

Надо сказать switch это еще то приключение даже если делаешь таблицы как копия метаданных
switch out проходит норм , а для switch in приходится городить констрейнт

ALTER TABLE [dbo].[msg_buffer_temp] ADD CONSTRAINT Range_msg_buffer_temp CHECK (MsgCounter>2560000000 AND MsgCounter<=2590000000);
иначе MS SQL выдает ошибку "allow values that are not allowed by range defined by partition"

Т.е. получается что партицирование как инструмент удобного перевода исторических данных в архив и обратно (по партициям) не такое уж и удобное.
1) Alter table switch работает только в рамках одной файловой группы в другую базу его не сделаешь
2) Бэкап, truncate , восстановление нужной партиции можно сделать только через BCP - долго
3) Бэкап\восстановление отдельных партиций через файловые группы можно делать только если она в read -only, т.е последующий транкейт не сделаешь.

Т.е. с одной стороны куча контролей по констрейнтам, а с другой узкие возможности по переносу данных.


Получается если чтото историческое хочешь отправить в архив проще
1) Сделать архив базы buffer
2) Затранкейтить ненужные партиции
Если понадобится восстановить отдельную партицию
3) При необходимости восстановить его в том же Instance SQL под именем buffer_backup
4) Далее через insert перенести данные в нижний диапазон. Хорошо что insert почти не раздувает transaction log

Я читал про секционированное представление, но для себя решил пока его не использовать по 3м причинам
1) Чтобы соединить два набора данных (архивная + актуальная таблицы) и это будет View с union причем таблиц из разных баз одного инстанса . Т.е. для оптимизатора это будут по сути разные таблицы с разными индесами (даже если там все логически одинаково) и он наверняка будет всегда делать поиск по обоим всегда.
2) Мне может понадобится восстановить более чем один диапазон из архива из разных бэкапов.
3) У меня много запросов с разными комбинациями условий и join + чтобы избежать обращения ко всем партициям (известная особенность) приходится везде ставить условие на основное поле партицированного индекса MsgCounter.

Учитывая это решил, что заставлять оптимизатор Microsoft работать правильно сложнее чем загнать архивные данные через insert если нужно.

Еще один подводный камень с партициями.
Нужно сделать массовую загрузку данных в несколько свободных партиций, а для этого нужно отключить все индексы кроме кластреного (для скорости)
И вот что получается - ребилд по партициям можно сделать , а Disable уже нет только для всего индекса
https://docs.microsoft.com/ru-ru/sql/t-sql/statements/alter-index-transact-sql?view=sql-server-ver15

У microsoft формально есть решение с 2005 года, типа грузите в стейдж таблицу, там ребилд индексов а далее переключите ее в партицию
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2005/administrator/cc966380(v=technet.10)
т.е. если еще нужно констрейнты по диапазонам ставить то уже эта акробатика напрягает. Вроде бы партиции специально придумали для изолированной работы с партициями , но по факту без stage таблиц ничего не живет. Сразу захотелось поизучать как дела у Oracle с этим, по опыту использования там всегда продуманней механизмы были