Solved

Error when scripting SQL '08 R2 backups when multiple instances co-exist.

Posted on 2013-06-10
2
427 Views
Last Modified: 2013-06-11
I have a client with two SQL instances running on one server.  INSTANCE1 is v10.50.4000 and INSTANCE2 is v10.50.2500.  

I've created backup scripts by right-clicking on each database, selecting Properties, and then configuring Full backups to be stored to a folder on the local hard drive.  I've then created a CMD file with the syntax: "sqlcmd -i d:\sql_backups\scripts\DATABASENAME-monday.sql" and have the CMD file run via a Scheduled Task. (The version of sqlcmd.exe is 10.50.4000.)

When I run a backup, I get the following error:

HResult 0x2, Level 16, State1
Named Pipes Provider: Could not open a connection to SQL Server [2].
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.  
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.

(If I run any of the backups from within the SQL Management Studio by opening the .SQL file and running it, they complete successfully.)

I found a thread that suggested going into SQL Server Configuration Manager -> SQL Server Network Configuration -> Protocols for INSTANCE -> Named Pipes Properties and removing the instance name from the Pipe Name.  For example, I changed the Pipe Name from "\\.\pipe\MSSQL$INSTANCE1\sql\query" to "\\.\pipe\sql\query".

I made that change to the Pipe Name for INSTANCE1 and then restarted it.  Sure enough, the scripted backup completed successfully.  I then changed the Pipe Name for INSTANCE2 and attempted to restart it.  It would not restart successfully.  I stopped INSTANCE1 and was then able to start INSTANCE2, but when I tried to start INSTANCE1, it also would not start.  Apparently I can't remove the instance name from the Pipe Name for both instances.

Presumably if I just had one instance on this server, it wouldn't be an issue, but the client has two separate software packages and each one is configured to connect to its own specific instance.  I'd rather not have to reconfigure all of the client machines after merging the two instances.  

So what do I have to change to allow my scripted backups of both instances to be able to run successfully?  Thanks in advance!
0
Comment
Question by:SINC_dmack
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 10

Accepted Solution

by:
Matt Bowler earned 500 total points
ID: 39236696
You could add the -S <server name>[\<instance_name>] switch to your SQLCMD call.
0
 

Author Comment

by:SINC_dmack
ID: 39238301
That worked flawlessly.  Thanks for the quick response!

(This seems like it should be a pretty obvious answer to anyone who has even a modest experience with SQL, so I can't imagine why I wasn't able to come up with anything helpful while Googling.  Regardless, I appreciate it.)
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Create your own, high-performance VM backup appliance by installing NAKIVO Backup & Replication directly onto a Synology NAS!
This tutorial will walk an individual through the process of installing the necessary services and then configuring a Windows Server 2012 system as an iSCSI target. To install the necessary roles, go to Server Manager, and select Add Roles and Featu…
This tutorial will walk an individual through setting the global and backup job media overwrite and protection periods in Backup Exec 2012. Log onto the Backup Exec Central Administration Server. Examine the services. If all or most of them are stop…

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question