Friday, December 16, 2005

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

 

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.

15 comments:

  1. ASP.NET Podcast Show #30 - Minimizing the ASP.NET ViewState

    Part #2 [Via: Wallym ]

    Complex data binding...

    ReplyDelete
  2. Is there any way I can get a SP to output a string to a dos variable?

    ReplyDelete
  3. I 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.

    ReplyDelete
  4. I 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.

    ReplyDelete
  5. what if you want to pass more than 1 user variable?

    ReplyDelete
  6. I haven't tried it, but my guess is that you simply use more than variable. E.g.



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



    Would you mind trying it and posting back here with your results?

    ReplyDelete
  7. Thanks tonnes.

    I 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)

    ReplyDelete
  8. Hi,



    Iam 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

    ReplyDelete
  9. 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.



    Sean Kerr

    seankerr06@yahoo.co.uk

    ReplyDelete
  10. 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.

    ReplyDelete
  11. Just to add on the question above re. multiple parameters:

    Yes, 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.

    ReplyDelete
  12. Hi,

    How can I pass in a custom var to a custom path:

    Say I have

    $Path = "C:\test\test\"

    because it fails with "invalid argument :"

    ReplyDelete
  13. I was looking for this fr a while now..and couldnt get it nywhere..thanks lot for this tip.....it helped me.

    ReplyDelete
  14. Thanks 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.

    1. 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)

    ReplyDelete