SQL server 2008: Copy a file and rename it

Question

I have a file inside a directory \\myServer\Admin\temp\testtemp.txt

I need to write a TSQL to

1. Search for testtemp.txt file.
2. If exists, create a copy of it and rename it to Copytesttemp.txt

3. If there's testtemp.txt already in the above directory like this

   \\abcd\Admin\temp\Copytesttemp.txt
   

4. then delete it and recreate Copytesttemp.txt

How do I achieve it? Thanks.

Answer 1

You can use xp_cmdshell to run any DOS commands you like, e.g.

declare @cmdstring varchar(1000)

set @cmdstring = 'copy \\myServer\Admin\temp\testtemp.txt \\myServer\Admin\temp\Copytesttemp.txt'
exec master..xp_cmdshell @cmdstring 

Just make sure xp_cmdshell is enabled on your installation.

Answer 2

Create a SQL Agent job which runs a command script to do the actions.