Converting Substring to DateTime - extra eyes please

  • Brandie Tarvin

    SSC Guru

    Points: 172524

    Oh, lovely. The msdb tables are dropping backup data for backups that are still on the NAS. Possibly hitting the max retention because of all the other backups we have going on.

    So, going back to the drawing board on date or possibly the latest suggestion of sorting on the temp table.

    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: 994284

    Are you the one responsible for the backups or is that someone else?  And I'm not sure that max retention (I'm actually thinking about backup expiration dates here and could be wrong on what you meant) has anything to do with this problem.  It sounds more like someone has a backup log cleaner doing things out there.

    --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
    Create a Tally Function (fnTally)

  • Brandie Tarvin

    SSC Guru

    Points: 172524

    The team I'm on is responsible for everything. And I don't see any job or user-created proc that is clearing backup history. So something else must be clearing it.

    EDIT: The default backup media retention (in days) is set as 0. I always though that meant to protect the actual backup file from overwriting. But maybe I'm wrong? Maybe it's all about what's kept in the msdb database tables? Could someone clarify this one?

    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.

  • tripleAxe

    SSCertifiable

    Points: 5521

    If you still want to extract the datetime from the file name you could use something like the code below.

     

     

    DECLARE @File TABLE ([FileName] NVARCHAR(255) NOT NULL PRIMARY KEY);

    INSERT INTO @File ([FileName]) VALUES ('\\My\NAS\Directory\Backups\ABC1234_CO_201905060352.bak');
    INSERT INTO @File ([FileName]) VALUES ('\\My\NAS\Directory\Backups\ABC1234_CO_201905070352.bak');


    SELECT
    [FileName]
    , msdb.[dbo].[agent_datetime](LEFT(RIGHT(REPLACE([FileName],'.bak',''), 12) ,8) , RIGHT(RIGHT(REPLACE([FileName],'.bak',''), 12) ,4)+'00' ) [DateTime]
    FROM @File
  • Brandie Tarvin

    SSC Guru

    Points: 172524

    Thanks, tripleAxe, but your answer makes no sense to me. What is msdb.dbo.agent_datetime? There is no such table in my environment.

    Also, I can't load variables with file paths \ file names. The file names vary and I'm pulling them from a directory.

    EDIT: Nevermind on agent_datetime. I realized after I posted that this was a function reference. Looking at the system functions, I found it. I think I'll play with it.

    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.

  • tripleAxe

    SSCertifiable

    Points: 5521

    The table variable was there just as an example.  I used this to put your sample data in the table to demonstrate it working.  You can replace that part with your original code to load the filenames from the folder.

  • Jeff Moden

    SSC Guru

    Points: 994284

    Brandie Tarvin wrote:

    What is msdb.dbo.agent_datetime? There is no such table in my environment.

    It's the function MS built into the system to convert the integer dates and times that are listed in things like job history into actual DATETIME data types.

    --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
    Create a Tally Function (fnTally)

  • ScottPletcher

    SSC Guru

    Points: 98053

    If possible, put the full details in the "Description" in the backup file.  That way it's always automatically/inherently available with the backup.  The description can be retrieved prior to an actual restore (via RESTORE HEADERONLY).  You can still have a "basic" description of the backup if you want.  You can separate it from the full backup details that just aid with restore by using delimiter char(s) if you prefer (such as preceding/enclosing the restore info in ~ or whatever char).

    As you've seen, don't rely on the msdb tables, because:

    (1) you don't want to be limited to restoring backups from came from that instance only. It's much easier to have your logic just restore all files in a specified folder, regardless of source or datetime of the file.

    (2) you don't know if/when msdb rows will be cleared.

     

    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: 172524

    Back to the PowerShell hijack...

    I think I've figured it out (theoretically, not tested). If calling a PoSH script from SQL, you'll need to use xp_cmdshell or an OS job step (if you don't want to use the PowerShell job step or can't for some reason).

    For T-SQL, if the parameters are changeable, use dynamic SQL. If not, you can just use a straight call.

    xp_cmdshell 'powershell.exe -ExecutionPolicy Unrestricted -file c:\script.ps1 "Param1" "Param2" "Param3"..."ParamN"'

    The script itself needs to use the $args[] array as the first line of the script to parse out the parameters you're passing in. A few links below:

    https://mangolassi.it/topic/3426/understanding-args-in-powershell/6

    https://ss64.com/ps/syntax-args.html

     

     

    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.

  • ZZartin

    SSC-Dedicated

    Points: 30338

    I usually prefer to use named parameters, it might not matter if you're just passing in a list of file names but when you have a mix of parameters it's a lot less confusing.

Viewing 10 posts - 31 through 40 (of 40 total)

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