There are many options available to programmer for taking Database Backup. In this post we will take Database Backup in sql server using stored procedure among available possible ways, , we will create a simple store procedure that will take full database backup. Creating a store procedure will help us to reuse same n future.

We will use xp_cmdshell command which is introduced in sql server 2005 for server configuration option. In the new installation the xp_cmdshell is disabled by default. To enable xp_cmdshell run below script in sql server.

-- 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

if  xp_cmdshell  is not enabled and we will try to execute below store procedure it will give below error.

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.

Now use below store procedure which will accept 2 input parameters(Data base Name,Path) for taking full database backup.

CREATE PROC [dbo].[DAILYBACKUP]
(
	@DatabaseName varchar(50),
	@BackUpPath varchar(500)
)
AS
BEGIN

	declare @strDate varchar(20)
	declare @strPathDirectory varchar(200)
	declare @strBackupToRAR varchar(2000)

	set @strDate = CONVERT(VARCHAR(10), GETDATE(), 110)

	set @BackUpPath = @BackUpPath + '' + @strDate
	set @strPathDirectory = 'MD ' + @BackUpPath

	EXEC xp_cmdshell @strPathDirectory

	set @BackUpPath = @BackUpPath + '' + @DatabaseName
	set @strPathDirectory = 'MD ' + @BackUpPath

	EXEC xp_cmdshell @strPathDirectory

	set @BackUpPath = @BackUpPath + '' + @DatabaseName + '_'+ @strDate

	exec('BACKUP DATABASE '+ @DatabaseName +' TO  DISK = ''' + @BackUpPath + '.bak''' )

END

Now execute above store procedure to take back up.

exec [DAILYBACKUP] ‘AdventureWorks2008R2′,’D:BackUp’

Friends, I have explained regarding Database Backup in sql server using stored procedure. Please give your valuable feedback.