sqlcmd.exe -v Rocks - Passing parameters to command line SQL scripts

Useful in batch scenarios...

sqlcmd.exe -v Rocks

I'm the first to admit that I'm no database guru, but I occasionally have need to do something beyond just a simple SELECT, and I can usually manage to fumble my way through it.

The other day, the thing I was fumbling my way through was restoring a database from a backup a client had sent me. While I was doing it, I ran across a neat little option to sqlcmd.exe that I hadn't seen before, but which I definitely want to remember. It's the -v option, and it lets you pass parameters to your SQL script. In my case, I wanted to pass the current directory, so I could restore the database to files in whatever directory I happened to be running. Well, putting this in a .cmd file does the trick:

sqlcmd -E -i restoredb.sql -v root="%CD%"

Then I can use the root variable in my SQL script. All I have to do is reference it with the $(root) syntax, like this:

RESTORE DATABASE MyDB
    FROM DISK = '$(root)\mydb.bak'
    WITH REPLACE,
    MOVE 'mydb_data' to '$(root)\mydb.mdf',
GO

sqlcmd.exe -v Rocks