0

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

  • Did you try to get the latest full backup file name, the destination form system tables? Script to retrieve SQL Server database backup history and no backups – SqlWorldWide Jan 6 at 18:50
  • yes, its good t sql...but my case is to copy the latest files and restore them in test server . – datalearner Jan 6 at 18:53
  • 1
    Agent jobs can run PowerShell scripts. Instead of using a cmdexec step or TSQL+xp_cmdshell, write a PowerShell script that does what you want. – David Browne - Microsoft Jan 6 at 20:55
  • Thanks, But i am a newbie found a thread stackoverflow.com/questions/22457249/… but not sure how to modify it as per my requirement, – datalearner Jan 6 at 21:07
  • $OriginalDir = "\\XXXXX\YYYYY_backup\OOOO-NNN-DB01\XY_UUUUU\FULL" $BackupDir = "\\DEVSQLSERVER\XY_UUUUU_FULL" #After the -Name can be changed for whatever you need to backup. $LatestFile = Get-ChildItem -Path $dir - *.bak | Sort-Object LastAccessTime -Descending | Select-Object -First 1 Copy-Item -path "$OriginalDir\$LatestFile" "$BackupDir\$LatestFile" ........................................................................................................................................ this looks idiotic :( – datalearner Jan 6 at 21:34
0

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.

| improve this answer | |
  • You could launch the PowerShell script directly from your SQL Agent job step (instead of calling the file from a cmd step) – Dominique Boucher Jan 7 at 18:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.