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','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 NVARCHAR(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

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

转载于:https://www.cnblogs.com/lovewl2/p/11457038.html