Share via

Create Database Backup using T-SQL and Delete Older Backup's.

  • Article

USE MASTER
go
DECLARE @DBNAME NVARCHAR(100), @DBFILENAME NVARCHAR(100), @BACKUPPATH NVARCHAR(255), @DBDESC NVARCHAR(255)
SELECT @DBNAME = rtrim([Name]),
 @DBFILENAME = rtrim([Filename])
FROM MASTER.DBO.SYSALTFILES AS files
 inner join
 MASTER.DBO.SYSFILEGROUPS AS groups
 ON
 files.groupID = groups.groupID
WHERE DBID = (SELECT dbid FROM MASTER.DBO.SYSDATABASES
  WHERE [Name] like '%DB NAME%'
 )
DECLARE @C VARCHAR(20)
SELECT @C = CAST(DATEPART(MONTH,GETDATE()) AS VARCHAR(02)) +'_'+ CAST(DATEPART(DD,GETDATE()) AS VARCHAR(2))+'_'+CAST(DATEPART(YY,GETDATE()) AS VARCHAR(4))    
SET @BACKUPPATH = 'File Path\filename.bak'
SELECT @BACKUPPATH
SET @DBDESC = 'Full backup of Database Name on' + CAST(GETDATE() as varchar(11))
BACKUP DATABASE @DBNAME
TO DISK = @BACKUPPATH
WITH DESCRIPTION = @DBDESC

DECLARE @Date DATETIME,@Cast VARCHAR(20),@FilePath VARCHAR(100)
SELECT @Date= (GETDATE())
EXECUTE master.dbo.xp_delete_file 0 , N'\\FilePath\' , N'bak' ,@Date , 0

GO

 

The above command will delete the .bak files which are created before @Date

Comments

  • Anonymous
    February 27, 2014
    Nice.

  • Anonymous
    May 21, 2014
    cool!