February 6, 2020 at 5:19 pm
Hello All,
Can someone provide a script or point to an already created script that will copy the latest sql full backup file in the format MyFullBackup_backup_2020_02_06_040730_7697934.bak from one server to another?
I would like to include this in a job step for SQL Agent. I've tried robocopy, powershell, and "entertained" using SSIS, but would like to keep it within a SQL job step. Although the task seems to be pretty simple I'm getting errors and it's not related to permissions as I have admin access to source and target servers.
Many thanks,
David
February 6, 2020 at 5:30 pm
Have you configured the job to run as you?
What errors do you get with a straight copy?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 6, 2020 at 6:30 pm
Have you configured the job to run as you? I haven't configured the job yet because I'm testing in a query window to see if it works. Once it works, I'll configure a step and the job owner will set as 'sa',
What errors do you get with a straight copy? When testing in an SSMS query window I keep getting "The system cannot find the file specified". The native SQL Server 2016 backup file name format is different than earlier versions of SQL from what I've seen. Here's the typical full backup name -- MyFullBackupName_backup_2020_02_06_040730_7697934.bak
February 6, 2020 at 6:38 pm
What command are you running from SSMS to perform a file copy?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 6, 2020 at 7:54 pm
EXEC master.dbo.xp_cmdshell 'copy \\ABC\Database_Backups\Data\MyFullBackup.bak E:\Backups\MyFullBackup.bak ’
The issue is I need to pickup the latest full backup file. Using syntax above will just copy the exact name of the backup file, but the backup names generated (as you already know) are randomly generated from SQL Server to append date, time, etc, with dashes in-between: (i.e. MyFullBackupName_backup_2020_02_06_040730_7697934.bak).
How can I update the syntax above to copy the latest full backup?
February 6, 2020 at 8:36 pm
Put the latest file name in a variable and then use that variable for your copy. Here is some code which does that, for any file in c:\temp:
echo off
cd c:\temp
for /f %%i in ('dir /b/a-d/od/t:c') do set LatestFile=%%i
echo on
echo The most recently created file is %LatestFile%
pause
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 7, 2020 at 2:22 pm
Personally, I would use Powershell to do this, which can be put directly into an Agent Task; thus no need for SSIS:
$SourceDirectory = "C:\temp"
$DestinationDirectory = "C:\YourDirectory\SubDirectory"
$File = Get-ChildItem $SourceDirectory -Include "MyFullBackupName_backup*" | Sort-Object LastWriteTime | Select-Object -Last 1
Copy-Item -Path $File.FullName -Destination $DestinationDirectory
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 7, 2020 at 2:26 pm
Personally, I would use Powershell to do this, which can be put directly into an Agent Task; thus no need for SSIS
No one mentioned SSIS apart from you!
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 7, 2020 at 2:32 pm
Thom A wrote:Personally, I would use Powershell to do this, which can be put directly into an Agent Task; thus no need for SSIS
No one mentioned SSIS apart from you!
The OP did in their opening post:
I've tried robocopy, powershell, and "entertained" using SSIS, but would like to keep it within a SQL job step.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 7, 2020 at 2:34 pm
You can also get the name of the latest backup file by querying the backupset and backupmediafamily tables in msdb. That way, there's no looping, and you'll still pick up the backup file even if it's in a different location (provided you have access to that location, of course).
John
February 7, 2020 at 2:36 pm
The OP did in their opening post.
That's twice in two days you've highlighted my inability to comprehend basic English, you scoundrel! 🙂
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 7, 2020 at 2:38 pm
The OP did in their opening post.
That's twice in two days you've highlighted my inability to comprehend basic English, you scoundrel! 🙂
Don't worry, I'm pretty sure you see my posts in the automated emails first before I then reread them and realise how awful my typing was. I really should start typing slower and read what I typed before I press the Submit button not afterwards. :/
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 7, 2020 at 7:58 pm
With the understanding that I kept it super simple for understanding and haven't parameterized some of the things that could/should be, here's how to pull the full path name for the latest *.BAK file in a given leaf level directory. Based on what you've posted, I think you can probably do the rest from there.
--===== Create the Temp Table that will hold the list of all the *.BAK files from the backup directory.
CREATE TABLE #DirResults
(
RowNum INT IDENTITY(1,1)
,BakFileFullPath VARCHAR(560)
)
;
--===== Define the variables we need for this section.
-- Note that the hardcoded DIR command returns the files by descending creation data and returns the full
-- path for each file.
-- /O-D means Order by creation Date in reverse (-) order.
-- /B mean "bar bones" file names only.
-- /S usually means "resursive through Sub-directories" but is being used in this case to force the full file
-- path to be returned instead of just the file names and extensions.
DECLARE @DirCmd VARCHAR(560) = 'DIR "\\ABC\Database_Backups\Data\*.bak" /O-D /B /S'
,@LatestBakFilePath VARCHAR(560)
;
--===== This gets the list of backup files according to the previously defined command.
INSERT INTO #DirResults WITH (TABLOCK) --For Minimal Logging
(BakFileFullPath)
EXEC xp_CmdShell @DirCmd
;
--===== This returns only the first path for the .BAK files, which will be for the latest Bak file.
-- See the Dir command switch notes in the PRESETS section above for why that is.
SELECT @LatestBakFilePath = BakFileFullPath
FROM #DirResults
WHERE RowNum = 1
;
--===== Display the full path name of the latest backup to copy
SELECT @LatestBakFilePath
;
--Jeff Moden
Change is inevitable... Change for the better is not.
February 7, 2020 at 8:04 pm
p.s. Remember that the COPY command does NOT require you to provide a file name for the target. You only need to provide it with a directory name and it will copy using the same file name as the source.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply