随笔 - 435 文章 - 0 评论 - 111 阅读 - 62万

自动备份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

另一个写法

"C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE" -S .\ -E -i C:\Inetpub\CRM\App_Backup\Backup.sql
rar a CRM_Full_%date:~4,2%%date:~7,2%%date:~10,4%.rar CRM_Full_%date:~10,4%%date:~4,2%%date:~7,2%*.BAK
del CRM_Full_%date:~10,4%%date:~4,2%%date:~7,2%*.BAK