Some problems with Microsoft SQL Server

Problem 1: changes in sqlcmd

I don't know exactly what happened, but from a certain moment the sql script I used to backup my SQL server DB stopped working. I usually ran it with this syntax:

sqlcmd  -i backup.sql

Where backup.sql contained the code to connect to my server. Suddenly the script started acting up, giving this message:

HResult 0x2, level 16, state 1
Named Pipes Provider: Could not open a connection to SQL Server [2].

After a while, I understood that for some reason, the sqlcmd utility changed its behavior, and forced the user to set the server with the -S parameter. So, in my case, I solved changing my batch script this way

sqlcmd -S lpc:.\SQLEXPRESS -i backup.sql

where lpc:.\SQLEXPRESS is the way to connect to my SQLEXPRESS instance via shared memory.

Problem 2: the wrong restore place

I also have a script that restores my db on another machine. I changed the destination PC, and, guess what, the restore script started acting up too!!! :-( This was the error message:

Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "C:\SomePathTo\myDB.mdf" failed with the operating system
error 3(The system cannot find the path specified.).

As the database was in C:\SomePathTo in the source machine and the target machine had not such directory, this stupid SQL server could not complete the restore operation!
The solution is

1) Run the command

restore filelistonly from DISK= 'C:\someDirt\myDB.bak'

If you run this from sqlcmd, redirect the output to some file with the -o option, as you can't see this large output on one line.

2) Search the results of this command for the colums "LogicalName" and substitute them in this script (here I used the names myDB_dat and myDB_log)

RESTORE DATABASE [myDB]
FROM DISK = 'c:\mfoff\mfoffdb.bak'
with
move 'myDB_dat' TO 'C:\newPath\myDB.mdf',
move 'myDB_log' To 'C:\newPath\myDB_log.ldf'

Lascia un commento

You must be logged in to post a comment.