自动备份sqlexpress 数据库脚本

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