windows任务计划cmd数据库备份

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‘,‘F‘
go

BackUpLog.bat

sqlcmd -S 127.0.0.1 -U sa -P sa -i C:BackUpLog.sql

BackUpLog.sql

exec [sp_BackupDatabase] ‘VRTMS‘,‘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 NVARCHAR(1000)

DECLARE @dateTime NVARCHAR(20)

SELECT @dateTime = REPLACE(CONVERT(VARCHAR,GETDATE(),111),‘/‘,‘‘) + REPLACE(CONVERT(VARCHAR,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

参考：http://www.learnfuture.com/article/166