SQL2005EXPress自动备份

STEP1：在数据库服务器的master表中创建存储过程sp_BackupDatabase

代码如下

 USE [master]

 GO

 /****** 对象:  StoredProcedure [dbo].[sp_BackupDatabase]    脚本日期: 04/09/2019 09:38:30 ******/

 SET ANSI_NULLS ON

 GO

 SET QUOTED_IDENTIFIER ON

 GO

 -- =============================================

 -- Author: RyanDing

 -- Create date: 2010-10-10

 -- Description: 备份数据库

 -- Parameter1: 数据库名

 -- Parameter2: 备份类型 F=全部, D=差异, L=日志

 -- =============================================

 CREATE PROCEDURE [dbo].[sp_BackupDatabase]

        @databaseName sysname, @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 = ''F:\SQLExpressAutoBackup\' + @databaseName + '_Full_' + @dateTime + '.BAK'''

        IF @backupType = 'D'

                SET @sqlCommand = 'BACKUP DATABASE ' + @databaseName +

                ' TO DISK = ''F:\SQLExpressAutoBackup\' + @databaseName + '_Diff_' + @dateTime + '.BAK'' WITH DIFFERENTIAL'

        IF @backupType = 'L'

                SET @sqlCommand = 'BACKUP LOG ' + @databaseName +

                ' TO DISK = ''F:\SQLExpressAutoBackup\' + @databaseName + '_Log_' + @dateTime + '.TRN'''

        EXECUTE sp_executesql @sqlCommand

 END  

STEP2：创建SQL文本，代码如下：保存成backup.sql文件即可

 exec [sp_BackupDatabase] 'WeightCommon','F'

 go  

STEP3：创建批处理文件，以下代码保存成SQLRun.bat即可

@ECHO OFF

SET dbhost=MS-20161018OMFZ\SQLEXPRESS

SET dbuser=sa

SET dbpasswd=sasa

set dbName=master

SET sqlpath=%~dp0

set sqlfile=Backup.sql

osql -S %dbhost% -U %dbuser% -P %dbpasswd% -d %dbName% -i %sqlpath%%sqlfile%

ECHO 完成!

PAUSE

@ECHO Done!

效果如下：