SQL Maintenance script not running with SQL Server Express

Question

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.

4. 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.

Answer 1

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