Sql Server (Automatic Database Backup)

Automatic Full Backup on Schedule.

1. Create This stored procedure in Master database.
2. Create one job in sql server agent in steps put this sp as command with exec .
3. make schedule for that.
4. write sp_configure 'xp_cmdshell' ,1 and execute it.
   

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

Create PROCEDURE [dbo].[Temp_Beckup_copy]
as
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----Create Backup of Specified Database and Delete Backup files older than two days
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
begin
Set NoCount On
Declare
@vCount Int,
@vNumDBs Int,
@vDBName Varchar(255),
@vBackupPath Varchar(255),
@vFileName Varchar(100),
@vBackupString Varchar(1000),
@vDeleteString Varchar(1000),
@vNewPath Varchar(1000),
@vErrorString Varchar(1000)
Set @vBackupPath = 'D:\TempBackup\Build' --path where Backup file is going to be Created
--Set @vBackupPath = 'c:' --path where Backup file is going to be Created
Declare
@vDBList Table(
DBID INT NOT NULL IDENTITY(1, 1),
DatabaseName Varchar(256)
)
Insert Into @vDBList
Select Name From master.dbo.SysDatabases where dbid in (10) --List of Backup Database IDS
Set @vNumDBs = @@RowCount
Set @vCount = 1
While @vCount <= @vNumDBs
Begin
Select @vDBName = DatabaseName From @vDBList Where DBID = @vCount
Set @vNewpath = @vBackupPath --+ '\' --+ @vDBName
Set @vDeleteString = 'Del "' + @vNewPath + '\'+ @vDBName + '' + Convert(Varchar(8), DateAdd(Day, -4, GetDate()), 112) + '.bak"' + ' /F /Q'
-- print @vDeleteString
Exec xp_CMDShell @vDeleteString, NO_OUTPUT
Set @vFileName = @vDBName + '' + CONVERT(VARCHAR(8), GETDATE(), 112) + '.bak'
Set @vBackupString = 'BACKUP DATABASE [' + @vDBName + '] TO DISK = ''' + @vNewPath + '\'+@vFileName +
''' WITH NOFORMAT, NOINIT, NAME = N''' + @vDBName + ''', SKIP, REWIND, NOUNLOAD, STATS = 10 , COPY_ONLY '
-- Print @vBackupString
Exec (@vBackupString)
-- If @@Error <> 0
-- Begin
-- Set @vCount = @vNumDBs
-- Set @vErrorString = 'net send * SQL Backup failed on (' + @vDBName + ')! Please view event log!'
-- ---Exec XP_CMDShell @vErrorString
-- End
Set @vCount = @vCount + 1
End
END