Redefinindo um novo banco de dados de arquivamento

A maneira integrada de reduzir o tamanho do backup para VLDBs com dados somente leitura é particionar suas tabelas grandes, colocando partições mais antigas em grupos de arquivos somente leitura e, em seguida, fazer backups pouco frequentes dos grupos de arquivos somente leitura (ou banco de dados inteiro), combinados com backups regulares dos grupos de arquivos de leitura e gravação. Em seguida, você pode executar uma restauração fragmentada .

[usando as visualizações UNION ALL em vários bancos de dados] funcionará?

Sim, normalmente você pode substituir uma tabela por uma visualização sem alterar o aplicativo, a menos que o aplicativo faça algo incomum. O risco está no desempenho, pois a exibição UNION ALL em vários bancos de dados não tem o mesmo desempenho.

Você pode tentar usar a compactação columnstore nas tabelas grandes no banco de dados de arquivo morto.

Posso criar uma visualização/sinônimo (a visualização consultará ambos os bancos de dados da tabela com UNION ALL) que será o nome do objeto padrão em vez do nome da tabela original real?

Sim, você pode criar uma visão/sinônimo para consultas entre bancos de dados.

aqui estão alguns efeitos colaterais que vêm à mente ao implementar isso:

• Seu aplicativo pode depender de esperar uma tabela e receber uma exibição. (deve haver uma maneira de mascarar isso, mas não consigo encontrá-lo neste momento)
• Renomear sua(s) tabela(s) pode quebrar scripts/outras visualizações/procedimentos/...
• Incompatibilidade de agrupamento entre bancos de dados (se diferente)
• Sobrecarga possível extra devido a duas consultas em dois bancos de dados em vez de uma consulta em um.
• Há muitas considerações extras ao usar bancos de dados Read Only, como estatísticas que não são atualizadas, não são criadas automaticamente, .... Mais sobre isso aqui .

• Restrições/relações entre as duas tabelas nos dois bancos de dados não funcionarão. Isso pode resultar em chaves primárias duplicadas, referências de chave estrangeira não funcionando, ....

• ...

Um exemplo de uma visão sobre duas tabelas em dois bancos de dados

CREATE VIEW [dbo].[maintable]
AS
SELECT id,val FROM [dbo].[maintable_v2] --new table
UNION ALL
SELECT id,val FROM [my_test_old].[dbo].[maintable_old]; --old table

Consultando esta visão recém-criada no novo banco de dados

SELECT id,val
FROM [dbo].[maintable]
ORDER BY id;

Resultado

id  val
1   oldvalue --old table 
2   NewValue --new table

Uma alternativa

A melhor alternativa seria David Browne - a resposta da Microsoft.

Se isso não for uma opção, aqui está uma solução menos ideal:

1. Mova os dados da tabela para uma tabela de histórico criada em um grupo de arquivos somente leitura no mesmo banco de dados.
2. Crie a nova tabela
3. Crie a visualização na tabela de histórico e a nova tabela
4. Faça backup do grupo de arquivos somente leitura separadamente, faça backup do banco de dados sem o grupo de arquivos somente leitura em ocasiões separadas.

Para fazer backup dos grupos de arquivos read_write:

BACKUP DATABASE DatabaseName READ_WRITE_FILEGROUPS  
TO DISK = N'C:\SQLBackups\DatabaseBackup.bak';

5. Se você estiver em uma empresa, poderá fazer restaurações de grupos de arquivos online (por etapas), no padrão, você não pode fazer isso, o que significa que terá que aguardar a restauração do grupo de arquivos somente leitura.

Isso resulta na única vantagem real de dividi-lo em vários bancos de dados quando na edição padrão seria restaurar primeiro o banco de dados não histórico, tornando-o acessível para consultas enquanto o banco de dados histórico está restaurando.

• Outra opção (não para sua versão, a partir do SQL Server 2016): [SQL Server Stretch database][3. Se os dados do histórico frio não forem muito consultados, armazene-os no azure. Isso pode ser feito sem qualquer consulta ou alterações no aplicativo.