Forum Discussion
Error Locating Server/Instance Specified - Tried everything I can find
Hello SQL experts,
I am sort of at my wits end at this point and was hoping someone from the community could help me, I have referenced several discussions on this community and still have not found a solution that worked for my situation.
I am trying to set up a automatic backup solution for a SQL Express Server so I can't use the SQL Agent to schedule a backup. Below is a batch script for sqlcmd I am trying to use to create the backup and as soon as I have it working, I plan on scheduling it as a task that will be executed weekly. I have also included the SQL query that works just fine when run from inside SSMS and creates the backup as expected.
Batch file script:
Echo off
REM ================================================================
REM BackupDatabase.bat
REM ================================================================
REM --- Configuration ---
SET SERVER_NAME=.\FTVIEWX64_SRSS
SET USER_NAME=xxxxxx
SET PASSWORD=xxxxxx
SET SQL_SCRIPT=C:\Users\Public\Documents\xxxx_BackupDatabase.sql
REM --- display timestamp ---
echo [%date% %time%] Starting database backup...
REM --- Run SQLCMD ---
sqlcmd -S %SERVER_NAME% -U %USER_NAME% -P %PASSWORD% -i "%SQL_SCRIPT%"
IF %ERRORLEVEL% EQU 0 (
echo [%date% %time%] Backup completed successfully.
) ELSE (
echo [%date% %time%] Backup FAILED! Error code: %ERRORLEVEL%
)
pause
SQL Query being used:
-- BackupDatabase.sql
DECLARE @BackupFileName NVARCHAR(255);
SET @BackupFileName = N'D:\SQLdbBackup\XXXX_'
+ CONVERT(VARCHAR(8), GETDATE(), 112)
+ N'.bak';
BACKUP DATABASE XXXX
TO DISK = @BackupFileName
WITH NOFORMAT, NOINIT,
NAME = N'Full Backup of XXXX',
SKIP, NOREWIND, NOUNLOAD, STATS = 10;
GO
When I run the batch file I get this in the sqlcmd window as an error code:
The necessary services all seem to be running fine as you can see below:
The sqlcmd version is as follows:
SQL server version is as follows:
Connections for the server are configured as follows:
Any and all help would be greatly appreciated so I can resolve the issue and get regular backups scheduled for this server.
Thanks and Kind Regards,
Dan