 |
|
| SQL Server Tips: |
|
|
|
DATABASE MANAGEMENT AND ADMINISTRATION
Five sqlcmd features to automate SQL Server database tasks
Roman Rehak, Contributor
02.18.2008
Rating: -4.44- (out of 5)
|
Sqlcmd is one of the command line utilities included with SQL Server 2005. It was designed for executing SQL scripts and replaces the osql utility that came with previous versions of SQL Server. In addition to executing SQL code, it also executes so-called "sqlcmd extensions." These extensions provide a lot of added functionality that go beyond plain SQL, making sqlcmd a useful tool for automating SQL Server database administration and maintenance via scripting. This article shows you the basics of using sqlcmd and suggests some practical ways to use it for SQL Server administration and maintenance.
Start sqlcmd from the command prompt window in the interactive mode. While you could execute SQL queries that you type in the window and see the results, you can pretty much achieve the same thing much more easily by running queries in SQL Server Management Studio in sqlcmd mode. The real power of sqlcmd lies in its ability to easily automate the execution of saved SQL scripts with options for controlling where and how the script is executed -- you control that via passed-in parameters or sqlcmd variables.
Here is a list of the most useful features in sqlcmd:
- Executing script files. You can execute script files in sqlcmd in two ways. If you are in DOS, you can invoke sqlcmd and use the –i option to pass in the name of the file you want to execute:
Note: Double quotes are needed only if the path to the file contains any spaces, but I tend to use them all the time for the sake of consistency.
The second option is to invoke another SQL file from a file that is being executed. This allows you to create two or more layers of script files, where at the top you control the flow – which files to execute and which parameters to pass in when they are executed. The following script will execute MySql.sql from inside of a SQL file running in sqlcmd mode:
- Using and populating variables. This is, in my opinion, the most useful feature addition if you compare sqlcmd in SQL Server 2005 with osql in previous SQL Server versions. You can declare variables inside of sqlcmd scripts using the :setvar directive and then use them later in the code by enclosing the variable name with the $() placeholder. Sqlcmd will replace the placeholder with the value of the referenced variable:
One very useful behavior is, if you declare and populate a variable, the execution context includes any script files invoked by the file where the variable is declared. This allows you to pass variables from the parent script file to child script files. In the following example, the hello variable will be available for use inside of the invoked MySql2.sql file:
If calling sqlcmd from DOS, you can pass in variables and populate them using the –v command line option:
- Connecting to multiple servers. When starting sqlcmd, you have to connect to a defined server to ...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
execute your code. But then in SQL, you can change the connection to a different server and any subsequent scripts will be executed on that server. You connect to another server by using the :connect directive:
- Improved error handling. Sqlcmd gives you many options for handling situations where something doesn't go the way you expected it to. When you combine this feature with the ability to use sqlcmd variables, you get a lot of flexibility for protecting yourself from the unexpected. For example, let's say you want to make sure that a certain script file only executes on a particular database server and you want to enforce it in code. This example uses the ":on error exit" directive to specify that the script execution should stop if the SQL Server instance name is not RREHAK:
Any scripts that follow the code above would not get executed.
- Backing up and restoring a database. Now I'll show you one example of a useful sqlcmd script. I frequently create scripts that back up a database on one server and restore it on another server. With sqlcmd you can achieve the whole thing in a single script file because you can connect to both source and destination server. The following script backs up the Northwind database on one server and restores it on another server:
-- connect to the source server
:CONNECT rrehak\sql2000
BACKUP DATABASE Northwind
TO DISK = 'C:\Temp\Northwind.bak'
WITH INIT
GO
-- connect to the destination server
:CONNECT rrehak
-- if the database exists, disconnect any
possible connections
IF EXISTS (SELECT * FROM
master.dbo.sysdatabases WHERE name =
'Northwind')
ALTER DATABASE Northwind
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
RESTORE DATABASE [Northwind]
FROM DISK = N'C:\Temp\Northwind.bak'
WITH FILE = 1,
MOVE N'Northwind' TO N'C:\SQL Server
Databases\Northwind.mdf',
MOVE N'Northwind_log' TO N'C:\SQL Server
Databases\Northwind_1.ldf',
NOUNLOAD, STATS = 10
GO
Here's one more improvement to the script above, which demonstrates how to execute operating system commands from sqlcmd. The demo script restored the database from the location where the source database was backed up. Let's say you want to copy a backup file to a folder on the destination server because you'll need to restore the file repeatedly and want to avoid restoring over the network. Sqlcmd can execute DOS commands using the :!! directive. In this case, place the following script after the backup section and before the restore section to execute the DOS copy command for creating a copy of the backup file:
:setvar CopyCommand "copy
\ \sqlserver1\c$\Temp\Northwind.bak
\ \sqlserver2\c$\sql"
:!! $(CopyCommand)
All of these features make sqlcmd the tool of choice for automating SQL Server administration and maintenance tasks. For example, you can create a "worker" script file that contains a common set of SQL scripts that need to execute in multiple databases, possibly on multiple servers as well. Inside of that script you would use sqlcmd variables.
This article provided an overview and a few examples of using the sqlcmd utility. You can read more about sqlcmd in Microsoft Books Online and start building your own collection of sqlcmd script files that automate your repetitive tasks and make you a more productive DBA.
ABOUT THE AUTHOR:
[IMAGE]Roman Rehak is a senior database architect at MyWebLink.com in Colchester, Vt. He specializes in SQL Server development, database performance tuning, ADO.NET and writing database tools. He regularly contributes SQL Server articles to Visual Studio Magazine, SQL Server Magazine and other technical publications and presents at user groups and conferences in the U.S. and Canada. He is an active member and volunteer for the Professional Association for SQL Server. Roman also serves as Tech Chair for the SQL Server track at the annual DevTeach conferences in Canada and is president of the Vermont SQL Server User Group.
|
|
|
|
|
|
|
|
|
| TechTarget provides technology professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective purchase decisions and managing their organizations' technology projects - with its network of . |
|
| |
All Rights Reserved, , TechTarget |
|
|
|
|
|
