Skip to content

Instantly share code, notes, and snippets.

@ashishkudale
Last active June 11, 2018 17:40
Show Gist options
  • Save ashishkudale/6cb09c23fd9a8c582381709c21589126 to your computer and use it in GitHub Desktop.
Save ashishkudale/6cb09c23fd9a8c582381709c21589126 to your computer and use it in GitHub Desktop.
CREATE PROCEDURE [dbo].[auto_backup] as
declare
@DATABASENAME nvarchar (100),
@PATH nvarchar (100),
@zipname nvarchar (100),
@zipname1 nvarchar (100)
SET @DATABASENAME = db_name()
SET @PATH = 'C:\DB_Backup\'+@DATABASENAME
DBCC SHRINKDATABASE (@DATABASENAME)
BACKUP DATABASE @DATABASENAME TO DISK =@PATH WITH NAME = 'BACKUP',STATS = 10
set @zipname = 'Del '+@PATH+'_'+CONVERT(NVARCHAR,(GETDATE()-5),105)+'.BAK'
set @zipname1 = 'Ren '+@PATH+' '+@DATABASENAME+'_'+CONVERT(NVARCHAR,GETDATE(),105)+'.BAK'
exec master.dbo.XP_CMDSHELL @zipname, no_output
exec master.dbo.XP_CMDSHELL @zipname1, no_output
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment