定期自动备份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 是为了覆盖之前备份的内容。

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

 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 如下

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

　　3.2 创建Windows 任务定时执行Copy.bat ,具体任务配置就不描述了，同上。

 

这样就完成了文件的自动定期备份任务。