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
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.
ASP.NET Podcast Show #30 - Minimizing the ASP.NET ViewState
ReplyDeletePart #2 [Via: Wallym ]
Complex data binding...
Is there any way I can get a SP to output a string to a dos variable?
ReplyDeleteI can't think of an easy one. You could write a very simple C# program to do it, or you could use a more advanced scripting environment, like PowerShell.
ReplyDeleteI used SQLCMD.exe to print the required data to a file. If you want the result only and not table name etc.. Use the Print statement in your SQL file to print only what you want. Then you just have to read the file to get what you want.
ReplyDeletewhat if you want to pass more than 1 user variable?
ReplyDeleteI haven't tried it, but my guess is that you simply use more than variable. E.g.
ReplyDeletesqlcmd -E -i restoredb.sql -v root="%CD%" foo="BAR"
Would you mind trying it and posting back here with your results?
Thanks tonnes.
ReplyDeleteI was searching exactly for this (desperately). You really rock man. In my case i wanted to attach a DB and run a lot of scripts from command prompt, but did not know how to pass current directory to script file.
"%CD%" helped.
sqlcmd -S localhost\SQLExpress -E /i db_attach.sql -v Path="%CD%"
Thanks again!!!
Nishanth
(nishanth_marathe@rediffmail.com)
No charge. :)
ReplyDeleteHi,
ReplyDeleteIam using MSDE2000. iam want to use osql. can u please help how do i restore database using the osql.exe.
Thanks
Kiran
kumarkiranc@yahoo.com
I know it's a while since you asked someone else, but I've tried more than one variable as you suggested and it works just fine. Thanks for the tip off.
ReplyDeleteSean Kerr
seankerr06@yahoo.co.uk
So that's why they prevent you from using both -q and -i with sqlcmd (an old hack that worked with osql to pass vars through a temp table). Thanks! sqlcmd -v is so much nicer.
ReplyDeleteJust to add on the question above re. multiple parameters:
ReplyDeleteYes, you can use multiple parameters with the -v option. Syntax is:
-v var=value[ var=value...]
Check http://msdn.microsoft.com/en-us/library/ms162773.aspx for details.
Hi,
ReplyDeleteHow can I pass in a custom var to a custom path:
Say I have
$Path = "C:\test\test\"
because it fails with "invalid argument :"
I was looking for this fr a while now..and couldnt get it nywhere..thanks lot for this tip.....it helped me.
ReplyDeleteThanks for the post, it was very helpfull for me. Now we have a very dinamic backup solution for all DB's en instances in our whole enviroment with 2 commandlines.
ReplyDelete1. First we reschedule a script that automaticly detects new database servers and/or instances en register them into instances.txt
2. (thanks to Craig) for /f "Tokens=*" %i in (\\server\instances.txt) do (sqlcmd -S %i -i \\bl-ap00006\path\BackupAllDB.sql -v root="F:\backup\%i\" -o F:\LOGS\%i\backuplog.txt)