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
Nifty, eh? I know I'm far from the first one to "discover" this, but maybe it'll help someone who hasn't seen it before.
Posted
Dec 16 2005, 03:15 PM
by
craig-andera