Sqlcmd : invalid argument path string Powershell

Question

I've tried many solutions but I can't fix my problem.

I want to execute this PowerShell command :

sqlcmd -S localhost\SQLEXPRESS -U sa -P mypassword 
       -v db="school" bakfile="C:\Users\xal\Documents\backup.bak" -i restore.sql

But I get an error:

Sqlcmd : « :\Users\xal\Documents\backup.bak » : invalid argument.

As you can see the path argument bakfile is cut off. I don't know why.

This is my SQL script that I want to execute with variables :

USE MASTER

RESTORE DATABASE $(db)
FROM DISK = $(bakfile)
WITH REPLACE;

Apart from being spread across two lines without line continuation (which I assume you did for readability), there is no obvious problem with your command line; therefore, more information is needed to diagnose the problem. — mklement0
– mklement0, Commented Jan 18, 2022 at 18:32

Elie · Accepted Answer · 2022-01-20 08:51:38Z

I couldn’t solve the problem, so I created a C# executable that does the same thing.

I found out that we can use SqlCommand to achieve the same thing :

conn = new SqlConnection($"Data Source ={server};Initial Catalog={dbname};" +
    $ "Persist Security Info=True;User ID={id};Password={password}");

conn.Open();

SqlCommand cmd = new SqlCommand($"USE MASTER RESTORE DATABASE {dbname} FROM DISK = '{bakPath}' WITH REPLACE;", conn);
cmd.ExecuteNonQuery();
conn.Close();

Here is the code of my .exe :

static void Main(string[] args) {
  string server;
  string dbname;
  string id;
  string password;
  string bakPath;

  SqlConnection conn;

  if (args.Length > 0) {
    server = args[0];
    dbname = args[1];
    id = args[2];
    password = args[3];
    bakPath = args[4];
    try {
      Console.WriteLine("#--------------------------------------------------#");
      Console.WriteLine("server=" + server);
      Console.WriteLine("dbname=" + dbname);
      Console.WriteLine("id=" + id);
      Console.WriteLine("password=" + password);
      Console.WriteLine("bakfile=" + bakPath);
      Console.WriteLine("#--------------------------------------------------#");
      Console.WriteLine("Processing restoration...");
      conn =
        new SqlConnection($"Data Source ={server};Initial Catalog={dbname};" +
          $ "Persist Security Info=True;User ID={id};Password={password}");

      conn.Open();

      SqlCommand cmd = new SqlCommand($"USE MASTER RESTORE DATABASE {dbname} FROM DISK = '{bakPath}' WITH REPLACE;", conn);
      cmd.ExecuteNonQuery();
      conn.Close();
      Console.ForegroundColor = ConsoleColor.Green;
      Console.WriteLine("Restoration done !");
      Console.ForegroundColor = ConsoleColor.White;
    } catch (Exception ex) {
      Console.ForegroundColor = ConsoleColor.Red;
      Console.WriteLine("Error :" + ex.Message);
      Console.ForegroundColor = ConsoleColor.White;
    }

  } else {
    Console.WriteLine("You need to pass the arguments required")
  }

}

I can now run my .exe with arguments.

FoxheadRaven · Accepted Answer · 2023-08-25 00:58:25Z

0

I ran into this same problem and it took quite some time to get a handle on it. Bottom line, Powershell doesn't like the colon in the bakfile parameter. As you may know, colons have a special meaning to powershell sometimes.

If you run it from the Windows command line, rather than the powershell command line, it should work just fine. To get it to work from the Powershell command line, something like the following seems to work:

#Set the default drive to C:
C:
#Run the command without the drive specified
sqlcmd -S localhost\SQLEXPRESS -U sa -P mypassword -v db=school bakfile=\Users\xal\Documents\backup.bak -i restore.sql

answered Aug 25, 2023 at 0:58

FoxheadRaven

3532 silver badges7 bronze badges

Collectives™ on Stack Overflow

Sqlcmd : invalid argument path string Powershell

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related