August 7, 2018 at 9:51 am
I have a scheduled backup script that runs nightly:
@echo off
set DB_NAME=SUPPORT_DB
set BK_FILE=C:\SQLBACKUP\Monday\%DB_NAME%.bak
set DB_HOSTNAME=192.168.50.5 (local-machine)
echo.
echo.
echo Backing up %DB_NAME% to %BK_FILE%...
echo.
echo.
echo.
sqlcmd -U ******** -P *******-S np:\\%DB_HOSTNAME%\pipe\MSSQL$SUPPORTDB\sql\query -d master -Q "BACKUP DATABASE [%DB_NAME%] TO DISK = N'%BK_FILE%' WITH INIT , NOUNLOAD , NAME = N'%DB_NAME% backup', NOSKIP , STATS = 10, NOFORMAT" -o "C:\SQLBackup\Friday\BackupLog.txt"
echo.
echo Done!
echo.
Remote connection is turned on in the DB. Browser service is started name pipes on tcp-ip port default 1433 but i get this errror:
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Named Pipes Provider: Could not open a connection to SQL Server [53]. .
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 13 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..
Does anyone have any suggestions.?
Thanks James
August 7, 2018 at 5:45 pm
jamesstirling01 - Tuesday, August 7, 2018 9:51 AMI have a scheduled backup script that runs nightly:@echo off
set DB_NAME=SUPPORT_DB
set BK_FILE=C:\SQLBACKUP\Monday\%DB_NAME%.bak
set DB_HOSTNAME=192.168.50.5 (local-machine)
echo.
echo.
echo Backing up %DB_NAME% to %BK_FILE%...
echo.
echo.
echo.
sqlcmd -U ******** -P *******-S np:\\%DB_HOSTNAME%\pipe\MSSQL$SUPPORTDB\sql\query -d master -Q "BACKUP DATABASE [%DB_NAME%] TO DISK = N'%BK_FILE%' WITH INIT , NOUNLOAD , NAME = N'%DB_NAME% backup', NOSKIP , STATS = 10, NOFORMAT" -o "C:\SQLBackup\Friday\BackupLog.txt"
echo.
echo Done!
echo.
Remote connection is turned on in the DB. Browser service is started name pipes on tcp-ip port default 1433 but i get this errror:
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Named Pipes Provider: Could not open a connection to SQL Server [53]. .
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 13 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..Does anyone have any suggestions.?
Thanks James
I would guess using the private IP address is the problem. Try using the computer name or fully qualified domain name. Or use 127.0.0.1 for a local connection.
Sue
August 8, 2018 at 2:29 am
Does not work with FQDN 127.0.0.1 address same error.
August 8, 2018 at 6:54 am
What I'd start with for troubleshooting is, verify your SQLCMD connection will work. When you do this, do it in a command prompt window, rather than using a batch file, so you can see what's happening and make changes on the fly.
Start with the basics:sqlcmd -U ******** -P *******
locally on the server. If it works this way (which would be whatever protocol SQLCMD defaults to,) then move on to adding the -S (which is ONLY required when running across a network, locally it'll try to connect to the default instance.)
Once you can get to a point where you can run the backup without using the batch file through SQLCMD, then work on plugging it into a batch file with the variables such as you've already got.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply