Create PROCEDURE [dbo].[usp_BackupDatabase]
@databaseName sysname,@backupPath nvarchar(255), @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 = '''+@backupPath+'\' + @databaseName + '_Full_' + @dateTime + '.BAK''' IF @backupType = 'D'
SET @sqlCommand = 'BACKUP DATABASE [' + @databaseName +
'[ TO DISK = '''+@backupPath+'\'+ @databaseName + '_Diff_' + @dateTime + '.BAK'' WITH DIFFERENTIAL' IF @backupType = 'L'
SET @sqlCommand = 'BACKUP LOG [' + @databaseName +
'[ TO DISK = '''+@backupPath+'\' + @databaseName + '_Log_' + @dateTime + '.TRN''' EXECUTE sp_executesql @sqlCommand
END
Backup.sql
批处理文件
SQLCMD.EXE -S .\SQLEXPRESS -E -i Backup.sql
@echo off
for /f "tokens=2 delims==" %%a in ('wmic path win32_operatingsystem get LocalDateTime /value') do (
set t=%%a
)
set Today=%t:~0,4%%t:~4,2%%t:~6,2%
set DBName=CRM_Test
rar a %DBName%_db_%Today%.rar %DBName%_Full_%Today%*.BAK ping 127.0.0.1 -n 5
del %DBName%_Full_%Today%*.BAK