Hello and thank you for making such a well-designed script available to the public. I have client with some heavy db fragmentation and performance issues. I would like to use your scripts to help them, but I’m having trouble. Unfortunately they only have SQL Server Express, so I don’t have the Server Agent.

I understood via the FAQ that I must execute the store commands manually and/or schedule them with cmd files. I’m trying to do that, but I’m missing something. Here is what I have done:

  1. I executed the MaintenceSolution script, specifying Database1 as the database for object creation.
  2. I created three .cmd files as shown at the bottom.
  3. When trying to run the cmd files, I receive this error:

C:\SQL_Scripts>sqlcmd -E -S .\SQLEXPRESS -d DATABASE1 -Q "EXECUTE dbo.DatabaseIntegrityCheck @Databases = 'User_Databases" HResult 0xFFFFFFFF, Level 16, State 1 SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information seeSQL Server Books Online.. Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.

  1. I have verified that remote connections are enabled, TCP/IP is enabled and that a port is open in the firewall.

I’m sure this is a typo or misunderstanding on my side, but I’m stuck. My three command statements are below…

sqlcmd -E -S .\SQLEXPRESS -d DATABASE1 -Q "EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASES', @Directory = N'D:\Data\Backups\Database1', @BackupType = 'FULL'" -b -o C:\Log\DatabaseBackup.txt

sqlcmd -E -S .\SQLEXPRESS -d DATABASE1 -Q "EXECUTE dbo.DatabaseIntegrityCheck @Databases = 'User_Databases'"

sqlcmd -E -S .\SQLEXPRESS -d DATABASE1 -Q "EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES'

I appreciate any help you can provide.

  • 1
    Your error is very common, please describe the result of all of the troubleshooting items listed here: support.microsoft.com/en-us/help/4009936/… – Aaron Bertrand Jun 18 '18 at 13:04
  • What version of SQL Server are you using? The SQL Server Native Client 10.0 error message suggests you might be using SQL Server 2008 (R2), but you might be just relying on outdated SQL DAO/ADO/ODBC drivers. Please add the relevant version tag to your question by editing it. Thanks. – John aka hot2use Jun 18 '18 at 13:49
  • 1
    @TomV does the newest Express have Agent? I thought it was omitted from Express. – MguerraTorres Jun 18 '18 at 13:59
  • Eh I meant browser – Tom V - try topanswers.xyz Jun 18 '18 at 17:30
  • Is it possible that the server has no named instance, just the default one? In that case you might want to try sqlcmd -E -S . ... (just a dot for the server name (if on the same machine), no instance name, no backslash). – sticky bit Jun 19 '18 at 1:09

I recently set up some backups for a couple of different Express instances on our network and the command statements that you are using are formatted correctly so you are good there.

The error seems to be specific to the fact that it is unable to find the Server\Instance. @stickybit's answer is most likely the culprit. I would double check that you are using the correct instance name. If it is the default instance then you will just use -S . if it is a named instance using the .\SQLEXPRESS should work provided the name of the instance is actually SQLEXPRESS and provided this is running on the same machine that the instance is installed on. If you are running this command file from another computer then you will need something like -S \\SERVERNAME\INSTANCENAME

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.