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 At the bottom of the dialog (the norm): 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. <\/EM>J <\/FONT><\/FONT>J J<\/SPAN><\/SPAN><\/FONT><\/SPAN><\/SPAN><\/SPAN><\/SPAN>). <\/FONT><\/FONT><\/SPAN><\/P> <\/FONT><\/FONT><\/SPAN> <\/P> 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<\/B> become your favorite new features). <\/SPAN>Here’s a very quick summary of SQLCMD: So – I created a script that does this and here’s the play by play of it:<\/FONT><\/FONT><\/SPAN><\/P> <\/FONT><\/SPAN> <\/P> 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.<\/FONT><\/SPAN><\/P> :ON ERROR EXIT <\/SPAN> <\/P> IF ‘$(DB)’ IN (‘master’, ‘model’, ‘msdb’, ‘ReportServer’, ‘distribution’, ‘tempdb’) Next, I wanted to create a connection to my 2000 server (this could have easily been a parameter!)<\/FONT><\/FONT><\/SPAN><\/P> :CONNECT servername<\/SPAN><\/P> <\/SPAN> <\/P> BACKUP DATABASE $(DB) <\/SPAN>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:<\/SPAN><\/P> — Example 1<\/SPAN><\/P> :CONNECT SERVER1 — In Example 1, both procedures execute on SERVER2<\/SPAN><\/P> <\/SPAN> <\/P> :CONNECT SERVER1 <\/SPAN> <\/P> — Example 2<\/SPAN><\/P> : CONNECT SERVER2 — In Example 2, PROC1 executes on SERVER1 then PROC2 executes on SERVER2 – as desired.<\/SPAN><\/P><\/BLOCKQUOTE> 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.<\/SPAN><\/P> :CONNECT servername<\/SPAN><\/P> SET NOCOUNT ON<\/SPAN><\/P> <\/SPAN> <\/P> IF DATABASEPROPERTYEX(‘$(DB)’, ‘COLLATION’) IS NOT NULL <\/SPAN> <\/P> WAITFOR DELAY ‘$(DELAY)’ — this was just for testing…<\/SPAN><\/P><\/BLOCKQUOTE> Next, I need to determine the SQL 2005’s Data Path as I didn’t want to assume C:\\Program Files\\…<\/SPAN><\/P> To do this I’m using an undocumented xp (cause I wanted to see if it still works? it does!)<\/SPAN><\/P> DECLARE @SQLPath nvarchar(512) 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):<\/SPAN><\/P> <\/SPAN> <\/P> CREATE TABLE #BackupFileList Next, I get the info from the backup device (again, notice the parameters being used even in Dynamic String Execution!)<\/SPAN><\/P> 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.<\/SPAN><\/P> UPDATE #BackupFileList Now I’m ready to walk the filelist and build the RESTORE with MOVE database string. This completes the script!<\/SPAN><\/P> DECLARE @LogicalName sysname <\/SPAN> <\/P> DECLARE FileListCursor CURSOR FAST_FORWARD FOR <\/SPAN> <\/P> OPEN FileListCursor<\/SPAN><\/P> <\/SPAN> <\/P> FETCH NEXT FROM FileListCursor INTO @LogicalName, @PhysicalName<\/SPAN><\/P> <\/SPAN> <\/P> SELECT @ExecStr = N’RESTORE DATABASE $(DB)’ + WHILE @@FETCH_STATUS <> -1 <\/SPAN> <\/P> — SELECT @ExecStr<\/SPAN><\/P> EXEC (@ExecStr)<\/SPAN><\/P> <\/SPAN> <\/P> DEALLOCATE FileListCursor <\/SPAN>I set my parameters on the call to the script.<\/P>
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
<\/FONT><\/FONT><\/SPAN>:CONNECT servername
NOTE: the semi-colon is required <\/FONT><\/FONT><\/SPAN>
\n
<\/FONT><\/FONT><\/SPAN>: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<\/EM>) to control script flow and retry behavior (yeah!)<\/FONT><\/FONT><\/SPAN>
\n
\n
\n
\n
\n
\n
\n
go<\/SPAN><\/P>
\n
\n
BEGIN
RAISERROR (‘System Databases are NOT supported. Script Terminated.’, 16, -1)
END
go<\/SPAN><\/P><\/BLOCKQUOTE>
\n
\n
\n
\n
\n
TO DISK = ‘\\\\servername\\sharename\\path\\$(db).bak’ — NOTICE YOU CAN USE VARIABLES WITHIN STRINGS!
WITH INIT
PRINT ‘ ‘
go<\/SPAN><\/P><\/BLOCKQUOTE>
\n
\n
\n
\n
EXEC PROC1<\/SPAN><\/P>
\n
: CONNECT SERVER2
EXEC PROC2<\/SPAN><\/P>
\n
\n
\n
EXEC PROC1
go<\/STRONG><\/SPAN><\/P>
\n
\n
\n
EXEC PROC2
go<\/STRONG><\/SPAN><\/P>
\n
\n
\n
\n
\n
\n
\n
DROP DATABASE [$(DB)]<\/SPAN><\/P>
\n
\n
\n
\n
\n
\n
EXECUTE master.dbo.xp_regread ‘HKEY_LOCAL_MACHINE’,
‘SOFTWARE\\Microsoft\\MSSQLServer\\Setup’,
‘SQLPath’, @SQLPath OUTPUT<\/SPAN><\/P><\/BLOCKQUOTE>
\n
\n
\n
\n
( 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
)<\/SPAN><\/P><\/BLOCKQUOTE>
\n
\n
\n
EXEC(‘LOAD FILELISTONLY FROM DISK = ”D:\\SQLDemo\\$(db).bak”’)<\/SPAN><\/P><\/BLOCKQUOTE>
\n
\n
\n
SET PhysicalName = @SQLPath + N’\\Data\\’ + REVERSE(SUBSTRING(REVERSE(PhysicalName), 1, PATINDEX(‘%\\%’, REVERSE(PhysicalName)) -1))<\/SPAN><\/P><\/BLOCKQUOTE>
\n
\n
\n
, @PhysicalName sysname
, @ExecStr nvarchar(max)<\/SPAN><\/P>
\n
\n
SELECT LogicalName, PhysicalName
FROM #BackupFileList<\/SPAN><\/P>
\n
\n
\n
\n
\n
\n
N’ FROM DISK = ”D:\\SQLDemo\\$(db).bak”’ +
N’ WITH MOVE ”’ + @LogicalName + N”’ TO ”’ + @PhysicalName + N””
FETCH NEXT FROM FileListCursor INTO @LogicalName, @PhysicalName<\/SPAN><\/P>
\n
BEGIN
SELECT @ExecStr = @ExecStr + N’, MOVE ”’ + @LogicalName + ”’ TO ”’ + @PhysicalName + ””
FETCH NEXT FROM FileListCursor INTO @LogicalName, @PhysicalName
END<\/SPAN><\/P>
\n
\n
\n
\n
\n
go<\/SPAN><\/P><\/BLOCKQUOTE>
\n