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

EXEC master.dbo.sp_BackupDatabases @databaseName='myTest_Db', @backupLocation='D:\testAutoDBBack\'