IPAddr

If you want to be able to use the SQL Server SQLCMD.exe (or the older OSQL.exe) command line programs in order to perform SQL Server T-SQL commands from the command line then you need to install them first.

There are three ways to do this.

  1. Install SQL Server itself (either the full version or the SQL Express version)
  2. Install only the SQL Server Management Console
  3. Install only the SQL command Line Tools

When you do a full SQL Server install then you install the SQL Server Manangement Console by default (unless you are doing a SQL server ENGINE only install) and when you install the SQL Server Manangement Console you install the SQL Command Line tools by default.

Recently I had a problem (Matt-PC @ Jayben) where I could NOT install the SQL Server Management Console as it kept telling me that there was another version of Visual Studio 2008 which needed to updated to SP1. I checked and ALL of the installed programs for SQL Server 2008 and Visual C++ 2008 etc were already AT SP1. I tried the Windows updates but Windows Update reported that there were no updates to install. I tried to download and install the SP1 files by themselves and they offered only to uninstall or repair and so I chose the REPAIR option. Howver, none of this made ANY difference - I still could NOT install the SQL Server 2008R2 Management Console

In the end I downloaded the SQL Server Command Lines tools and tried to install them but it said that it needed the SQL Server ODBC driver 11 first so I downloaded and installed this and then SQLCmd.exe and OSQL.exe both worked.

32-bit File NameFile Size
msodbcsql_x86.msi2,891,776
MsSqlCmdLnUtils_x86.msi2,404,352
64-bit File NameFile Size
msodbcsql_x64.msi4,632,576
MsSqlCmdLnUtils_x64.msi2,682,880
All Install FilesFile Size
sql_command_line.exe8,683,520

Once you have installed the command line tools you can use a batch file to automatically do things like create Play Databases. Note that in the sample Make_Play_Data.Bat file below you need to add any lines which are indented onto the end of the previous line. The lines are displayed in this fashion to make this document easier to read and print.


@Echo Off
@Rem===============================================================================
@rem Run Make_Play.Bat FIX to just run the scripts without the backup/restore process
@Rem===============================================================================

@Set Database=GOSoftwareLogs
@Set Server=win-8-64
@Set SQLServer=%SERVER%
@Set BackupDrive=E:
@Set BackupPath=%BackupDrive%\Bakup_SQL
@If "%ComputerName%"=="%Server%" Set MappedPath=%BackupPath%
@If Not "%ComputerName%"=="%Server%" Set MappedPath=W:\Bakup_SQL
@Set DataPath=%BackupDrive%\ABMApps\ABMAccounts\GOSoftware
@Set NewID=791792

@Rem===============================================================================
@Rem Only Need to Change the Lines ABOVE this line
@Rem===============================================================================
@Set UsrNam=abm
@Set Pswd=abm
@Set LiveData=%Database%
@Set PlayData=%LiveData%Play
@Set WindowColour=0x00ffff
@Set TimeOut=3600
@Set SQLPre=-S %SQLServer% -U %UsrNam% -P %Pswd% -t %TimeOut%
@Set Fix=%1
@If "%Fix%"=="fix" Set Fix=FIX
@If "%Fix%"=="fiX" Set Fix=FIX
@If "%Fix%"=="fIx" Set Fix=FIX
@If "%Fix%"=="fIX" Set Fix=FIX
@If "%Fix%"=="Fix" Set Fix=FIX
@If "%Fix%"=="FiX" Set Fix=FIX
@If "%Fix%"=="FIx" Set Fix=FIX
@Echo On
@Echo===============================================================================
@Echo Wait! Backup %LiveData% database on %SQLServer% SQL Server
@Echo===============================================================================
SQLCmd.exe %SQLPre% -Q "Use %LiveData% "
If Not "%Fix%"=="FIX" SQLCmd.exe %SQLPre% -Q 
  "BACKUP DATABASE %LiveData% TO DISK='%BackupPath%\%LiveData%.Bak' WITH NAME='%LiveData%', 
  SKIP, INIT"
@Echo.
@Echo===============================================================================
@Echo Wait! Restoring Backup to Play Database on %SQLServer% SQL Server
@Echo===============================================================================
If Not "%Fix%"=="FIX" SQLCmd.exe %SQLPre% -Q "RESTORE DATABASE %PlayData% 
  FROM DISK='%BackupPath%\%LiveData%.Bak' 
  WITH REPLACE, FILE=1, MOVE '%LiveData%_Data' TO '%BackupPath%\%PlayData%.mdf', 
  MOVE '%LiveData%_Log' TO '%BackupPath%\%PlayData%.ldf' " 
