Sql-server – Copy last modified files from network path into SQL server box and rename them using Agent job

sql serversqlcmdt-sqlxp-cmdshell

I have to copy latest backup from a network path and bring that to my SQL server physical drive for restore, I have to do that using agent job.

This is what i am doing.

Created a SQL server agent job

1) XCOPY source *.bak /Y destination < This copies all the files, but I need the latest file >
2) Ren *bak mydbbackup.bak <**added this into step 2 of job **>

Questions:

What mistake am i doing here ?

*Are there any xcopy **/**commands which can copy the latest files only and ignore old files ?

Is there a way i can do that using another SQLCMD command besides creating a stored procedure*

Thanks,
learner

Best Answer

Found a work around :

I have created ps1 file using the script below.... . . . $source = '' $destination = ''

@(Get-ChildItem $source -Filter *.bak | Sort LastWriteTime -Descending)[0] | % { Copy-Item -path $_.FullName -destination $("$destination\CMSOPEN_backup.bak") -force} . . .

and then, created bat file which calls this ps1 file, went to sql agent job, created step to execute the bat file later executed the t sql for restore.

Related Question