SQL Server - Qual estratégia de backup/restauração devo usar para realizar o seguinte?

Dadas as seguintes informações:

Nossos engenheiros de suporte precisam pegar backups de banco de dados de clientes.

e:

Os dados mais "valiosos" são "a configuração" do nosso sistema. Ele está contido em cerca de 50 tabelas...

e:

Estou preocupado em alterar temporariamente o modelo de recuperação para esse fim [de permitir que o grupo de arquivos "Data" se torne ReadOnly para que possa ser omitido do backup]

Eu sugeriria uma abordagem um pouco diferente: use SqlPackage.exe (parte do SQL Server Data Tools / SSDT) ​​para "extrair" ou "exportar" as tabelas e dados desejados em um único arquivo que pode ser recuperado e "publicado " ou "importado". O SSDT é gratuito, portanto, não deve haver nenhum problema de licenciamento relacionado à colocação do executável SqlPackage.exe (e quaisquer DLLs dependentes etc.) nos sistemas cliente.

Você só precisa ter certeza de que SqlPackage.exe (e quaisquer DLLs dependentes, etc) estão em cada sistema cliente. Você pode iniciar SqlPackage.exe de xp_cmdshell. Você pode até mesmo criar um script .CMD que execute SqlPackage.exe com os parâmetros de linha de comando apropriados (existem vários -- ou você pode colocar a maioria das opções em um arquivo de "perfil de publicação" baseado em XML e especificar isso na linha de comando ). Você então transferiria o arquivo compactado ( .dacpac ou .bacpac ) localmente, assim como está transferindo o arquivo de backup agora. Se você estiver enviando por FTP para seu servidor local a partir deles, você pode incluir os comandos de FTP no script .CMD e, se colocar oEXEC xp_cmdshell 'ExportAndTransferData.cmd';declaração em um procedimento armazenado, você só precisaria executar esse procedimento armazenado :-).

Olhando para as várias opções e sabendo que você deseja um subconjunto das tabelas, estou pensando que você deve primeiro tentar um "Extrair" (ou seja /Action:Extract, você não deseja todas as tabelas, então especifique: /p:ExtractAllTableData=false. Então, para cada tabela que você deseja, especifique: /p:TableData=schema.table(você terá cerca de 50 dessas opções especificadas, dado que deseja cerca de 50 tabelas) Finalmente, pode ser uma boa ideia verificar a extração especificando: /p:VerifyExtraction=true.

Depois que o arquivo .dacpac for recuperado localmente, você o "Publicará" (ou seja, /Action:Publish. Para esta ação, você só precisa de um banco de dados de modelo com todo o esquema (tabelas, PKs, FKs, índices, visualizações, etc) e procedimentos/funções armazenados já está lá. Esta operação apenas colocará os dados nas tabelas apropriadas.

Exemplo de script CMD ( ExportAndTransfer.cmd)

@ECHO OFF

SET SQLPATH="C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin"

%SQLPATH%\SqlPackage.exe ^
    /Action:Extract ^
    /OverwriteFiles:True ^
    /SourceDatabaseName:ClientDatabaseName ^
    /SourceServerName:(local) ^
    /TargetFile:%TEMP%\ConfigurationTables.dacpac ^
    /p:IgnoreExtendedProperties=True ^
    /p:ExtractAllTableData=False ^
    /p:TableData="dbo.Table1" ^
    /p:TableData="dbo.Table2" ^
    /p:VerifyExtraction=true

ftp -s:path\to\FtpScript.txt my_server_hostname

Procedimento armazenado de exemplo para chamar o script CMD

CREATE PROCEDURE dbo.RunExtract
AS
SET NOCOUNT ON;

EXEC xp_cmdshell 'C:\path\to\ExportAndTransfer.cmd';

GO

Tudo o que você precisa depois desses dois é o script ftp.

ATUALIZAR

Embora a operação "Extrair" seja geralmente mais indulgente do que a operação "Exportar" devido ao fato de "Exportar" não gostar de vários tipos de objetos, neste caso específico, a operação "Exportar" funcionou para o OP, pois não requer a captura de todas as tabelas que estão relacionados via Chaves Estrangeiras. A operação "Extrair" requer que, se for especificada uma tabela que tenha quaisquer FKs para outras tabelas ou se quaisquer outras tabelas FK para ela, todas as tabelas vinculadas de alguma forma à tabela desejada devem ser incluídas. Encontrei este tópico do fórum do MSDN discutindo o motivo desse requisito:

Um dacpac pode ser publicado em qualquer banco de dados -- vazio ou não vazio. E quando os dados da tabela são publicados, se a tabela de destino não estiver vazia, todos os dados são descartados e substituídos. Mas se a tabela de destino não estiver vazia e tiver dependências de FK de entrada, é possível que haja referências de FK quebradas. Exigir que todas as tabelas sejam um conjunto fechado resolve esse problema, porque todas as referências recebidas também serão apagadas.

Há menos opções para especificar para "Exportar", então a linha de comando seria mais parecida com a seguinte:

%SQLPATH%\SqlPackage.exe ^
    /Action:Export ^
    /OverwriteFiles:True ^
    /SourceDatabaseName:ClientDatabaseName ^
    /SourceServerName:(local) ^
    /TargetFile:%TEMP%\ConfigurationTables.bacpac ^
    /p:TableData="dbo.Table1" ^
    /p:TableData="dbo.Table2"

Você está correto, exceto que você pode fazer backups parciais e restaurações fragmentadas com o banco de dados no modo de recuperação simples, mesmo que você perca certa flexibilidade dessa maneira. Para fazer uma restauração fragmentada online, ou seja, colocar o banco de dados online sem os grupos de arquivos somente leitura, você precisa da edição corporativa do SQL Server. Lembre-se de que a edição de desenvolvimento possui todos os recursos corporativos.

1. Sim, um backup parcial é um backup do arquivo de dados primário e de todos os grupos de arquivos de leitura/gravação no banco de dados
2. Esta é uma restauração online, você precisará da edição corporativa para fazer isso. Mas isso também funcionará na edição dev
3. Você pode usar o modelo de recuperação simples para isso e não precisará fazer nenhum exercício para alterar o modelo de recuperação, basta alterar seus backups atuais para serem parciais e percorrer todos os cenários possíveis.

Uma boa redação com exemplos é sobre conversa simples