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
