question

rehaan avatar image
rehaan asked

copy backup file

Hi friends, I need to copy 2 backup files for 2 databases from 1 server to another.It can be powershell, xp_cmdshell , but i need to do this in a sql job. Source : Z:\MSSQL\BACKUP\sqlcluster$Azure_AG1\DBName\Full\sqlcluster$AZURE_AG1_DBName_FULL_20180302_210001.bak I will have 3 files in the location, but I need the latest file . Destination : \\prod-sql01\e$\MSSQL\Backup\DBName\Full\ Can anyone give me some sytax please. I tried with powershell but it give me error, looks like it doesnt' like $
restorebackupscopy
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Mrs_Fatherjack avatar image
Mrs_Fatherjack answered
I expect the error you were getting is because you need to escape the $ so that it sees it in the string and not as a variable, you can do this by using ` to escape it.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Awesome Mrs_Fatherjack.. that worked liked a charm. Can i ask you another question please How can i get the latest file, as I have 3 files but I need only 1 file to be copied which is the latest ?
0 Likes 0 ·
Mrs_Fatherjack avatar image
Mrs_Fatherjack answered
I found this on a different site and looks like it would work $dir = "C:\test_code" $latest = Get-ChildItem -Path $dir | Sort-Object LastAccessTime -Descending | Select-Object -First 1 $latest.name
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Mrs_FatherJack I will try and let you know. thanks again
0 Likes 0 ·
How do I combine both now please. I am sorry for many questions, I am learning..
0 Likes 0 ·
Shawn_Melton avatar image
Shawn_Melton answered
Something like this should work for you... $SourcePath = 'Z:\MSSQL\BACKUP\sqlcluster$Azure_AG1\DBName\' $DestPath = '\\prod-sql01\e$\MSSQL\Backup\DBName\Full' $Databases = 'DBName1','DBName2' foreach ($db in $Databases) { $file = Get-ChildItem $SourcePath -Filter "*.bak" | Where-Object {$_.BaseName -match "*$db*"} | Sort-Object LastAccessTime -Descending | Select-Object -First 1 Start-BitsTransfer -Source $file.FullPath -Destination "$DestPath\$($file.BaseName)" }
4 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks Shawn_Melton. You guys are amazing. I will try this and keep you posted
0 Likes 0 ·
so sorry for a dumb question, it throws me an error The string is missing the terminator: ".
0 Likes 0 ·
That'll be the $ again...
0 Likes 0 ·
...or it could be something about the name of your database...
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.