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
        
    :ON ERROR EXIT
    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.



:ON ERROR EXIT
go


 


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


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



:CONNECT servername


 


BACKUP DATABASE $(DB)
 TO DISK = ‘\\servername\sharename\path\$(db).bak’ — NOTICE YOU CAN USE VARIABLES WITHIN STRINGS!
 WITH INIT
PRINT ‘ ‘
go


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


:CONNECT SERVER1
EXEC PROC1



: CONNECT SERVER2
EXEC PROC2


– In Example 1, both procedures execute on SERVER2


 


:CONNECT SERVER1
EXEC PROC1
go


 


– Example 2


: CONNECT SERVER2
EXEC PROC2
go


– 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


SET NOCOUNT ON


 


IF DATABASEPROPERTYEX(‘$(DB)’, ‘COLLATION’) IS NOT NULL
 DROP DATABASE [$(DB)]


 


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’,
            ‘SOFTWARE\Microsoft\MSSQLServer\Setup’,
            ‘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
 EXEC(‘LOAD FILELISTONLY FROM DISK = ”D:\SQLDemo\$(db).bak”’)


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)


 


DECLARE FileListCursor CURSOR FAST_FORWARD FOR
 SELECT LogicalName, PhysicalName
 FROM #BackupFileList


 


OPEN FileListCursor


 


FETCH NEXT FROM FileListCursor INTO @LogicalName, @PhysicalName


 


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


WHILE @@FETCH_STATUS <> -1
BEGIN
 SELECT @ExecStr = @ExecStr + N’, MOVE ”’ + @LogicalName + ”’ TO ”’ + @PhysicalName + ””
 FETCH NEXT FROM FileListCursor INTO @LogicalName, @PhysicalName
END


 


– SELECT @ExecStr


EXEC (@ExecStr)


 


DEALLOCATE FileListCursor
go


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.