SQL Server exposes command shell in the form of extended stored procedures.

There are quite a few of them that are pretty useful, especially if your only

way of accessing the server is through SQL Server Enterprise Manager and/or

Query Analyzer. For example, xp_fixeddrives can give you free disk information,

xp_sendmail allows you to send emails in SQL script. To see how to use them

together,

see my previous article on monitoring disk space using SQL scripts. Among

many extended stored procedures, the most versatile is xp_cmdshell, because it

provides you with a DOS interface that you can use to execute many useful

commands.

For example, often times we want to find out when the server was last

rebooted. This is very easy to do with xp_cmdshell. All you have to do is run

xp_cmdshell 'net statistics server'. In the result set, you will see "Statistics

since ....". That was when your server was last rebooted.

To use xp_cmdshell effectively, it helps if you know DOS command well. For

example, if you use a command like DEL that needs interactive user input, it is

going to be hard to automate tasks. To find out all the options or switches that

are associated with a command, under DOS, type YourCommand /?, you will see all

the options that are available to you. Please note that some command's options

are case-sensitive.

When writing these series of articles, I strive to use examples that are easy

to follow, touch key aspects of the technology, and provide a template for you

to work on. This one is no exception. The following are 2 examples that will

help you get started with xp_cmdshell.

Example 1: Stored Procedure to delete files in a folder

When your only way of managing SQL Server is through Enterprise Manager or Query

Analyzer, you will have to do a lot of tasks through command shell. If you find

yourself routinely conduct some activities using xp_cmdshell, it is a good idea

to wrap those routines into a stored procedure. This example takes an input

parameter for directory name and delete all files within that directory. The

directory name can be UNC path. I put some comments within this procedure so it

should be easy to follow.

CREATE proc usp_DeleteFileInFolder @FolderName varchar(150) as
--Created by Haidong Ji
SET NOCOUNT ON
declare @DOSCommand varchar(150)
--Check whether the user supply \ in the directory name
if not (right(@FolderName, 1) = '\')
set @FolderName = @FolderName + '\'
--Delete all files within this folder. Note: del *.* only deletes files,
--not folders. Q is the quite switch so DOS will not ask for confirmation
set @DOSCommand = 'del /Q ' + '"' + @FolderName + '*.*' + '"'
print @DOSCommand
exec master..xp_cmdshell @DOSCommand
GO

Example 2: Stored procedure to automatically generate database schema

Often times, there is a need to keep a backup copy of database schema

information, so that an empty database can be easily created without data. You

can generate database script using Enterprise Manager. You can also use SQL-DMO

for this purpose.(See

Andy's intro to DMO). However, SQL Server also provides a nifty utility

called scptxfr that you can use to generate database script. This example

demonstrate how you can use xp_cmdshell to call an external program to finish

some task. This stored procedure will iterate all user databases on the server

and generate database scripts for them. The output will be stored in the folder

name passed in as an input parameter. Each database will have a separate file.

The file name will be in the format of ServerName_DBName_YYYYMMDD.SQL. You can

easily schedule this procedure as a job so database scripts will be backup

routinely.

CREATE PROCEDURE usp_GenerateDBScripts @FolderName varchar(150) AS
-- Author: Haidong Ji
-- Purpose: This stored procedure will generate DB scripts
-- for each user databases on this server.
-- Note that DB property info will not be generated, such as file location,
-- recovery model, and other db options like Auto Update Statistics,
-- Auto Shrink, etc.
--Note also that you can use UNC path for output file.
-- ***Important*** Depending upon your installation of SQL Server,
-- you might need to change the directory name 
-- to get to the scptxfr.exe utility
set nocount on
declare @DBName varchar(50)
Declare @CMD Varchar(1000)
--Check whether the user supply \ in the directory name
if not (right(@FolderName, 1) = '\')
set @FolderName = @FolderName + '\'
declare UserDB_Cursor cursor fast_forward for
select name from master.dbo.sysdatabases where name not in ('master', 'tempdb', 'model', 'pubs', 'northwind', 'msdb')
open UserDB_Cursor
fetch next from UserDB_Cursor into @DBName
WHILE @@FETCH_STATUS = 0
Begin
Select @CMD='Exec master..xp_cmdshell ''"c:\Program Files\Microsoft SQL Server\MSSQL\upgrade\scptxfr.exe" /I /s '
+@@SERVERNAME+' /d ' + @DBName + ' /f ' + @FolderName +@@Servername+'_' + @DBName + '_'+
Replace(convert(Varchar(8),getdate(),112)+'_'+convert(Varchar(8),getdate(),114),':','')+
'.sql /r /O /Y /G '''
print @CMD
Exec (@CMD)
fetch next from UserDB_Cursor into @DBName
end
close UserDB_Cursor
deallocate UserDB_Cursor
GO

Conclusion

Continuing the discussion of SQL Server admin automation techniques, (see my

previous articles on

VBScript and

WMI), I talk about using command shell to automate various tasks in SQL

Server in this article. Hopefully it can provide enough information to get you

started.

In addition,

Brian Knight's article on xp_cmdshell is also very helpful.