Better way to find a folder and list contents?

[SIMMS7400]

Hi Folks -

I"m thinking about using the following script to locate a specific directory and then return it's contents:

Code: Select all

set Dir=%ProgramFiles%\Microsoft SQL Server
cd %Dir%
for /d  /r "%Dir%" %%a in (*) do if /i "%%~nxa"=="Backup" set "BKUP_PATH=%%a"

DIR "%BKUP_PATH%"

It works, the display is a little cluttered:

Volume in drive C has no label.
Volume Serial Number is DCFC-ECC3

Directory of C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup

08/20/2016 04:11 PM <DIR> .
08/20/2016 04:11 PM <DIR> ..
08/20/2016 04:11 PM 1,659,392 HYPDM_20160820.bak
1 File(s) 1,659,392 bytes
2 Dir(s) 57,911,906,304 bytes free

Is there a way to add some code to just display *.BAK files? I'm open to making my code cleaner as well.

The reason I dont just set a variable for the fully qualified path to the \Backup directory is because the intermediary folders between Program Files & Backup can depending on server/version/installation. Hence why I search for \Backup.

Also, please note the string in red. I'm just recently seeing that. Does my script cause that error?

Thank you, all!

[aGerman]

Is there a way to add some code to just display *.BAK files?

Untested:

Code: Select all

set "Dir=%ProgramFiles%\Microsoft SQL Server"
for /d  /r "%Dir%" %%a in (*) do if /i "%%~nxa"=="Backup" dir /a-d /b "%%a\*.bak"

Also, please note the string in red.

That belongs to the normal output of DIR without the /b option.

Steffen

[foxidrive]

It works, the display is a little cluttered:

Volume in drive C has no label.
Volume Serial Number is DCFC-ECC3

Also, please note the string in red. I'm just recently seeing that. Does my script cause that error?

This change should give you the same display without those lines, in aGerman's code.

Code: Select all

DIR "%%a\*.bak"|findstr /vi "^volume" 

and you might like to test this also:

Code: Select all

DIR "%%a\*.bak"|findstr /vi "^.volume <dir> bytes"

[aGerman]

Another approach

Code: Select all

DIR /a-d "%%a\*.bak"|findstr /vbc:" "

(Exclude directory listing at the outset, exclude lines that begin with a space using findstr.)

One more

%~ftzaI - expands %I to a DIR like output line

Code: Select all

for %%i in ("%%a\*.bak") do echo %%~nxtzai

Steffen

[SIMMS7400]

Good Morning Guys -

Wow - thank you so much!

Steffen's original code worked perfect, but will now test out the additional suggestions! Been learning so much from you guys! Can't thank you enough!!

[SIMMS7400]

Hi Guys -

I have another question. In the event Microsoft SQL Server is not installed in the %PROGRAMFILES% directory, I want to make the search more expansive.

Therefore, I'd like to earch all available directory for the folder name "Microsoft SQL Server".

The below code works fine:

Code: Select all

@ECHO OFF
for  /f "delims=" %%a in ('dir /b /s /a:d "%ProgramFiles%\Microsoft SQL Server\" ^|findstr /e /i "\MSSQL"') do (
  @SET MSSQL_PATH=%%~a
)
SET BAK_FILE_NAME=%SSQLDB%_%date:~-4,4%%date:~-10,2%%date:~-7,2%.BAK
SET MSSQL_BKUP_PATH=%MSSQL_PATH%\Backup\%BAK_FILE_NAME%

But that only gets me so far. I'd like to expand the search to directory's C-G.

I've tried this but it doesn't seem to find it, and outputs errors about the "directory name being too long".

Code: Select all

@ECHO OFF

for %%d in (C D E F G H) do (
     if exist %%d: (
           for  /f "delims=" %%a in ('dir /b /s /a:d "%%d:\" ^|findstr /e /i "Microsoft SQL Server"') do (
           @SET MSSQL_PATH=%%~a
           )
     )
)

To add additional detail, some environments have Microsoft SQL Server installed directly off a drive such as :

G:\Microsoft SQL Server

or in my original post:

%ProgramFiles%\Microsoft SQL Server

Thank you, folks!

[SIMMS7400]

Some additional notes:

This works better, but gives me the wrong path:

Code: Select all

for /d /r "C:\" %%a in (*) do if "%%~nxa"=="Microsoft SQL Server" set "MSSQL_PATH=%%a"

       
ECHO %MSSQL_PATH% >>TEST.txt

C:\Users\srvc-oracle\AppData\Roaming\Microsoft\Microsoft SQL Server

So it appears "Microsoft SQL Server" is too generic of search pattern.

Would there be a way to put some checks on it? For instance, the correct path I need will always have a sub-directory called MSSQLL two directories below "Microsoft SQL Server".

The directory between "Microsoft SQL Server" and "MSSQL" is a directory based off the version SQL Server version:

Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL

which changes with each potential environment, therefore I can't use that string as a search pattern.

