Link to home
Start Free TrialLog in
Avatar of Dale Fye
Dale FyeFlag for United States of America

asked on

Using T-SQL (SQL Server 2008 R2) to determine whether file exist on server and delete it

I'm working on a procedure to determine whether a file exists on the path referenced in a field of my database.

In some instances, I will need to be able to copy this file to another location on my server and then delete the original once I've confirmed that the copy operation was successful.

how can I accomplish this with T-SQL in Sql Server 2008 R2
Avatar of chaau
chaau
Flag of Australia image

it is possible with a help of xp_cmdshell
You will need to execute DOS commands. E.g. to check if file exists you can execute this command:
exec msdb..xp_cmdshell 'if exist c:\test.txt echo exists!'

Open in new window

If you want to copy file use:
exec msdb..xp_cmdshell 'copy c:\test.txt c:\test1.txt'

Open in new window

Note: the folders need to be related to the SQL Server, not the client where you execute the SQL queries
I would use Powershell to accomplish this. Here is a link to get the snap-ins installed https://technet.microsoft.com/en-us/library/cc281947(v=sql.105).aspx
From there you can use the Invoke-SQLCMD to query and loop through the records to run a test-path if the fullpath exist and if so do something like remove-item.

#
# Add the SQL Server Provider.
#

$ErrorActionPreference = "Stop"

$sqlpsreg="HKLM:\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps"

if (Get-ChildItem $sqlpsreg -ErrorAction "SilentlyContinue")
{
    throw "SQL Server Provider for Windows PowerShell is not installed."
}
else
{
    $item = Get-ItemProperty $sqlpsreg
    $sqlpsPath = [System.IO.Path]::GetDirectoryName($item.Path)
}


#
# Set mandatory variables for the SQL Server provider
#
Set-Variable -scope Global -name SqlServerMaximumChildItems -Value 0
Set-Variable -scope Global -name SqlServerConnectionTimeout -Value 30
Set-Variable -scope Global -name SqlServerIncludeSystemObjects -Value $false
Set-Variable -scope Global -name SqlServerMaximumTabCompletion -Value 1000

#
# Load the snapins, type data, format data
#
Push-Location
cd $sqlpsPath
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
Update-TypeData -PrependPath SQLProvider.Types.ps1xml 
update-FormatData -prependpath SQLProvider.Format.ps1xml 
Pop-Location


# Gather Table for DB

Set-Location SQLSERVER:\SQL\xxxprolaw\prolaw\databases\prolaw
#gets the query results and stores in an array 
$Records = Invoke-Sqlcmd -Query "SELECT * from CreditValueLevels" 
#for each record Test if the path exists and then do something
Foreach ($record in $records)
{
       If (Test-Path -Path $record.Fullname)
        {
            Remove-item $Record.Fullname -Recurse -Force
        }
}

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Dale Fye

ASKER

Thanks, Vitor.  It looks like the maintenance plan will take care of deleting older versions of database backups for me.  I was looking for a way to do this manually but think doing it in the maintenance plan is the way to go.