SQL拼接备份数据库

在SQLserver使用脚本备份数据库的时候需要注意的问题是:

1.指向的文件名必须是有读写权限。

2.在使用批量数据库备份时候需要根据自己需求选择性备份。

-- =============================================
-- Author:      <奔跑的金鱼>
-- Blog:        <http://www.cnblogs.com/OliverQin/>
-- Create date: <2015/01/04>
-- Description: <备份指定数据库>
-- =============================================
DECLARE
      @FileName     VARCHAR(200),
      @CurrentTime  VARCHAR(50),
      @DBName       VARCHAR(100),
      @SQL          VARCHAR(1000)
BEGIN
    SET @CurrentTime = CONVERT(CHAR(8),GETDATE(),112) + CAST(DATEPART(hh, GETDATE()) AS VARCHAR) + CAST(DATEPART(mi, GETDATE()) AS VARCHAR)

    SET @DBName='test'---------你要备份的数据库名

    SET @FileName = 'C:\backup\' + @DBName + '_' + @CurrentTime

    SET @SQL = 'BACKUP DATABASE ['+ @DBName +'] TO DISK = ''' + @FileName + '.bak' +
     ''' WITH NOINIT, NOUNLOAD, NAME = N''' + @DBName + '_backup'', NOSKIP, STATS = 10, NOFORMAT'
    PRINT @SQL
    EXEC(@SQL)
END


--*************************************************************************************************
-- =============================================
-- Author:      <奔跑的金鱼>
-- Blog:        <http://www.cnblogs.com/OliverQin/>
-- Create date: <2015/01/04>
-- Description: <批量备份数据库>
-- =============================================
----------------------------------------------------申明变量
DECLARE
      @FileName VARCHAR(200),
      @CurrentTime VARCHAR(50),
      @DBName VARCHAR(100),
      @SQL VARCHAR(1000)
---------------------------------------------------获取当前时间
SET @CurrentTime = CONVERT(CHAR(8),GETDATE(),112) + CAST(DATEPART(hh, GETDATE()) AS VARCHAR) + CAST(DATEPART(mi, GETDATE()) AS VARCHAR)
---------------------------------------------------申明游标
DECLARE CurDBName CURSOR FOR 
    SELECT NAME FROM Master..SysDatabases where dbid>4
---------------------------------------------------打开游标
OPEN CurDBName
-----------------------------------------------------移动游标指向到第一条数据，提取第一条数据存放在变量中
FETCH NEXT FROM CurDBName INTO @DBName
-----------------------------------------------------如果上一次操作成功则继续循环
WHILE @@FETCH_STATUS = 0
BEGIN
    --Execute Backup
    SET @FileName = 'C:\backup\' + @DBName + '_' + @CurrentTime
    SET @SQL = 'BACKUP DATABASE ['+ @DBName +'] TO DISK = ''' + @FileName + '.bak' +
     ''' WITH NOINIT, NOUNLOAD, NAME = N''' + @DBName + '_backup'', NOSKIP, STATS = 10, NOFORMAT'
    EXEC(@SQL)
    --print @SQL
-----------------------------------------------------继续提下一行数据
    FETCH NEXT FROM CurDBName INTO @DBName
END
---------------------------------------------关闭游标
CLOSE CurDBName
---------------------------------------------删除游标
DEALLOCATE CurDBName


--*************************************************************************************************
-- =============================================
-- Author:      <奔跑的金鱼>
-- Blog:        <http://www.cnblogs.com/OliverQin/>
-- Create date: <2015/01/04>
-- Description: <备份指定数据库>
-- Way:         <使用SQL拼接的方式>
-- =============================================
DECLARE @SQL VARCHAR(MAX)
SELECT @SQL = COALESCE(@SQL,'') + '
BACKUP DATABASE '+ QUOTENAME(name,'[]') 
+ ' TO DISK = ''C:\backup\'+ name + '_' + CONVERT(CHAR(8),GETDATE(),112) + CAST(DATEPART(hh, GETDATE()) AS VARCHAR) + CAST(DATEPART(mi, GETDATE()) AS VARCHAR) + '.bak' 
+ ''' WITH NOINIT, NOUNLOAD, NAME = N''' + name + '_backup'', NOSKIP, STATS = 10, NOFORMAT'
FROM sys.databases WHERE database_id >4 AND name like '%%' AND state =0

PRINT(@SQL)
EXECUTE(@SQL)