May 7, 2019 at 12:32 pm
I'm experimenting with a backup / purge backup solution for our non-prod environment. Every 3 hours, a copy-only backup of a prod database gets taken and pushed down to a non-prod NAS share. I'm only supposed to keep 4 backups (12 hours worth), so I wrote a script to purge them. My original code looked at the time stamp and would delete the backups, but then would not delete backups from previous dates because a backup from yesterday at 7:00 a.m. (during a 8:00 a.m.) delete would look like it had only been out 1 hour instead of 25 hours.
So I updated my code to account for the date and now it's looking very clunky. Could y'all take a look at my script below and let me know if there's a better way to calculate the # of hours based on the date/time stamp on the backup file name?
Backup file names: ABC1234_CO_201905060352.bak and ABC1234_CO_201905070352.bak (the first from 5/6 and the second from 5/7).
DECLARE @PurgeFolder SYSNAME = '\\My\NAS\Directory\Backups', @PurgeFile SYSNAME, @DeleteFile VARCHAR(100), @FileCnt INT = 0;
IF (SELECT OBJECT_ID('tempdb..#DirTree')) IS NOT NULL
DROP TABLE #DirTree;
IF (SELECT OBJECT_ID('tempdb..#PurgeBackups')) IS NOT NULL
DROP TABLE #PurgeBackups;
CREATE TABLE #DirTree (Id INT IDENTITY (1,1), SubDirectory NVARCHAR(255), Depth SMALLINT,
FileFlag BIT);
INSERT INTO #DirTree (SubDirectory, Depth, FileFlag)
EXEC xp_dirtree @PurgeFolder, 1, 1;
SELECT * FROM #DirTree;
DELETE FROM #DirTree WHERE Subdirectory NOT LIKE 'ABC1234_CO%';
DELETE FROM #DirTree WHERE RIGHT(Subdirectory,4) <> '.bak'
SELECT * FROM #DirTree;
--This is the code I need to clean up if I can. It feels bulky.
SELECT DATEDIFF(hh,CONVERT(DATE,GETDATE(),120),CONVERT(DATE,SUBSTRING(REPLACE(REPLACE(Subdirectory,'ABC1234_CO_',''),'.bak',''),1,8),120))
+ DATEDIFF(hh,CONVERT(TIME,RIGHT('0' + CONVERT(VARCHAR(4),DATEPART("hh", GETDATE())),2) + ':' + RIGHT('0' + CONVERT(VARCHAR(4),DATEPART("mi", GETDATE())),2),120),
CONVERT(TIME,(SUBSTRING(Subdirectory,20,2) + ':' + SUBSTRING(Subdirectory,22,2) + ':00.0000000'),120)) AS HrsAged, Subdirectory
--INTO #PurgeBackups
FROM #DirTree;
May 7, 2019 at 1:30 pm
imho, there are other options to consider rather analysing file names:
May 7, 2019 at 1:37 pm
My database is too large. Restoring headers takes far too long for this operation. I need it to be quick. The code above completes in less than a minute.
May 7, 2019 at 1:42 pm
<headdesk>. Wow. How did I miss this?
SELECT DATEDIFF(hh,CONVERT(DATE,GETDATE(),120),CONVERT(DATETIME,(RIGHT(LEFT(SubDirectory,LEN(SubDirectory)-8),8))))
+ DATEDIFF(hh,CONVERT(TIME,GETDATE()),CONVERT(TIME,(SUBSTRING(Subdirectory,20,2) + ':' + SUBSTRING(Subdirectory,22,2) + ':00.0000000'),120)) AS HrsAged, Subdirectory
--INTO #PurgeBackups
FROM #DirTree;
And apparently I can't bold the changed parts of it to point it out. That's annoying. Okay, outside the codeblock it is.
SELECT DATEDIFF(hh,CONVERT(DATE,GETDATE(),120),CONVERT(DATETIME,(RIGHT(LEFT(SubDirectory,LEN(SubDirectory)-8),8))))
+ DATEDIFF(hh,CONVERT(TIME,GETDATE()),CONVERT(TIME,(SUBSTRING(Subdirectory,20,2) + ':' + SUBSTRING(Subdirectory,22,2) + ':00.0000000'),120)) AS HrsAged, Subdirectory
--INTO #PurgeBackups
FROM #DirTree;
May 7, 2019 at 2:02 pm
played a bit with a file name variable - see below
declare @file varchar(50) = 'ABC1234_CO_201905060352.bak'
-- my version
select
datediff(hh
,CONVERT(DATE,GETDATE(),120) /*why from the beginning of the day?*/
,convert(datetime
/*date below*/
,left(
replace(stuff(@file,1,len(@file)-charindex('_',reverse(@file))+1,''),'.bak','')
,8)
+' ' /*time below*/
+left(
right(
replace(stuff(@file,1,len(@file)-charindex('_',reverse(@file))+1,''),'.bak','')
,4)
,2)
+':'
+right(
right(
replace(stuff(@file,1,len(@file)-charindex('_',reverse(@file))+1,''),'.bak','')
,4)
,2)
)
) [hours]
,convert(datetime
,left(
replace(stuff(@file,1,len(@file)-charindex('_',reverse(@file))+1,''),'.bak','')
,8)
+' '
+left(
right(
replace(stuff(@file,1,len(@file)-charindex('_',reverse(@file))+1,''),'.bak','')
,4)
,2)
+':'
+right(
right(
replace(stuff(@file,1,len(@file)-charindex('_',reverse(@file))+1,''),'.bak','')
,4)
,2)
) [backup date]
-- Tarvin
SELECT DATEDIFF(hh
,CONVERT(DATE,GETDATE(),120)
,CONVERT(DATETIME
,(RIGHT(LEFT(@file,LEN(@file)-8),8))))
+ DATEDIFF(hh
,CONVERT(TIME,GETDATE())
,CONVERT(TIME,(SUBSTRING(@file,20,2) + ':' + SUBSTRING(@file,22,2) + ':00.0000000'),120)) AS [hours]
,CONVERT(DATETIME
,(RIGHT(LEFT(@file,LEN(@file)-8),8))
)
+ DATEDIFF(hh
,CONVERT(TIME,GETDATE())
,CONVERT(TIME,(SUBSTRING(@file,20,2) + ':' + SUBSTRING(@file,22,2) + ':00.0000000'),120)) AS [backup date]
May 7, 2019 at 2:18 pm
I'd use CROSS APPLYs to assign alias names to make the main SELECT statement and conditions easier to read and maintain. For example:
--...prior code same as before...
DELETE FROM #DirTree WHERE Subdirectory NOT LIKE 'ABC1234_CO%' OR
PATINDEX('%20[0-9][0-9][012][0-9]%' /*YYYYMM*/, subdirectory) = 0 OR
RIGHT(Subdirectory,4) <> '.bak'
SELECT * FROM #DirTree;
--This is the code I need to clean up if I can. It feels bulky.
SELECT Subdirectory, HrsAged, Depth, FileFlag
--INTO #PurgeBackups
FROM #DirTree
CROSS APPLY (
SELECT PATINDEX('%20[0-9][0-9][012][0-9]%', subdirectory) AS yyyymmddhhmm_start
) AS aliases1
CROSS APPLY (
SELECT CAST(STUFF(STUFF(SUBSTRING(subdirectory, yyyymmddhhmm_start, 12),
11, 0, ':'), 9, 0, ' ') AS datetime) AS Subdir_datetime
) AS aliases2
CROSS APPLY (
/*Adjust this calc if/as needed depending on how you want to "round up" hours*/
SELECT DATEDIFF(MINUTE, GETDATE(), Subdir_datetime) / 60 AS HrsAged
) AS aliases3
/*WHERE HrsAged <= ...*/
;
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
May 7, 2019 at 3:31 pm
Thank you all for your responses. I'm choosing not to go with either of the additional solutions because I'm trying to cut down the code I have rather than add more to it. I've finally settled on this for my final SELECT...INTO.
SELECT DATEDIFF(hh,GETDATE(),CONVERT(VARCHAR(10),CONVERT(DATETIME,(RIGHT(LEFT(SubDirectory,LEN(SubDirectory)-8),8))),101)
+ ' ' + LEFT(RIGHT(LEFT(SubDirectory,LEN(SubDirectory)-4),4),2) + ':' + RIGHT(RIGHT(LEFT(SubDirectory,LEN(SubDirectory)-4),4),2)), SubDirectory
FROM #DirTree
May 7, 2019 at 3:56 pm
IIRC, the file paths and the fact that they're "COPY ONLY" are stored in the MSDB database. I think it would be a simple, very quick, and very accurate method of finding the backups you want to delete.
As a bit of a side bar, it would also be a lot simpler and safer if you did your "COPY ONLY" backups to a different directory rather than the same directory as the "real" backups.
You might also want to consider just doing PIT restores from the production backups because that would serve to prove that your backups are actually viable with no extra effort.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 7, 2019 at 4:07 pm
Why are you trying to do this in SQL, this is something that's extremely trivial in powershell, as in 1 line of code.
(Get-ChildItem -Path ("C:\Test\*.txt") | Sort-Object -Property LastWriteTime -Descending | Select-Object -Skip 4) | ForEach-Object{Write-Output $_.Name; $_.Delete()}
May 8, 2019 at 1:00 am
Why are you trying to do this in SQL, this is something that's extremely trivial in powershell, as in 1 line of code. (Get-ChildItem -Path ("C:\Test\*.txt") | Sort-Object -Property LastWriteTime -Descending | Select-Object -Skip 4) | ForEach-Object{Write-Output $_.Name; $_.Delete()}
Since I'm no PoSh ninja, how would you call that from a stored procedure or SQL Server job along with the correct path?
--Jeff Moden
Change is inevitable... Change for the better is not.
May 8, 2019 at 7:47 am
how would you call that from a stored procedure
XP_CMDSHELL
or SQL Server job
SQL Agent has Powershell type
🙂
Far away is close at hand in the images of elsewhere.
Anon.
May 8, 2019 at 12:42 pm
Totally agree with the XP_CmdShell thing. Of course, if you do that, you don't need PowerShell to begin with. Just create the file deletion commands directly from MSDB backup history and Bob's your uncle.
Of course, that's not what I'm looking for from ZZartin. I'm looking for how he'd run his PowerShell suggestion from a proc or a job along with being able to pass a parameter for the path to it.
As for SQL Agent having a "Powershell Type", let's see you pass a path to the job that runs that. 😀
And, of course, if a "fixed" directory is always used for this problem then, yes, an SQL Agent Job could be built to do it.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 8, 2019 at 1:56 pm
Totally agree with the XP_CmdShell thing. Of course, if you do that, you don't need PowerShell to begin with. Just create the file deletion commands directly from MSDB backup history and Bob's your uncle. Of course, that's not what I'm looking for from ZZMartin. I'm looking for how he'd run his PowerShell suggestion from a proc or a job along with being able to pass a parameter for the path to it. As for SQL Agent having a "Powershell Type", let's see you pass a path to the job that runs that. 😀 And, of course, if a "fixed" directory is always used for this problem then, yes, an SQL Agent Job could be built to do it.
That's what I love about you Jeff, I answer a post and you slap me with a pork chop, a cold one at that 🙁
But seriously, I agree with you about xp_cmdshell.
However powershell has the ability to accept command line parameters.
This does not help with Powershell type in the agent though.
Far away is close at hand in the images of elsewhere.
Anon.
May 8, 2019 at 2:51 pm
Totally agree with the XP_CmdShell thing. Of course, if you do that, you don't need PowerShell to begin with. Just create the file deletion commands directly from MSDB backup history and Bob's your uncle. Of course, that's not what I'm looking for from ZZMartin. I'm looking for how he'd run his PowerShell suggestion from a proc or a job along with being able to pass a parameter for the path to it. As for SQL Agent having a "Powershell Type", let's see you pass a path to the job that runs that. 😀 And, of course, if a "fixed" directory is always used for this problem then, yes, an SQL Agent Job could be built to do it.
So yep xp_cmdshell is an option, you could certainly parameterize it, if you wanted your parameter would just replace C:\Test\*.txt in the example i gave. How much validation you do on that would really depend on the use case if you actually wanted to parameterize it. Given that this was specifically for backup jobs and those should be very carefully tuned and controlled hardcoding the search path name seems fine.
Actually the caveat is if you wanted to use xp_cmdshell you would need to wrap it in a powershell -command "&{....}" to actually call it from the command line.
May 8, 2019 at 6:39 pm
Jeff Moden wrote:Totally agree with the XP_CmdShell thing. Of course, if you do that, you don't need PowerShell to begin with. Just create the file deletion commands directly from MSDB backup history and Bob's your uncle. Of course, that's not what I'm looking for from ZZartin. I'm looking for how he'd run his PowerShell suggestion from a proc or a job along with being able to pass a parameter for the path to it. As for SQL Agent having a "Powershell Type", let's see you pass a path to the job that runs that. 😀 And, of course, if a "fixed" directory is always used for this problem then, yes, an SQL Agent Job could be built to do it.
That's what I love about you Jeff, I answer a post and you slap me with a pork chop, a cold one at that 🙁 But seriously, I agree with you about xp_cmdshell. However powershell has the ability to accept command line parameters. This does not help with Powershell type in the agent though.
Oh, man. I'm sorry it came across as a pork chop. Didn't mean to do that at all, David. I was offering alternatives to the PowerShell solution because I didn't see the need for PowerShell here at all.
Equally so, I'm not trying to pork chop ZZartin, either. I just see a whole lot of PowerShell solutions/suggestions and wonder if they're running it from a batch file or what and they almost never say "and here's how to run it in a Proc or from and SQL Agent Job", which is what looked like the method really needing to be done here.
I also ask people how they would schedule their PowerShell batch and I either end up with crickets or someone suggests (definitely not my first choice) using the Windows Scheduler.
So, no slight to either of you and I sure didn't mean for it to make it look like I was trying to turn you good folks into pork chop landing pads.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 39 total)
You must be logged in to reply to this topic. Login to reply