Início / dba / Perguntas / 198638
Accepted
Andrew Lackenby
Asked: 2018-02-24 02:30:58 +0800 CST

Excluindo arquivos de dentro do SQL Server

  • 772

Eu tenho usado xp_delete_filepara excluir arquivos de backup antigos, mas agora preciso excluir arquivos que fazem parte de um aplicativo (como parte do GDPR). Como xp_delete_filesó permitirá que você exclua arquivos de log ou backup, pensei em escrever o meu próprio em C#.

O problema que estou encontrando agora é que procedimentos armazenados estendidos só podem ser implementados no banco de dados mestre. Prefiro conter esse novo procedimento armazenado em meu próprio banco de dados de usuário para poder distribuir via controle de origem etc.

Alguém sabe uma maneira de contornar isso sem usar xp_cmdshell?

Isso pode ser feito no PowerShell. Eu poderia até escrever a coisa toda em C#. No entanto, é realmente útil poder fazê-lo em T-SQL também.

Estou aberto a usar um procedimento armazenado CLR que invoca o código C#. Eu estava pensando em alterar o procedimento armazenado para uma função se não puder implantar um procedimento armazenado.

sql-server files

1 respostas

  1. Best Answer

    Os procedimentos armazenados estendidos (XPs) foram preteridos desde o lançamento do SQL Server 2005 e novos projetos não devem usar essa API.

    Isso pode ser feito facilmente via SQLCLR e, sim, você pode retornar uma string (ou a maioria dos tipos de dados) de um método C#. A única construção restrita a retornar um INT é um procedimento armazenado, e isso vale para procedimentos armazenados T-SQL e SQLCLR.

    Você usaria algo ao longo das linhas de:

    using System;
using System.IO;

public class Stuff
{
  [SqlFunction(IsPrecise=true)]
  public static SqlString DeleteFile ([SqlFacet(MaxSize=500)] SqlString FilePath)
  {
    string _ReturnMessage = String.Empty;

    try
    {
      File.Delete(FilePath.Value);

      _ReturnMessage = "some text here";
    }
    catch (Exception _Error)
    {
      _ReturnMessage = _Error.Message;
    }
  }

  return new SqlString(_ReturnMessage);
}

    Ao criar o objeto wrapper T-SQL, certifique-se de especificar a RETURNS NULL ON NULL INPUTopção para que ela não seja executada se a NULLfor passada (você provavelmente deve adicionar validação adicional ao método, como retornar se uma string vazia for passada em, etc):

    CREATE FUNCTION dbo.DeleteFile
(
  @FilePath NVARCHAR(500)
)
WITH RETURNS NULL ON NULL INPUT
EXTERNAL NAME.... ;

    Como o Assembly precisará ser marcado como PERMISSION_SET = EXTERNAL_ACCESS, não defina o banco de dados como TRUSTWORTHY ON. Em vez disso, você deve assinar o Assembly, criar a chave ou certificado assimétrico em [master], criar um logon a partir dessa chave ou certificado assimétrico e, em seguida, conceder a EXTERNAL ACCESS ASSEMBLYpermissão de logon (se estiver usando o SQL Server 2005 - 2016) ou a UNSAFE ASSEMBLYpermissão (começando com o SQL Server 2017 ).

    Para obter instruções detalhadas sobre como fazer isso por meio de um processo de compilação, você pode tentar uma das duas técnicas que descrevo nas seguintes postagens do blog (ambas funcionam totalmente com SSDT ou de forma independente):

    Considere também apoiar minha sugestão de chave assimétrica que eliminaria a maior parte do incômodo de publicar assemblies assinados:

    Permitir que a chave assimétrica seja criada a partir da string de bytes hexadecimais binários, assim como CREATE CERTIFICATE

    OU, se você preferir não lidar com nada disso, existe uma função File_Delete na biblioteca SQL# SQLCLR (que eu escrevi) que faz isso. Ele ainda retorna uma string, que é a mensagem de erro se ocorrer um erro, senão é apenas uma string vazia. No entanto, não há como fazer com que ele retorne uma string personalizada em caso de sucesso. Além disso, observe que, embora haja uma versão gratuita, as funções do sistema de arquivos estão disponíveis apenas na versão completa. Mas o aspecto de segurança seria tratado de forma limpa e adequada, e existe até um T-SQL Stored Procedure, que existe no mesmo banco de dados que o código SQLCLR, que configurará o Asymmetric Keye associado Loginem[master](o que pode ajudar nas implantações em novos sistemas, não exigindo uma instalação completa, desde que este procedimento armazenado tenha sido implantado e executado antes da implantação de quaisquer Assemblies (assumindo um ambiente do SQL Server 2017)).

    ATUALIZAR

    O SQL Server 2019 (que não estava disponível no momento em que publiquei esta resposta) apresenta um novo procedimento armazenado do sistema não documentado, xp_delete_files, que é um pouco mais flexível do que o xp_delete_file. Para mais informações, veja minha resposta aqui (também no DBA.SE):

    Como o novo procedimento armazenado do sistema sys.xp_delete_files é diferente de sys.xp_delete_file?

    • 1

relate perguntas