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:
- I executed the MaintenceSolution script, specifying Database1 as the database for object creation.
- I created three .cmd files as shown at the bottom.
- 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.
- 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.