Backup using osql and named pipes - Syntax help

MikeeMiracle
MikeeMiracle used Ask the Experts™
on
Hi

I am trying to backup a SQL database using the osql command but I am having problems with the syntax.  On a normal setup I use:

osql -E -Q " BACKUP DATABASE BESMgmt TO DISK = 'D:\backup\besDBbackup.bak' WITH INIT"

On the current server however the port has been moved off the default 1433.  I have created a named pipe called "backupsql" which has the details of the server and port to use, I am unsure how I intergrate this named pipe into my backup.  I can use "osql -E -S backupsql" and get a connection with the 1> prompt but the following does not work and just lists the default osql /? output.

osql -E -S backupsql " BACKUP DATABASE BESMgmt TO DISK = 'D:\backup\besDBbackup.bak' WITH INIT"

Can anyone offer any assistance? Im sure it's one of those you either know or you don't scenario's.

Many Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Use sqlcmd instead of osql.
osql is outdated in SQL Server 2005.

Hope this helps:

http://msdn.microsoft.com/en-us/library/ms162773(SQL.90).aspx

Author

Commented:
Thanks but that does not really answer my question.  sqlcmd uses the same syntax's but that does not work either if I use that instead of osql.  If your going to suggest using something different can you please specify the syntax as im a stranger to sql so need extra help.

Question remains, can I do a backup like this using the alias name which is currently what is casuing me trouble with osql.

Thanks
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Hope this helps:

replace all those values like user_name, password, etc., before trying to run it out
sqlcmd -U user_name -P password -S server_name -d db_name -E -Q "BACKUP DATABASE BESMgmt TO DISK = 'D:\backup\besDBbackup.bak' WITH INIT" 

Open in new window

Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

Commented:
Thanks, i have been suplimenting -u -p with -S  apart from taht I kept the same syntax.

C:\Users\Administrator>sqlcmd -E -S backupsql -d vim_vcdb -E -Q "BACKUP DATABASE
 vim_vcbd TO DISK = 'D:\backup\besDBbackup.bak' WITH INIT"

Comes out with

Msg 911, Level 16, State 11, Server servername\SQLEXP_VIM, Line 1
Could not locate entry in sysdatabases for database 'vim_vcbd'. No entry found w
ith that name. Make sure that the name is entered correctly.
Msg 3013, Level 16, State 1, Server servername\SQLEXP_VIM, Line 1
BACKUP DATABASE is terminating abnormally.

Regarding the error "Could not locate entry in sysdatabases for database 'vim_vcbd'.  I fired up SQL server management studio express and I see that the vin_vcdb database is at "root" level or the same level as the "System Databases" folder which this command is trying to look inside to find this database.

Is the backup still possible with this configuration?

Thanks
Most Valuable Expert 2014

Commented:
The -E is not Named Pipes it security authorization. The -E is indicating that you want to use username of whoever is running the sqlcmd.exe from the command prompt. So if you are logged on the server as MyDomain\Admin, the SQL Server has the login MyDomain\Admin in the SQL Server --> Security --> Logins with appropriate permissions to complete the task.

SQLCMD.EXE -E -S MyServerName -Q "BACKUP DATABASE BESMgmt TO DISK = 'D:\backup\besDBbackup.bak' WITH INIT"

Also the drive you are backing up to has to be local to the server, and the path must exist already.
Most Valuable Expert 2014

Commented:
As an aside -- I highly recommend you disable Named Pipes. They are nothing but problems. The protocol was designed for very small, fast, stable networks. However I have never heard of them working well over the long term. Even M$ has backed away from it to a large extent.

Author

Commented:
The backup path exists but

SQLCMD.EXE -E -S MyServerName

Will not connect as it's trying to connect using the default 1433 port which then fails.  Using the named pipe is the only way I could see to get the connection on the right port.

Is there any other way I could specify a port?
Most Valuable Expert 2014
Commented:
Try it as:
-----------------------------------
SQLCMD.EXE -E -S MyServerName,1522
-----------------------------------

Just to note -- as long as you have the SQL Server Browser Service running, you can use 1433/1434 for all instances of SQL running on a single server. The only reason to change it is to satisfy specific apps, or to be an odd port for security.


How to: Connect to the Database Engine Using sqlcmd.exe
http://msdn.microsoft.com/en-us/library/ms188247%28SQL.90%29.aspx

SQL Server Browser Service
http://msdn.microsoft.com/en-us/library/ms181087%28SQL.90%29.aspx

Author

Commented:
Great thanks.   I have spent 4 days trying to find a method of specifying the port number.
Most Valuable Expert 2014

Commented:
Glad to be of assistance. May all your days get brighter and brighter.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial