May 16, 2019 at 4:59 pm
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.
May 16, 2019 at 10:14 pm
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
Change is inevitable... Change for the better is not.
May 17, 2019 at 10:52 am
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?
May 17, 2019 at 2:20 pm
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
May 20, 2019 at 10:32 am
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.
May 20, 2019 at 10:36 am
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.
May 20, 2019 at 12:03 pm
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
Change is inevitable... Change for the better is not.
May 20, 2019 at 7:15 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
May 21, 2019 at 12:58 pm
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
May 21, 2019 at 2:45 pm
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 39 (of 39 total)
You must be logged in to reply to this topic. Login to reply