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

Question

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

Answer 1

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.

Answer 2

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?

Answer 3

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.

Answer 4

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?