May 142019
 

You’re running WSUS (Windows Server Update Services) on Windows Server 2019 Server Core, and you want to run the WSUS Re-Index or WSUS Cleanup script, but you can’t because you cannot install the SQL Management Studio on Windows Server Core.

Well, there’s a way around this. To run SQL scripts on the WID (Windows Internal Database) on Windows Server Core, we’ll need to install “sqlcmd” (info here).

Now normally with Microsoft SQL, you’d simply connect remotely using the SQL Management Studio, and you can if you’re using fully blown Microsoft SQL Server with your WSUS implementation, however most of us aren’t. In most small deployments, WSUS is configured using WID (Windows Internal Database) which is essentially Microsoft SQL Express.

Microsoft SQL Express doesn’t support remote named pipe connections, and there’s no easy way to configure TCP connections with the registry editor, so the easiest way to accomplish executing SQL scripts is to install and use the “sqlcmd”.

Instructions

Install the SQLCMD command utility

  1. First we need to identify the version of SQL express that’s running WID on the server running Windows Server Core 2019.
  2. Open “notepad” and open the following file which containts the WID log.
    C:\Windows\WID\Log\error
  3. At the beginning of the log file, you’ll note the Microsoft SQL version that’s running. In my case it’s “Microsoft SQL Server 2014 (SP2-GDR)” specifically 12.0.5214.6 as shown below.
    2019-05-14 10:52:47.79 Server      Microsoft SQL Server 2014 (SP2-GDR) (KB4057120) - 12.0.5214.6 (X64) 
    	Jan  9 2018 15:03:12 
    	Copyright (c) Microsoft Corporation
    	Windows Internal Database (64-bit) on Windows NT 6.3  (Build 17763: ) (Hypervisor)
  4. The “sqlcmd” is part of the Microsoft SQL Server Feature Pack, so a quick search of “SQL Server 2014 SP2 Feature Pack” returned the following URL:
    https://www.microsoft.com/en-us/download/details.aspx?id=53164
  5. When you click download, you’ll notice multiple files. Choose the ”
    ENU\x64\MsSqlCmdLnUtils.msi” file to download.
  6. Copy this file over to your server running Windows Server Core.
  7. Execute and run the installer. Follow the prompts.
  8. You’ll notice the installer will error and require “Microsoft ODBC Driver 11 for SQL Server”. A quick search finds this download:
    https://www.microsoft.com/en-ca/download/details.aspx?id=36434
  9. Download the above file, install the “Microsoft ODBC Driver 11 for SQL Server”.
  10. Re-start the “MsSqlCmdLnUtils.msi” installer, and it should now complete.
  11. You have now installed the SQLcmd utility.

Run the WSUS Re-Index Script

  1. Download the WSUS Database Re-Index script from:
    https://gallery.technet.microsoft.com/scriptcenter/6f8cde49-5c52-4abd-9820-f1d270ddea61
  2. Copy the script to the server.
  3. Run the following command from the command prompt:
    sqlcmd -S np:\\.\pipe\Microsoft##WID\tsql\query –i C:\Folder\WsusDBMaintenance.sql

You’ve officially installed the sqlcmd and ran the WSUS Re-Index script on Windows Server Core. Congratulations!

  8 Responses to “Run WSUS cleanup and index script on Windows Server Core without SQL Management Studio”

  1. Thanks for the article.
    Isn’t it supposed to be error.LOG ?

    type %SystemRoot%\WID\Log\error.log | find /i “microsoft sql server”
    seems to do the trick

  2. …also, the server/named pipe has changed from
    np:\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query
    to
    np:\\.\pipe\MICROSOFT##WID\tsql\query

  3. Hi magnus,

    In my case it was just “error”. However I’m wondering if this could be different on other versions of Windows.

    Thanks for pointing that out though! 🙂 I’m sure it’ll help numerous other users!

    Cheers,
    Stephen

  4. Hi magnus,

    Could you post what Windows Server your running for users? My steps were on Windows Server 2019 Server Core. I’m assuming you’re running a different version?

    Thanks,
    Stephen

  5. Windows Server 2019 core, here as well

    C:\>ver

    Microsoft Windows [Version 10.0.17763.379]

    C:\>systeminfo | findstr /B /C:”OS Name” /C:”OS Version”
    OS Name: Microsoft Windows Server 2019 Standard Evaluation
    OS Version: 10.0.17763 N/A Build 17763

  6. Hi Stephen,

    Thank you for this wonderful guide.

    Hi Magnus,

    You’re a genius.
    Wasted DAYS on google until I came across your gem of a reply. It’s tsql now.
    Thank you heaps.

  7. The call to sqlcmd needs to have a -I (capital i) parameter, which tells SQL to run the script with QUOTED_IDENTIFIERS

  8. Great post again, Stephen. For your information: TechNet Gallery is closed, but some of the scripts can be found at GitHub. For example:

    https://gist.github.com/emnavarro02/0ffd6481ce7c9b207f7762732fd73aa8

    (source: https://docs.microsoft.com/en-us/answers/questions/250683/re-index-the-wsus-30-database.html)

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)