SQL Server does not exist or access denied.
Closed this issue · 5 comments
I appear to be coming up against an issue where SqlPipe will timeout and respond with SQL Server does not exist or access denied for instances which I know exist.
D:\> sqlpipe restore MYDB -i sql2008r2 -f MYDB_Packaged_2015_06_02.bak
Failed to open connection to 'lpc:.\sql2008r2': [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. [Microsoft OLE DB Provider for SQL Server]
D:\> sqlcmd -S lpc:.\sql2008r2
1> select @@SERVERNAME, net_transport from sys.dm_exec_connections where session_id = @@SPID;
2> go
net_transport
-------------------- --------------
mydesktop\sql2008r2 Shared memory
(1 rows affected)
1> quit
D:\>whoami /groups | find /i "S-1-5-32-544"
BUILTIN\Administrators Alias S-1-5-32-544 Mandatory group, Enabled by default, Enabled group, Group owner
Possibly of note is that, if I run against a server with only a default SQL instance, instead I get Didn't recognize the SQL Server instance name 'LOCALDB#C53376B1'.
E:\> sqlpipe backup SCRATCH > tmp.bak
IClientVirtualDeviceSet2.CreateEx failed
Didn't recognize the SQL Server instance name 'LOCALDB#C53376B1'.
E:\> sqlcmd -S lpc:.
1> select @@SERVERNAME, net_transport from sys.dm_exec_connections where session_id = @@SPID
2> go
net_transport
--------- --------------
myserver Shared memory
(1 rows affected)
Are you sure that the build you uploaded to Releases is as per the code in the repo, and that you haven't hard-coded LOCALDB#C53376B1 into the serverName parameter?
Definitely looks like the sort of thing I would have been messing about with the last time I touched this! I've just recompiled and it works on a SQLExpress instance I have. Please download the release ZIP and try again.
Backup certainly looks better, now.
D:\> SqlPipe.exe backup OTHERDB -i SQL2012 > tmp.bak
2910720 bytes written
Restore is throwing a new error, so at least we're further along! Haven't had time to look into it in depth, but possibly not creating the virtual device when using restore method?
D:\> sqlpipe restore MYDB -i sql2008r2 -f MYDB_Packaged_2015_06_02.bak
Query failed: 'RESTORE FILELISTONLY FROM VIRTUAL_DEVICE = '{CE7B8CBA-9493-45C1-A60D-6C458972A5B2}''
Cannot open backup device '{CE7B8CBA-9493-45C1-A60D-6C458972A5B2}'. Operating system error 0x80070002(failed to retrieve text for this error. Reason: 15100). [Microsoft OLE DB Provider for SQL Server]
D:\> sqlpipe restore MYDB -i sql2008r2 -f "%CD%\MYDB_Packaged_2015_06_02.bak"
Query failed: 'CREATE DATABASE [MYDB]'
Database 'MYDB' already exists. Choose a different database name. [Microsoft OLE DB Provider for SQL Server]
D:\> sqlpipe restore MYDB2 -i sql2008r2 -f "%CD%\MYDB_Packaged_2015_06_02.bak"
Query failed: 'RESTORE FILELISTONLY FROM VIRTUAL_DEVICE = '{AFA82C4A-EF53-47D9-9387-6DE5D5C30DFD}''
Cannot open backup device '{AFA82C4A-EF53-47D9-9387-6DE5D5C30DFD}'. Operating system error 0x80070002(failed to retrieve text for this error. Reason: 15105). [Microsoft OLE DB Provider for SQL Server]
D:\> sqlpipe restore MYDB3 -i sql2008r2 < "%CD%\MYDB_Packaged_2015_06_02.bak"
Query failed: 'RESTORE FILELISTONLY FROM VIRTUAL_DEVICE = '{F461BC7B-1EBC-4BCE-9CCC-E5BF06A9A5E0}''
Cannot open backup device '{F461BC7B-1EBC-4BCE-9CCC-E5BF06A9A5E0}'. Operating system error 0x80070002(failed to retrieve text for this error. Reason: 15105). [Microsoft OLE DB Provider for SQL Server]
(I can raise as separate issue, if preferred...)
Looking back at this it looks like restore is very half-baked at the moment as it needs to take into account data/log file WITH MOVE stuff and working out what data directory to use (currently hard coded to c:\temp\foo 😱). I'd avoid using restore at the moment and file an issue for it.
I can live without restore for now 😄
I'm not seeing any hard-coded reference to C:\temp\foo in the source, which suggests it has disconnected from what you currently work on/build from. Can you put your current source in a branch?
It's up to date, see: https://github.com/duncansmart/sqlpipe/blob/master/SqlPipe.cpp#L416 - I recall finding the restore work a bit tricky and the backup being good enough for my needs at the time!