I know what your thinking. Why in the world would I want to manipulate the complex workings of MS SQL Server using the archaic command line? Before today I would have been thinking that right along with you.
Issue
Like all good development shops we try to follow the best practice of using a TST/DEV database along side the Production database. All scripts, changes, new releases, etc… get executed against the TST/DEV database and after passing use cases and regression testing are then released to production. The problem is, the TST/DEV database gets badly out of date. Another issue is that as the client is doing data cleanup and removing/deleting data from production the copy of that data lives forever in TST/DEV. Not only is this data using space it doesn’t need to, but the bigger issue here is the legal ramifications where a client thinks they have destroyed something only to find out it has been preserved in a test database!
Solution
Here is where SQLCMD comes in. I wrote a .bat file that calls the SQLCMD interface and then passes SQLCMD a .sql script file that contained everything I needed to do. Keep reading for an explanation of each component of this method.
Part I – .bat File
Create a new text file and save it as YourFileName.bat. The following code should go into the .bat file:
C: sqlcmd.exe -S ServerName\InstanceName,PORT -E -h -1 -i "FullPath\ScriptFileName.sql" -o "%USERPROFILE%\Desktop\OutputResults.txt"
Line 1 – I am running this script from a different drive so C: is just getting me back to the root of the C drive.
Line 2 – First we call sqlcmd.exe we then pass it some variables.
-S ServerName\InstanceName,PORT = -S is the parameter for Server. Since I use a named instance on a non-default port I have to specify those items as well. If you are using a default instance on the default port you can simply do -S ServerName.
-E = Tells SQLCMD to connect to the SQL Server using trusted Windows authentication.
-h -1 = disables header rows. This will make more sense when we get to the section on the output results file. Basically SQLCMD will return your query results similar to how SSMS does. It will list a header row followed by a row with _____ to act as a separator and then your data. I didn’t want the header rows so I turned them off. If you do want header rows simply leave off the -h -1 parameters.
-i "FullPath\ScriptFileName.sql" = -i is the parameter for passing an input file. This is your .sql file that has all your execution code in it. I try to stay in the habit of not using spaces in folder or file names. However, I wrap my paths/filenames in ” ” so that if there is a space the CMD prompt won’t bomb out.
-o "%USERPROFILE%\Desktop\OutputResults.txt" = -o is the parameter for sending the output to a file rather than the CMD prompt screen. Again I try to not use spaces but I wrap my paths/filenames in ” ” just in case. Notice %USERPROFILE%, that is a windows shortcut for the current logged in user. I do this so that whoever is running the .bat file the results will get put on their desktop. If I were to hard code my username in the path it would go to my desktop (and actually other users won’t have write permission to my desktop so that would create problems). You could of course drop this output file to the C drive or some shared folder outside of the user profile.
For a full list of available parameters, you can always run sqlcmd.exe /?
Part II – .sql File
Create a new text document and save it as YourFileName.sql. The following code should go into the .sql file:
PRINT 'Start Script' SET NOCOUNT ON SELECT CONVERT(VARCHAR(12), GETDATE(), 107)+' @ '+CONVERT(VARCHAR(8), GETDATE(), 108) SET NOCOUNT OFF PRINT '***************************************************' /* Truncate all tables being refreshed. */ PRINT 'Truncate tbl_AuditEdits' TRUNCATE TABLE [NetTraxNet_Client_Tst].[dbo].[tbl_AuditEdits] GO PRINT 'tbl_AuditEdits has been truncated 'PRINT '--------------------------------------------------' /*Repopulate all tables being refreshed. */ PRINT 'Repopulate tbl_AuditEdits' SET IDENTITY_INSERT [NetTraxNet_Client_Tst].[dbo].[tbl_AuditEdits] ON INSERT INTO [NetTraxNet_Client_Tst].[dbo].[tbl_AuditEdits] (AuditEdit_Seq, Audit_Seq, AuditEdit_ID, AuditEdit_User, AuditEdit_TS, AuditEdit_Source, TableName, FieldName, OriginalValue, UpdatedValue, EditReason) SELECT AuditEdit_Seq, Audit_Seq, AuditEdit_ID, AuditEdit_User, AuditEdit_TS, AuditEdit_Source, TableName, FieldName, OriginalValue, UpdatedValue, EditReason FROM [LinkedServerName].[NetTraxNet_Client_Prd].[dbo].[tbl_AuditEdits] GO SET IDENTITY_INSERT [NetTraxNet_Client_Tst].[dbo].[tbl_AuditEdits] OFF GO PRINT '' PRINT '***************************************************' SET NOCOUNT ON SELECT CONVERT(VARCHAR(12), GETDATE(), 107)+' @ '+CONVERT(VARCHAR(8), GETDATE(), 108) SET NOCOUNT OFF PRINT 'End Script'
Anything following PRINT will simply be text that is output to our output file.
Set NOCOUNT ON / NOCOUNT OFF – SQLCMD will give you the results total just like SSMS does. So after running a select it will tell you (X rows affected). In lines 3 & 28 I am querying the database for the current date/time and writing it to my output file. I log the start time as well as the finish time so that I can keep an eye on how long my script is taking to execute. Since the output returning form the query is just the date/time I don’t need it to also tell me that 1 row affected message. However, later on in the script where I am inserting records into my truncated tables, I do want to know the number of rows affected. That is why I turn it off and then back on here and at the end rather than just disabling it for the entire script.
Starting on line 7 I basically go through and truncate all the tables that I want to keep in sync with the production database. What I am ultimately going to do is loop through and truncate several tables in the TST database. The next section is then going to do inserts and repopulate the data from the production database.
Line 14 starts the repopulation process. The example here is a table that has an identity column so I had to enable identity insert as well as list out each column individually. If your table(s) that you are working with don’t have identity column enabled you can do the easier INSERT INTO TableName SELECT * FROM SourceTableName.
I am only showing you one truncate and one insert but my actual script truncates several (11) tables and then repopulates the same several (11) tables.
This method assumes that you are either selecting data from the same server or if you need to select data from another server (as I am) you need to first create a Linked Server. See my other blog posting for instructions on doing that.
Part III – Output (Results) File
The last piece to the process is the results of your executed script. Here is a sample of my output file as it is generated from the above .sql script.
Start Script May 26, 2011 @ 13:14:01 *************************************************** Truncate tbl_AuditEdits tbl_AuditEdits has been truncated *************************************************** Repopulate tbl_AuditEdits (128534 rows affected) *************************************************** May 26, 2011 @ 13:19:48 End Script
So, as I mentioned in Part II, since I turned off the (x rows affected) message using SET NOCOUNT ON function I don’t get that message below either of my date/time outputs. However, since I turn the message back on using the SET NOCOUNT OFF after my date is selected I do see the (x rows affected) when inserting back into my table.
Final Thoughts
I hope you find this helpful. I know that it sounds crazy to use the CMD prompt to manipulate MS SQL Server but I think you can see the advantage here. Now I can double-click an icon on my desktop to run through all of this without even having to open SSMS! I use this a lot for scheduling a task using the Windows task scheduler. I can automatically have tasks fire in the middle of the night that cleanup data, or import data, or anything you can imagine!