window环境下SQL Server 数据库备份脚本

SQL Server 数据库备份脚本

参考博客：https://www.cnblogs.com/wanggang2016/p/10395645.html

1、单一数据库备份 (测试可以)

GO   
DECLARE   
@backupTime VARCHAR(20)  
DECLARE   
@fileName VARCHAR(1000)    
SELECT  -- 时间
 @backupTime=(CONVERT(VARCHAR(8), GETDATE(), 112) +REPLACE(CONVERT(VARCHAR(5), GETDATE(), 114), ':', ''))    
 --核心主数据库
SELECT  -- 文件名
 @fileName='D:\backup\DataBase_BACKUP\数据库名_'+@backupTime+'.bak'  
backup database 数据库名 to disk=@fileName 

2、全部数据库备份 (测试可以)

--开启文件夹权限
GO
SP_CONFIGURE 'SHOW ADVANCED OPTIONS',1
RECONFIGURE
GO
SP_CONFIGURE 'XP_CMDSHELL',1
RECONFIGURE
GO
 
DECLARE
      @FileName VARCHAR(200),
      @CurrentTime VARCHAR(50),
      @DBName VARCHAR(100),
      @SQL VARCHAR(1000),
      @FilePath VARCHAR(100)
 
--SET @CurrentTime = CONVERT(CHAR(8),GETDATE(),112) + CAST(DATEPART(hh, GETDATE()) AS VARCHAR) + CAST(DATEPART(mi, GETDATE()) AS VARCHAR)
--年月日
SET @CurrentTime = CONVERT(CHAR(8),GETDATE(),112)
 
 
SET @FilePath = 'D:\Backup\SQLDataBaseBackupTest\' + @CurrentTime + '\'
--select CONVERT(CHAR(8),GETDATE(),112)
--文件夹不存在，则创建
declare @TEMP TABLE(A INT,B INT,C INT)--建立虚拟表，用来判断文件夹是否存在
INSERT @TEMP EXEC [MASTER]..XP_FILEEXIST @FilePath
IF NOT EXISTS(SELECT * FROM @TEMP WHERE B=1)
BEGIN
    --XP_CMDSHELL不允许使用变量拼接，所以使用exec方法
    declare @EX NVARCHAR(255)
    SET @EX = 'EXEC XP_CMDSHELL ''MKDIR ' + @FilePath + '''';
    EXEC(@EX)
END
 
 
--获取所有非系统数据库
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 = @FilePath + @DBName + '_text_' + @CurrentTime
    SET @SQL = 'BACKUP DATABASE ['+ @DBName +'] TO DISK = ''' + @FileName + '.bak' +
     ''' WITH NOINIT, NOUNLOAD, NAME = N''' + @DBName + '_backup'', NOSKIP, STATS = 10, NOFORMAT'
    EXEC(@SQL)
 
    --Get Next DataBase
    FETCH NEXT FROM CurDBName INTO @DBName
END
 
CLOSE CurDBName
DEALLOCATE CurDBName

3、删除备份数据30天前 (未测试)

--开启文件夹权限
GO
SP_CONFIGURE 'SHOW ADVANCED OPTIONS',1
RECONFIGURE
GO
SP_CONFIGURE 'XP_CMDSHELL',1
RECONFIGURE
GO
EXEC xp_cmdshell 'forfiles /p D:\Backup\SQLDataBaseBackupTest\ /m * -d -30 /c "cmd /c rd /s /q @path"'

总结：
数据备份：
1、无脚本：直接用维护计划
2、有脚本：脚本+作业
3、脱离sql server studio工具:用bat执行sql文件，加到window定时任务中