定期自动备份SQL Server 上的所有数据库

手头有一个比较老的SQL Sever 9.0 的服务器，因为是生产环境，使用的项目比较多，还有一些项目半年一年才用一次，所以不想将数据库服务器升级到最新的SQL Server, 有可能升级后那个产品就不能使用了，又需要折腾几天才能用，而项目使用的时候又是要发布了，也没有时间给你折腾。

这个数据库服务器的数据都是手动进行备份的，因为我比较懒，不像每次手动备份，就考虑找一个自动备份的方案。

方案思路：

1. 使用存储过程将所有数据库备份到本地。

2.创建一个Windows定期任务每天运行存储过程，本来SQL Server Agent 来创建定期任务，但是这个服务器上使用Agent 创建计划总是有问题。

3.创建Windows 定期任务将备份好的数据库Copy 到其它机器上。

接下来我们一步一步详细介绍。

1.1 在 Master 数据库上创建一个备份所有数据库的存储过程。

USE [master]
GO
 
/****** Object:  StoredProcedure [dbo].[p_BackAllDatabase]    Script Date: 2020/03/04 10:51:20 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
 
CREATE PROCEDURE [dbo].[p_BackAllDatabase]
(
   @path nvarchar(500) --备份的路径，不带后面的/字符
)
AS
--功能：备份所有数据库
 
--declare @back_date varchar(20)  --备份的时间
declare @file varchar(500)  --备份的文件名
declare @dbname varchar(50)  --备份的数据库名
declare @sql varchar(500)  --执行备份时的sql
DECLARE dba_database CURSOR FOR  --所有数据库的光标
       select [name] from sysdatabases order by [name]
 
--SET @back_date = convert(varchar ,getdate(), 112)
OPEN dba_database
 
FETCH NEXT FROM dba_database into @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
    IF (@dbname <> 'tempdb')
    BEGIN
         --SET @file = @path + '\' + @dbname + '-' +  @back_date + '.bak' --生成备份的文件名
         SET @file = @path + '\' + @dbname  + '.bak' --生成备份的文件名
         SET @sql = 'backup database [' + @dbname + '] to disk = ' + ''''+ @file + '''' +  ' with init'
         PRINT @sql
         EXEC (@sql)
    END
 
    FETCH NEXT FROM dba_database  into @dbname
END
 
CLOSE dba_database
DEALLOCATE dba_database
GO

　　其中下面一句主要是进行备份动作的，之所以使用With init 是为了覆盖之前备份的内容。

开始没有使用，备份了几次后发现备份文件越来越大，硬盘被占用空间增长很快。

1	`SET` `@sql =` `'backup database ['` `+ @dbname +` `'] to disk = '` `+` `''''+ @file +` `''''` `+` `' with init'`

2 创建Windows 定时任务，定时备份

2.1创建一个bat 调用存储过程，在命令行下进行备份。

@ECHO OFF
 
SET dbhost=127.0.0.1
SET dbuser=sa
SET dbpasswd= 
set dbName=master
SET sqlpath=%~dp0
set sqlfile=backup.sql
 
 
osql -S %dbhost% -U %dbuser% -P %dbpasswd% -d %dbName% -i %sqlpath%%sqlfile%
 
ECHO finish
 
 
@ECHO Done!

　　其中的 backup.sql 如下

1	`exec` `dbo.p_BackAllDatabase` `'F:\DBBackup'`

　2.2 创建 Windows 任务定时执行 bat

3. 创建一个Window 任务Copy 备份文件到其它机器

3.1 创建Copy.bat 进行Copy

set source=F:\DBBackup
set target=\\xa-at-file\ATShareData\qa_sql_dbback
 
del "%target%\*" /q
 
xcopy "%source%" "%target%" /s /y
 
del "%source%\*" /q