SQL Server 2005 – Management Studio Scripting Option and SQLCMD (loving it!)

One of my favorite features of Management Studio is the ability to “script” a step rather than execute it. Within SQL Management Studio, each dialog has 4 different options from which to choose (these are including the typical OK/Cancel) but also add a couple more at the top of the dialog:


At the top of the dialog

  • Schedule – which will create an Agent job.
  • Script – which will send the command(s) to a new Query Window in Management Studio

At the bottom of the dialog (the norm):

  • Run it right now – with the OK button
  • Cancel it – fairly obvious, eh?

The “Script” option has to be one of my favorites. You can learn from this dialog, you can cut/paste and help to build more complex scripts, you can just see what the heck the UI was going to do, etc… and well, that’s what led to SQLCMD. Something I wanted to do (from just a practical perspective) was create a simple/fast way to connect to my existing SQL Server 2000 machine, backup a database and then restore it to my SQL Server 2005 machine. One of the benefits of backup/restore is that SQL Server will upgrade the database on restore (no special settings required – this is default behavior if you restore a SQL Server 2000 backup to SQL Server 2005). Although finding the correct syntax for each step is easy through the UI, I wanted to take the overall process one-step further and create a SQLCMD-specific script to automate it (yes, I kind of like the idea of a command line tools for batch/automation, etc.. and yes, I could have also done this with DTS or the SQL Agent… good idea for another blog entry! Hey guys (and you know who you are!) you should do the comparable DTS/Agent jobs?! I’ll be waiting. J J J).