@Echo.
@Echo===============================================================================
@Echo Wait! Updating some data in the Live Database
@Echo===============================================================================
SQLCmd.exe %SQLPre% -d %LiveData% -Q "Update CUSTOMERS 
  Set LocationNo=1 Where IsNull(LocationNo,0)=0; "
SQLCmd.exe %SQLPre% -d %LiveData% -Q "Update PRODUCTS 
  Set MinQuantity=ISNULL((Select SUM(MinQuantity) from PLocDetails 
  Where PLocDetails.ProductID=Products.Uniqueid),0); "
SQLCmd.exe %SQLPre% -d %LiveData% -Q "Update PRODUCTS 
  Set MaxQuantity=ISNULL((Select SUM(MaxQuantity) from PLocDetails 
  Where PLocDetails.ProductID=Products.Uniqueid),0) "
@Echo===============================================================================
@Echo Wait! Changing the company name to Play
@Echo===============================================================================
SQLCmd.exe %SQLPre% -d %LiveData% -Q "Update %PlayData%.dbo.Company   
  Set CompanyName='%LiveData% Play Data', CompanyURL='www.%PlayData%.com.au', 
  CompanyAddress='PO Box 1234'+char(13)+char(10)+'ADELAIDE SA 5000' "
SQLCmd.exe %SQLPre% -d %LiveData% -Q "Update %PlayData%.dbo.Company 
  Set WindowColour=Convert(Int, %WindowColour%)"
SQLCmd.exe %SQLPre% -d %LiveData% -Q "Update %PlayData%.dbo.Company 
  Set CompanyID='%NewID%' "
SQLCmd.exe %SQLPre% -d %LiveData% -Q "Update %PlayData%.dbo.CStores 
  Set CompanyID='%NewID%' "
SQLCmd.exe %SQLPre% -d %LiveData% -Q "Update %PlayData%.dbo.CStores   
  Set StoreTitle=SubString('Play:'+StoreTitle,1,35) Where StoreTitle Not Like 'Play%' "
SQLCmd.exe %SQLPre% -d %LiveData% -Q "Update %PlayData%.dbo.Customers 
  Set CompanyID='%NewID%' "
SQLCmd.exe %SQLPre% -d %LiveData% -Q "Update %PlayData%.dbo.Customers   
  Set CustomerTitle=SubString('Play:'+CustomerTitle,1,35) Where CustomerTitle Not Like 'Play%' "
SQLCmd.exe %SQLPre% -d %LiveData% -Q "Update %PlayData%.dbo.Jobs 
  Set CompanyID='%NewID%' "
SQLCmd.exe %SQLPre% -d %LiveData% -Q "Update %PlayData%.dbo.Jobs 
  Set JobTitle=SubString('Play:'+JobTitle,1,35) Where JobTitle Not Like 'Play%' "
SQLCmd.exe %SQLPre% -d %LiveData% -Q "Update %PlayData%.dbo.Ledger 
  Set CompanyID='%NewID%' "
SQLCmd.exe %SQLPre% -d %LiveData% -Q "Update %PlayData%.dbo.Ledger 
  Set AccountTitle=SubString('Play:'+AccountTitle,1,35) Where AccountTitle Not Like 'Play%' "
SQLCmd.exe %SQLPre% -d %LiveData% -Q "Update %PlayData%.dbo.Products 
  Set CompanyID='%NewID%' "
SQLCmd.exe %SQLPre% -d %LiveData% -Q "Update %PlayData%.dbo.Products 
  Set ProductTitle=SubString('Play:'+ProductTitle,1,50) Where ProductTitle Not Like 'Play%' "
SQLCmd.exe %SQLPre% -d %LiveData% -Q "Update %PlayData%.dbo.Suppliers 
  Set CompanyID='%NewID%' "
SQLCmd.exe %SQLPre% -d %LiveData% -Q "Update %PlayData%.dbo.Suppliers 
  Set SupplierTitle=SubString('Play:'+SupplierTitle,1,35) Where SupplierTitle Not Like 'Play%' "
SQLCmd.exe %SQLPre% -d %LiveData% -Q "Update %PlayData%.dbo.ZCustomTable 
  Set CompanyID='%NewID%' "
@Echo Backup Process Completed! Please check the screen for errors
@%BackupDrive%
@cd %BackupPath%
@If Exist "%MappedPAth%\%LiveData%.Zip" Del "%MappedPAth%\%LiveData%.Zip"
@"?Zip" "%MappedPAth%\%LiveData%.Zip" -m "%MappedPAth%\%LiveData%.Bak"
@Echo Backup and Zipping Processes Completed! Please check the screen for errors
@If Not "%ClientName%"=="" If Not "%ClientName%"=="%ComputerName%" @pause