Converting Substring to DateTime - extra eyes please

  • Brandie Tarvin

    SSC Guru

    Points: 172471

    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;

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Andrey

    Old Hand

    Points: 366

    imho, there are other options to consider rather analysing file names:

    1. get list of 4 last backups from appropriate server (msdb) - they are needed, the rest is not
    2. exec "restore headeronly from disk=..." for each file and analyze create_date

     

  • Brandie Tarvin

    SSC Guru

    Points: 172471

    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.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Brandie Tarvin

    SSC Guru

    Points: 172471

    <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;

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Andrey

    Old Hand

    Points: 366

    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]

  • ScottPletcher

    SSC Guru

    Points: 97941

    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

  • Brandie Tarvin

    SSC Guru

    Points: 172471

    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

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Jeff Moden

    SSC Guru

    Points: 993661

    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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • ZZartin

    SSC-Dedicated

    Points: 30242

    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()}

  • Jeff Moden

    SSC Guru

    Points: 993661

    ZZartin wrote:

    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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • David Burrows

    SSC Guru

    Points: 64396

    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.

  • Jeff Moden

    SSC Guru

    Points: 993661

    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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • David Burrows

    SSC Guru

    Points: 64396

    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 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.

  • ZZartin

    SSC-Dedicated

    Points: 30242

    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 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.

     

  • Jeff Moden

    SSC Guru

    Points: 993661

    David Burrows wrote:

    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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

Viewing 15 posts - 1 through 15 (of 40 total)

You must be logged in to reply to this topic. Login to reply