Anyway, the primary goal is to leverage some of the new capabilities of SQLCMD (which I have to admit – if you’re a command line/automation person – this is going to quickly become your favorite new features).  Here’s a very quick summary of SQLCMD:


  • Replaces OSQL (and then some!)
  • Can set a variety of environment variables
  • Offers a -A parameter for the dedicated admin connection (only one window available for this at a time and the usage here is when you can’t otherwise connect to your server – for whatever reason. To ensure that they can always use this they have kept resources available so that this should always work. I use “should” because well, I rarely use always (or never)… J
  • Adds –v for variable input from the command line but also supports variables set as environment variables (via SET) or variables set within the SQLCMD script (SETVAR).
  • Allows connections to be specified within the script via:
    :CONNECT servername
    NOTE: the semi-colon is required

  • Has a few new error handling options such as
    NICE: this uses RAISERROR and when a severity of 11 (I think) or higher is raised you can define what happens (EXIT, IGNORE or RETRY n) to control script flow and retry behavior (yeah!)

  • And with the exception of a few new (yet VERY cool things like this) SQLCMD is really just TSQL plus some very nice automation stuff. (I can hear TSQL geeks cheering…)

So – I created a script that does this and here’s the play by play of it:


I don’t want to support system databases for backup/restore. You could certainly do a backup and then restore to a new name… but that will have to be in version 2.



IF ‘$(DB)’ IN (‘master’, ‘model’, ‘msdb’, ‘ReportServer’, ‘distribution’, ‘tempdb’)
 RAISERROR (‘System Databases are NOT supported. Script Terminated.’, 16, -1)

Next, I wanted to create a connection to my 2000 server (this could have easily been a parameter!)

:CONNECT servername


 TO DISK = ‘\\servername\sharename\path\$(db).bak’ — NOTICE YOU CAN USE VARIABLES WITHIN STRINGS!

NOTE: Be sure to set a GO after each batch that includes changes in connections, etc. The idea is that much of the SQLCMD code (per batch) is evaluated and executed first. Without the go I would have ended up in the next connection – executing the backup. Here’s a quick example of what I mean:

— Example 1



— In Example 1, both procedures execute on SERVER2




— Example 2


— In Example 2, PROC1 executes on SERVER1 then PROC2 executes on SERVER2 – as desired.

After the database is backed up I connect to the SQL 2005 server and drop the existing database… you may or may not want to do this so blindly.

:CONNECT servername





WAITFOR DELAY ‘$(DELAY)’ — this was just for testing…

Next, I need to determine the SQL 2005’s Data Path as I didn’t want to assume C:\Program Files\…

To do this I’m using an undocumented xp (cause I wanted to see if it still works? it does!)

DECLARE @SQLPath nvarchar(512)
EXECUTE master.dbo.xp_regread ‘HKEY_LOCAL_MACHINE’,
            ‘SQLPath’, @SQLPath OUTPUT

Next, I do some cool stuff to create the correct locations for the new data and log file names. This was the hardest part really… What if the database has multiple files – and the pathes are NOT the same as the server from which the database was backed up. Well, now I need to do a restore with MOVE. Have you seen the syntax for RESTORE with MOVE… it’s not overly pretty. Anyway, I first create a table into which I will store the output of LOAD FILELISTONLY (so I can interrogate the backup device and see the filelist of the database which was backed up):


CREATE TABLE #BackupFileList
( LogicalName sysname NULL
 , PhysicalName sysname NULL
 , [Type] char(1)
 , FileGroupName sysname NULL
 , Size bigint
 , MaxSize bigint
 , FileId smallint
 , CreateLSN numeric(25,0)
 , DropLSN numeric(25,0)
 , UniqueId uniqueidentifier
 , ReadOnlyLSN numeric(25,0)
 , ReadWriteLSN numeric(25,0)
 , BackupSizeInBytes bigint
 , SourceBlockSize bigint
 , FileGroupId  smallint
 , LogGroupGUID uniqueidentifier
 , DifferentialBaseLSN numeric(25,0)
 , DifferentialBaseGUID uniqueidentifier
 , IsReadOnly bit
 , IsPresent bit

Next, I get the info from the backup device (again, notice the parameters being used even in Dynamic String Execution!)

INSERT #BackupFileList

Now I reverse the physical name to find only the “file“ part of the name… reversing it to find the first string up to a backslash (from the end). Then I extract that string and reverse it back… and – at the beginning of the string, I add the local server’s data path.

UPDATE #BackupFileList
 SET PhysicalName = @SQLPath + N’\Data\’ + REVERSE(SUBSTRING(REVERSE(PhysicalName), 1, PATINDEX(‘%\%’, REVERSE(PhysicalName)) -1))

Now I’m ready to walk the filelist and build the RESTORE with MOVE database string. This completes the script!

DECLARE @LogicalName sysname
 , @PhysicalName sysname
 , @ExecStr   nvarchar(max)


 SELECT LogicalName, PhysicalName
 FROM #BackupFileList


OPEN FileListCursor


FETCH NEXT FROM FileListCursor INTO @LogicalName, @PhysicalName


       N’ FROM DISK = ”D:\SQLDemo\$(db).bak”’ +
       N’ WITH MOVE ”’ + @LogicalName + N”’ TO ”’ + @PhysicalName + N””
FETCH NEXT FROM FileListCursor INTO @LogicalName, @PhysicalName

 SELECT @ExecStr = @ExecStr + N’, MOVE ”’ + @LogicalName + ”’ TO ”’ + @PhysicalName + ””
 FETCH NEXT FROM FileListCursor INTO @LogicalName, @PhysicalName


— SELECT @ExecStr

EXEC (@ExecStr)



I set my parameters on the call to the script.

If you’re interested in reading more about somewhat stressful demos for the BI Team read here.

2 thoughts on “SQL Server 2005 – Management Studio Scripting Option and SQLCMD (loving it!)

  1. Kimberly,

    Great stuff!!! I am a big fan of yours!!!

    I know that I can use SQL 2005 client to manage standard SQL 2K servers. As a matter of fact, I will use it to manage all my SQL 2K servers from now on so I can learn and get familiar with SQL 2005 client. One thing I don’t know is if SQL 2005 beta2 client can be used to manage a SQL 2K cluster. I am hesitant to test this, as we only have one production cluster in my company, and I don’t want to cause any trouble on this cluster.

    Do you happen to know if I can use SQL 2005 Beta2 client to connect to and manage a SQL2K cluster?

    Thanks a bunch.

    Haidong "Alex" Ji

  2. So – I’ll start with a standard disclaimer that (in all honesty) I would really avoid this. If this is a critical/production server (and having it on a cluster implies that) then I’d probably avoid doing "testing" of a beta tool against it. In general, I think you’re probably better waiting for beta III. Just IMO.

    BUT – I did ask another reputable source as I just so happened to be in the right place (the SQL building – on campus) at the right time (when a Program Manager for the tools was wandering by…) and well, his generic answer is **NO** as well. Don’t use this to manage a cluster… And – just to add to this… Microsoft will have no sympathy for you ;) ;) ;).

    Now – if you’re wondering why and/or well, will it work? The answer here is that you probably won’t have a lot of problems. The known issues are that you should definitely avoid anything service related. Don’t try to stop/start, etc. as they may try to stop the instance and not the virtual server (remember, it is beta ;). Also, the object explorer "has issues"

    As for other tools – SQLCMD, Profiler, DTA, etc. – yes. These are OK.

    So – these comments are from a reputable source but I’d really hate to see something happen to a production box.


Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.