August 6, 2021 at 3:24 pm
I need to LOOP thru our Ola Hallengren gen'd backup DIR's to identify the most current FULL backup file and COPY it to a D/R directory (our Server team will then copy the files in the D/R directory t tape and ship offsite). I only wnat the single, most current FULL backup file copied fro each DIR.
We have over 100 SQL Servers.
This DOS cmd will render the list of SQL FULL backup files in a given DIR, sorted by most recently created file:
dir /b /a-d /o-d \\myFileServer\SQL_BKUP\mySQLServerAAA\myDBname111\FULL
dir /b /a-d /o-d \\myFileServer\SQL_BKUP\mySQLServerAAA\myDBname222\FULL
dir /b /a-d /o-d \\myFileServer\SQL_BKUP\mySQLServerBBB\myDBname777\FULL
dir /b /a-d /o-d \\myFileServer\SQL_BKUP\mySQLServerBBB\myDBname888\FULL
Looking to see if anyone can assist me in writing a process to loop thru all DIR's in \\myFileServer\SQL_BKUP above, all mySQLServernames, DBnames, and the FULL directory to identify the latest FULL backup file name. Then XCOPY or ROBOCOPY that file to our target D/R file share.
August 6, 2021 at 3:37 pm
My alter ego just located a sample: Finding newest file in all subdirectories - DosTips.com: www.dostips.com/forum/viewtopic.php?t=3250
You will need nested for loops. The first one to get a list of the directories, then a 2nd one to then list the files within that directory. I wouldn't use PATH as a variable name in a batch file unless you actually want to change that environmental variable.
August 6, 2021 at 6:19 pm
SQL creates a table entry for all db backups.
Look in table "msdb.dbo.backupset" to find the lastest full backup datetime for each db, and link to table "msdb.dbo.backupsetfamily" to find the corresponding physical file name.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 6, 2021 at 6:56 pm
So I think I'd like to use Powershell to accomplish this. This statement works in Powershell: (exactly as in DOS CMD SHELL)
xcopy \\myFileServer\SQL_BKUP\mySQLServerAAA\myDBname111\FULL\*.bak \\myFileServer\SQL_BKUP\DisasterRecovery
With your assistance, I'd like to procure a PS script to LOOP thru my backup DIRS...
\\myFileServer\SQL_BKUP\mySQLServerAAA\myDBname111\FULL
\\myFileServer\SQL_BKUP\mySQLServerAAA\myDBname222\FULL
\\myFileServer\SQL_BKUP\mySQLServerBBB\myDBname777\FULL
\\myFileServer\SQL_BKUP\mySQLServerBBB\myDBname888\FULL
and identify the NEWEST .BAK file in each DIR then XCOPY it to: \\myFileServer\SQL_BKUP\DisasterRecovery
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply