在SQL Server Express版本中没有代理功能如何自动备份数据库

因为是免费的且单个数据库可以支持到10GB，对于一般企业完全足够了，也就将就使用了，备份将分为两步：

1、创建备份脚本

2、创建系统的计划任务进行每天的备份

 

详细做法如下：

1、创建备份脚本

         打开SSMS/用其它编辑器也可以，新建查询，输入如下代码，这里考虑到要备份所有数据库，所以使用master数据库来创建执行脚本，亦可使用超简单的对于单独数据库操作，只需要几个命令即可，后文会给出示例，以下为完整备份所有数据库的语句。

USE [master] 
GO 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO  
 
CREATE PROCEDURE [dbo].[sp_BackupDatabases]  
            @databaseName sysname = null,   --数据库名称            
            @backupLocation nvarchar(200)   --备份位置
AS  
      SET NOCOUNT ON;            
      DECLARE @DBs TABLE
      (
            ID int IDENTITY PRIMARY KEY,
            DBNAME nvarchar(500)
      )           
      --用一个表来存放找到的所有数据库名
      INSERT INTO @DBs (DBNAME)
      SELECT Name FROM master.sys.databases
      where state=0 AND name=@DatabaseName OR @DatabaseName IS NULL ORDER BY Name
           
      -- 过滤掉不需要备份的数据库(自定不需要备份的数据库名称填入括号内)
      DELETE @DBs where DBNAME IN ('master','model','msdb','tempdb')         
            
      DECLARE @BackupFile varchar(100)    --备份文件名称
      DECLARE @DBNAME varchar(300)        --数据库名称
      DECLARE @sqlCommand NVARCHAR(1000)  --命令名称
      DECLARE @dateTime NVARCHAR(20)      --日期时间
      DECLARE @Loop int                   --循环备份所找到的数据库                       
            
      SELECT @Loop = min(ID) FROM @DBs   --从第1个开始备份
 
      WHILE @Loop IS NOT NULL   --有数据库要备份时进行操作
      BEGIN 
      --获得得数据库ID
      SET @DBNAME = '['+(SELECT DBNAME FROM @DBs WHERE ID = @Loop)+']'
 
      --设置当前日期和时间，格式为年月日
      SET @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),112),'/','')
 
      --设置备份文件名称，例如: D:\SQLBak\dbname_FULL_20171121.BAK
      SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_FULL_'+ @dateTime+ '.BAK'      
      
      --按类型执行备份操作 
      SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NOSKIP, NOFORMAT'
     
      --开始执行备份命令
      EXEC(@sqlCommand)
 
      --备份下一个数据库
      SELECT @Loop = min(ID) FROM @DBs where ID>@Loop
 
END

 

在SSMS中执行完上面的代码后，将会在master数据库中产一个存储过程，如下图示：

 

 

现立建立几个数据库来测试，环境中我建立了test、test01、test02三个数据库，

 

 

在SSMS中执行刚才建立的存储过程，

EXEC master.dbo.sp_BackupDatabases @backupLocation='D:\SQLBackups\'

@后面是指定备份文件存放路径，执行结束后可以在D:\SQLBackups文件夹中看到已经备份的文件，没有错误产生，对比上图中的数据库可以看出这就是想要的结果，如下图：

 

创建命令备份，这里将离开SSMS环境来操作。

sqlcmd -S . -E -Q "EXEC master.dbo.sp_BackupDatabases @backupLocation='D:\SQLBackups\'"

对上面的命令做下注释

Sqlcmd 是SQL自带的命令工具，

-S(大写)是指定服务器，在本机的话就用点(.)表示，两边都有空格

-E(大写)表明是信任的连接

-Q(大写)命令行查询并且完成后退出

打开CMD窗口，执行上面的语句输出是正常的，说明备份成功了，如下图：

 

 在C盘bak目录中建立了.bat批处理文件，将上面的语句放入文件中，将如下图：

 

 

2、创建系统的计划任务进行每天的备份

文件建立好后打开控制面板-管理工具-任务计划，建立一个任务即可，如下图。

 

 

附注：简单的语句，此种情况需要每个数据库建立一个存储过程来操作，适合只有一个数据库或是少量的，将上面的存储过程内容改成如下的即可，其它一样操作。

USE test
CREATE PROCEDURE [dbo].[sp_BackupDatabases]
AS
      --先设置一个变量存放保存位置不然会出错，因为要按日期来备份，不用的可以去掉这部分，则只需要一条语句即可。
      DECLARE @SavePath NVARCHAR(300)
      SET @SavePath='D:\SqlFullBak\test_BAK'+ CONVERT(VARCHAR, GETDATE(),112)
      BACKUP DATABASE  test  TO DISK = @SavePath WITH INIT, NOSKIP, NOFORMAT    

END

 

 

 PS： 欢迎转载，转载请保留出处，谢谢。

参考链接：https://support.microsoft.com/en-us/help/2019698/how-to-schedule-and-automate-backups-of-sql-server-databases-in-sql-se