CODEWITHSUNDEEP
sql-server
albert sh
albert sh

Reputation: 1193

Error when trying to schedule auto backup in SQL Server Express

I am trying to create scheduled task for auto backup in SQL Server 2019 based on this article :

https://learn.microsoft.com/en-us/troubleshoot/sql/admin/schedule-automate-backup-database

As recommended in order to test if the auto backup is working, I have to run the batch file containing this command:

sqlcmd -S .\EXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='c:\AutoBackup\', @backupType='F'"

I ran it in the command line to test if its working fine but I get the following error:

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : MAX_PROVS: Error Locating Server/Instance Specified [xFFFFFFFF]. . Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login timeout expired. Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : 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 see SQL Server Books Online..

I installed Sql server ODBC driver for another issue that I faced before and also checked that remote connection is allowed as SSMS is working fine.

Any advice is appreciated.

Upvotes: 0

Views: 738

Answers (1)

Martin Cairney
Martin Cairney

Reputation: 1767

Have you double checked that your SQL Express instance is actually named "EXPRESS"?

When you connected using SSMS, did you also used Windows Authentication?

Your command breaks down as follows:

  1. This part defines the server/instance. IN this case on the local machine with a Named Instance of "EXPRESS"

    sqlcmd -S .\EXPRESS

  2. This part states that you want to use Windows Auth with the currently logged in user

    -E

Upvotes: 1

Related Questions