We help IT Professionals succeed at work.

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

541 Views
Last Modified: 2017-03-20
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
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2013

Commented:
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
yo_beeDirector of Information Technology
CERTIFIED EXPERT

Commented:
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

IT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
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.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.