Thanks guys!

[aGerman]

(Because searching multiple drives wastes your time ...) Before I answer your question you should check an alternative way.

Open the registry editor (regedit.exe). Navigate to
HKLM\Software\Microsoft\Windows\CurrentVersion\Uninstall
Click each GUID in the left pane and check the "DisplayName" values. If you find the SQL server (and most likely you will) tell us what values and data could we use in order to get the path. (You could export the GUID key into a .reg file and post its content.)

Steffen

[SIMMS7400]

HI Steffen -

Thanks for the response:

Please see below for the rededit export:

Code: Select all

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\Microsoft SQL Server 2008 R2]
"DisplayName"="Microsoft SQL Server 2008 R2 (64-bit)"
"DisplayIcon"="\"c:\\Program Files\\Microsoft SQL Server\\100\\Setup Bootstrap\\SQLServer2008R2\\x64\\SetupARP.exe\""
"HelpLink"="http://go.microsoft.com/fwlink/?LinkId=154582"
"Publisher"="Microsoft Corporation"
"SQLProductFamilyCode"="{20E42995-BBE9-4697-8394-FCDC4338706B}"
"UninstallString"="\"c:\\Program Files\\Microsoft SQL Server\\100\\Setup Bootstrap\\SQLServer2008R2\\x64\\SetupARP.exe\""
"HelpTelephone"=""

My only concern with this method is that the display name will change with each version. Hence why I mentioned earlier that Microsoft SQL Server and MSSQL would be in the same path together, always.

But, curious as to what your solution may be. Thanks!

[aGerman]

That's what I had in mind

Code: Select all

@echo off &setlocal

REM find the registry path that ends with "\Microsoft SQL Server <somewhat that doesn't contain a backslash>"
for /f "delims=" %%i in (
  '2^>nul reg query "HKLM\Software\Microsoft\Windows\CurrentVersion\Uninstall"^|findstr /riec:"\\Microsoft SQL Server [^\\]*"'
) do for /f "tokens=2*" %%j in (
  '2^>nul reg query "%%~i" /v "UninstallString"^|find "REG_"'
) do set "UninstallString=%%~k"

REM See what we have
set UninstallString

REM Split the Uninstall path at the position of "Microsoft SQL Server" and take the first part together with "Microsoft SQL Server"
if defined UninstallString for /f "delims=?" %%i in ("%UninstallString:Microsoft SQL Server=?%") do set "MSSQL_PATH=%%iMicrosoft SQL Server"

REM See what we have
set MSSQL_PATH

pause

Steffen

[SIMMS7400]

Hi Steffen -

Wow, what an elegant solution! I'm confirming it works.

However it appears I wasn't clear enough and I apologize. I need to find the path Microsoft SQL Server is on (we have) but we need to go deeper into the directory, down to the MSSQL directory.

For instance:

Code: Select all

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL

In earlier posts I mentioned the intermediate folder between Microsoft SQL Server & MSSQL changes based on version. Is there a way to go down 2 additional directories after getting MSSQL_PATH?

so, I use the C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL as my "main path" because off that directory there are SQL backup folders, data folders etc that I use for my processes.

This is the code I'm currently using, but obviously the reason for my latest exercise to make it dynamic:

Code: Select all

::-- Get MSSQL Backup Directory --::
::
for  /f "delims=" %%a in ('dir /b /s /a:d "%ProgramFiles%\Microsoft SQL Server\" ^|findstr /e /i "\MSSQL"') do (
  @SET MSSQL_PATH=%%~a
)
SET BAK_FILE_NAME=%SSQLDB%_%date:~-4,4%%date:~-10,2%%date:~-7,2%.BAK
SET MSSQL_BKUP_PATH=%MSSQL_PATH%\Backup\%BAK_FILE_NAME%

I'm sorry if I confused you, Steffen. Thank you so much for your response!

[SIMMS7400]

Hi Steffen -

In looking at the registry again, there is a value called InstanceId with a value "MSSQL10_50.MSSQLSERVER".

Code: Select all

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\{FBD367D1-642F-47CF-B79B-9BE48FB34007}]
"InstanceId"="MSSQL10_50.MSSQLSERVER"
"AuthorizedCDFPrefix"=""
"Comments"=""
"Contact"=""
"DisplayVersion"="10.52.4000.0"
"HelpLink"=hex(2):68,00,74,00,74,00,70,00,3a,00,2f,00,2f,00,67,00,6f,00,2e,00,\
  6d,00,69,00,63,00,72,00,6f,00,73,00,6f,00,66,00,74,00,2e,00,63,00,6f,00,6d,\
  00,2f,00,66,00,77,00,6c,00,69,00,6e,00,6b,00,2f,00,3f,00,4c,00,69,00,6e,00,\
  6b,00,49,00,64,00,3d,00,31,00,35,00,34,00,35,00,38,00,32,00,00,00
