How to delete a physical file from server when related entry from database is removed

Joe Green 146 Reputation points
2021-02-11T14:00:38.873+00:00

Hello,

I've an .net MVC application that stores data in MS SQL Server 11. Most of the entries in the database has one or more physical files associated with them. This is what I need to do

  • Schedule a script/task to run everyday at a specific time
  • This script should delete records from the database with a certain status and delete physical files associated with these entries stored on the file storage (files are stored outside MS SQL Server).

Is this possible to do and how?

Joe

ASP.NET
ASP.NET
A set of technologies in the .NET Framework for building web applications and XML web services.
3,375 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,099 questions
{count} votes

4 answers

Sort by: Most helpful
  1. Guoxiong 8,126 Reputation points
    2021-02-11T17:00:08.163+00:00

    You can create and schedule a SQL job to archive your targets.

    1. Create a query to retrieve the file names and their paths from the table
    2. Delete the files DECLARE @CMD nvarchar(max);
      DECLARE @fileFullName nvarchar(128);
      SET @CMD = N'xp_cmdshell ''del "' + @fileFullName + '"''';
      EXEC(@CMD );
    3. Delete those records from the table

    You can use a query or a stored procedure to do the above tasks.

    0 comments No comments

  2. Joe Green 146 Reputation points
    2021-02-11T18:28:00.667+00:00

    Would 1, 2 and 3 be Steps in of a SQL Server Agent Job or I need to put all the logic in a Stored Procedure? Can I schedule execution of a SP?
    I'm able to query the database and get the filenames in step 1 but how do I pass those filenames from Step 1 to Step 2?


  3. Jeffrey Williams 1,891 Reputation points
    2021-02-11T22:37:48.02+00:00

    This would be much easier to do in Powershell - and you can create an agent job that will run the powershell commands. This also has the added benefit of restricting access to a proxy account that only has the necessary permissions in the database - and the file system - it needs to perform the job.

    You can use Invoke-SqlCmd to get the list of items from the database, then a for each loop over the results to check for existence of the file - if it exists, delete the file. The code you use in SQL would be a DELETE using OUTPUT to output the results which then gets returned to PS.

    This would also give you flexibility to archive the files - log the actions taken or any other processes you want to include.

    0 comments No comments

  4. Joe Green 146 Reputation points
    2021-02-19T19:02:39.95+00:00

    All the database operations are done using SQL DB user account. Do I need to use Active Directory account to run this SP and be able to delete physical files from file server or can this SP be run by SQL DB user?

    0 comments No comments