BackUpDiff.bat
sqlcmd -S 127.0.0.1 -U sa -P sa -i C:\BackUpDiff.sql
BackUpDiff.sql
exec [sp_BackupDatabase]’VRTMS’,’C:\BackUpDatabase’,’D’
go
BackUpFull.bat
sqlcmd -S 127.0.0.1 -U sa -P sa -i C:\BackUpFull.sql
BackUpFull.sql
exec [sp_BackupDatabase]’VRTMS’,’C:\BackUpDatabase’,’F’
go
BackUpLog.bat
sqlcmd -S 127.0.0.1 -U sa -P sa -i C:\BackUpLog.sql
BackUpLog.sql
exec [sp_BackupDatabase]’VRTMS’,’C:\BackUpDatabase’,’L’
go
ClearOverTimeBackUpFile.bat
forfiles /p C:\ BackUpDatabase /m *.full /d -30 /c “cmd /c del /f @file”
forfiles /p C:\BackUpDatabase /m *.diff /d -30 /c “cmd /c del /f @file”
forfiles /p C:\BackUpDatabase /m *.trn /d -30 /c “cmd /c del /f @file”
sp_BackupDatabase
create PROCEDURE [dbo].[sp_BackupDatabase] @databaseName sysname,@dirPath NVARCHAR(200), @backupType CHAR(1)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sqlCommand NVARC HAR(1000)
DECLARE @dateTime NVARCHAR(20)
SELECT @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),111),’/’,”) + REPLACE(CONVERT(VARCHAR, GETDATE(),108),’:’,”)
IF @backupType =’F’
SET @sqlCommand =’BACKUP DATABASE ‘+ @databaseName + ‘TO DISK =”’+ @dirPath +’\’ + @databaseName +’_BackUp_’ + @dateTime +’.full”’
IF @backupType =’D’
SET @sqlCommand =’BACKUP DATABASE ‘+ @databaseName +’ TO DISK =”’+ @dirPath +’\’ + @databaseName +’_BackUp_’ + @dateTime +’.diff” WITH DIFFERENTIAL’
IF @backupType =’L’
SET @sqlCommand =’BACKUP LOG ‘+ @databaseName +’ TO DISK =”’+ @dirPath +’\’ + @databaseName +’_BackUp_’ + @dateTime +’.trn”’
EXECUTE sp_executesql @sqlCommand
END
GO
Reference: http://www.learnfuture.com/article/166