"HelpTelephone"=""
"InstallDate"="20150908"
"InstallLocation"=""
"InstallSource"="E:\\x64\\setup\\sql_engine_core_inst_msi\\"
"ModifyPath"=hex(2):4d,00,73,00,69,00,45,00,78,00,65,00,63,00,2e,00,65,00,78,\
  00,65,00,20,00,2f,00,49,00,7b,00,46,00,42,00,44,00,33,00,36,00,37,00,44,00,\
  31,00,2d,00,36,00,34,00,32,00,46,00,2d,00,34,00,37,00,43,00,46,00,2d,00,42,\
  00,37,00,39,00,42,00,2d,00,39,00,42,00,45,00,34,00,38,00,46,00,42,00,33,00,\
  34,00,30,00,30,00,37,00,7d,00,00,00
"NoRepair"=dword:00000001
"Publisher"="Microsoft Corporation"
"Readme"=hex(2):50,00,6c,00,61,00,63,00,65,00,68,00,6f,00,6c,00,64,00,65,00,72,\
  00,20,00,66,00,6f,00,72,00,20,00,41,00,52,00,50,00,20,00,72,00,65,00,61,00,\
  64,00,6d,00,65,00,20,00,69,00,6e,00,20,00,63,00,61,00,73,00,65,00,20,00,6f,\
  00,66,00,20,00,6e,00,6f,00,20,00,55,00,49,00,00,00
"Size"=""
"EstimatedSize"=dword:0002855f
"SystemComponent"=dword:00000001
"UninstallString"=hex(2):4d,00,73,00,69,00,45,00,78,00,65,00,63,00,2e,00,65,00,\
  78,00,65,00,20,00,2f,00,49,00,7b,00,46,00,42,00,44,00,33,00,36,00,37,00,44,\
  00,31,00,2d,00,36,00,34,00,32,00,46,00,2d,00,34,00,37,00,43,00,46,00,2d,00,\
  42,00,37,00,39,00,42,00,2d,00,39,00,42,00,45,00,34,00,38,00,46,00,42,00,33,\
  00,34,00,30,00,30,00,37,00,7d,00,00,00
"URLInfoAbout"=""
"URLUpdateInfo"=""
"VersionMajor"=dword:0000000a
"VersionMinor"=dword:00000034
"WindowsInstaller"=dword:00000001
"Version"=dword:0a340fa0
"Language"=dword:00000409
"DisplayName"="SQL Server 2008 R2 SP2 Database Engine Services"

That value would give us the sub-directory to MSSQL_PATH needed.

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL
Anyway to incorporate that into your code?

[foxidrive]

To go back to the method you tried earlier, this may be specific enough to locate the folder that contains MSSQL

It's not as efficient as querying the registry but in a batch script kind of way it has simplicity going for it and that's all I'm showing. If this is enough to identify the right folder then it should stop with the first drive that contains a folder called MSSQLanything.MSSQLSERVER

The last line simply checks that there is a MSSQL folder inside it.

Code: Select all

@echo off
set "sql="
for %%a in (c d e f g h) do if not defined sql (pushd "%%a:\" & for /d /r %%b in (MSSQL*.MSSQLSERVER) do set "sql=%%b" & popd)
if defined sql echo Found "%sql%"
if defined sql if exist "%sql%\MSSQL\" echo Found "%sql%\MSSQL"

[foxidrive]

Code: Select all

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\{FBD367D1-642F-47CF-B79B-9BE48FB34007}]

"InstallLocation"=""

That value should be what you want. It's odd that there is nothing in it.

[aGerman]

Once you found the "Microsoft SQL Server" folder you could recursively run another loop in order to find the "MSSQL" folder. That would be already quite fast but additionally you could stop searching as soon as you found the folder.

Code: Select all

@echo off &setlocal

REM find the registry path that ends with "\Microsoft SQL Server <somewhat that doesn't contain a backslash>"
for /f "delims=" %%i in (
  '2^>nul reg query "HKLM\Software\Microsoft\Windows\CurrentVersion\Uninstall"^|findstr /riec:"\\Microsoft SQL Server [^\\]*"'
) do for /f "tokens=2*" %%j in (
  '2^>nul reg query "%%~i" /v "UninstallString"^|find "REG_"'
) do set "UninstallString=%%~k"

REM See what we have
set UninstallString

REM Split the Uninstall path at the position of "Microsoft SQL Server" and take the first part together with "Microsoft SQL Server"
if defined UninstallString for /f "delims=?" %%i in ("%UninstallString:Microsoft SQL Server=?%") do set "MSSQL_PATH=%%iMicrosoft SQL Server"

REM See what we have
set MSSQL_PATH

REM Search the MSSQL folder recursively, stop iterating as soon as it was found
for /d /r "%MSSQL_PATH%" %%i in (*) do if /i "%%~nxi"=="MSSQL" (set "MSSQL_PATH=%%i" &goto end_loop)
:end_loop

REM See what we have
set MSSQL_PATH

pause

Steffen