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‘ BackUpFull.bat sqlcmd -S 127.0.0.1 -U sa -P sa -i C:\BackUpFull.sql BackUpFull.sql exec [sp_BackupDatabase] ‘VRTMS‘,‘F‘ BackUpLog.bat sqlcmd -S 127.0.0.1 -U sa -P sa -i C:\BackUpLog.sql BackUpLog.sql exec [sp_BackupDatabase] ‘VRTMS‘,‘L‘ ClearOverTimeBackUpFile.bat forfiles /p C:\BackUpDatabase /m *.full /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 (编辑:ASP站长网) |