SQL Server Auto Backup: Create Folder Everyday Using xp_cmdshell and Automatic Backup

xp_cmdshell Server Configuration Option

Introduced in SQL Server 2005, the xp_cmdshell option is a server configuration option that enables system administrators to control whether the xp_cmdshell extended stored procedure can be executed on a system. By default, the xp_cmdshell option is disabled on new installations and can be enabled by using the Policy-Based Management or by running the sp_configure system stored procedure as shown in the following code example:

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO
How to Enable & Disable XP_CMDSHELL using SP_CONFIGUR

Introduction

In this tip we will take a look at the step to follow to enable or disable XP_CMDSHELL using SP_CONFIGURE system stored procedure. In order to use XP_CMDSHELL you need to be a system administrator. Read the following article which explains how to allow non-administrators to use XP_CMDSHELL.

Error Message Received when XP_CMDSHELL is disabled

You would end up getting the below mentioned error message when XP_CMDSHELL is disabled.

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1 SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘xp_cmdshell’ by using sp_configure. For more information about enabling ‘xp_cmdshell’, see “Surface Area Configuration” in SQL Server Books Online.

How to Enable XP_CMDSHELL using SP_CONFIGURE

A database administrator can enable XP_CMDSHELL using SP_CONFIGURE system stored procedure. T o enable XP_CMDSHELL execute the below mentioned script.

Use Master
GO

EXEC master.dbo.sp_configure ‘show advanced options’, 1
RECONFIGURE WITH OVERRIDE
GO

EXEC master.dbo.sp_configure ‘xp_cmdshell’, 1
RECONFIGURE WITH OVERRIDE
GO

Query Output

Configuration option ‘show advanced options’ changed from 0 to 1. Run the RECONFIGURE statement to install. Configuration option ‘xp_cmdshell’ changed from 0 to 1. Run the RECONFIGURE statement to install.

How to Disable XP_CMDSHELL using SP_CONFIGURE

It is always considered as a best practice to disable XP_CMDSHELL when not in use. A database administrator can disable XP_CMDSHELL using SP_CONFIGURE system stored procedure. To disable XP_CMDSHELL execute the below mentioned script.

Use Master
GO

EXEC master.dbo.sp_configure ‘xp_cmdshell’, 0
RECONFIGURE WITH OVERRIDE
GO

EXEC master.dbo.sp_configure ‘show advanced options’, 0
RECONFIGURE WITH OVERRIDE
GO

Query Output

Configuration option ‘xp_cmdshell’ changed from 1 to 0. Run the RECONFIGURE statement to install.
Configuration option ‘show advanced options’ changed from 1 to 0. Run the RECONFIGURE statement to install.
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_DBBackup] Script Date: 11/06/2013 12:46:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER proc [dbo].[sp_DBBackup]
— exec sp_DBBackup
as
declare @DBString varchar(max),
@DelString varchar(400),
@FinalBackup varchar(max),
@dbnAME VARCHAR (max),
@MonthlyFolde varchar(500),
@DailyFolderName varchar(500),
@CreateFolder varchar(2000),
@CopyCommand varchar(500),
@destination varchar(200),
@DatabaseName varchar(50)
declare CursorName cursor for
SELECT name FROM sys.databases WHERE name NOT IN (‘POS’,’KSInventory’,’master’, ‘tempdb’, ‘model’, ‘msdb’,’Accounts_Backup’,’ReportServerTempDB’,’ReportServer’,’distribution’)
open CursorName

Fetch NEXT FROM CursorName into @DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN
set @MonthlyFolde = DATENAME(MONTH,GETDATE()) + DATENAME(YEAR,GETDATE())
set @DailyFolderName = DATENAME(DAY,GETDATE()) + DATENAME(MONTH,GETDATE()) + DATENAME(YEAR,GETDATE())

set @CreateFolder = ‘IF exist D:\DatabaseBackup\’ + @MonthlyFolde + ‘ (mkdir D:\DatabaseBackup\’ + @MonthlyFolde + ‘\’ + @DailyFolderName + ‘) ELSE (MKDIR D:\DatabaseBackup\’ + @MonthlyFolde + ‘\’ + @DailyFolderName + ‘)’
EXEC XP_CMDSHELL @CreateFolder
set @dbnAME = @DatabaseName + ‘_’+ CONVERT(varchar(12),GETDATE(),112) + ‘_’ + SUBSTRING( CONVERT(varchar(12),GETDATE(),108) ,1,2) + SUBSTRING( CONVERT(varchar(12),GETDATE(),108) ,4,2) +
SUBSTRING( CONVERT(varchar(12),GETDATE(),108) ,7,2)
set @DBString = ‘D:\DatabaseBackup\’ + @MonthlyFolde + ‘\’ + @DailyFolderName + ‘\’ + @dbnAME
set @FinalBackup = @DBString + ‘.bak’
set @DelString = ‘DEL ‘ + @FinalBackup

BACKUP DATABASE @DatabaseName TO DISK = @FinalBackup
— COMPAC RAR
DECLARE @SERVER VARCHAR(100), @COMMAND VARCHAR(300), @FILE VARCHAR(100)
SET @SERVER = @@SERVERNAME
SET @COMMAND = ‘”D:\DatabaseBackup\Rar” a D:\DatabaseBackup\’ + @MonthlyFolde + ‘\’ + @DailyFolderName + ‘\’ + @dbnAME + ‘.rar D:\DatabaseBackup\’ + @MonthlyFolde + ‘\’ + @DailyFolderName + ‘\’ + @dbnAME + ‘.bak’

–select @COMMAND
EXEC XP_CMDSHELL @COMMAND
—- EXCLUIR FILE BACKUP
EXEC XP_CMDSHELL @DelString

Fetch NEXT FROM CursorName into @DatabaseName
END
CLOSE CursorName
DEALLOCATE CursorName

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s