您的位置:首页 > 数据库 > > 正文

sqlserver备份整表数据的语句(SqlServer批量备份多个数据库且删除3天前的备份)

更多 时间:2021-11-03 15:12:23 类别:数据库 浏览量:182

sqlserver备份整表数据的语句

SqlServer批量备份多个数据库且删除3天前的备份

废话不多说了,直接给大家贴代码了,具体代码如下所示:

  • /******************************************* 
     * 批量备份数据库且删除3天前的备份 
     *******************************************/ 
    DECLARE @backupfile VARCHAR(1024)  
    DECLARE @backdesc VARCHAR(1024)  
    DECLARE @filename VARCHAR(1024)  
    DECLARE @path VARCHAR(1024)  
    DECLARE @dbname VARCHAR(1024)  
    DECLARE @extension_name VARCHAR(16)  
    --备份参数  
    DECLARE tmp_Cur CURSOR  
    FOR  
      SELECT NAME  
      FROM  [sys].[databases]  
      WHERE  NAME NOT IN ( 'master', 'model','msdb','tempdb' )  
    SET @path = N'D:\Backup\Autoback\';  
    SET @extension_name = N'bak';  
    --生成文件名  
    SET @filename = CONVERT(VARCHAR(1024), GETDATE(), 120)  
    SET @filename = REPLACE(@filename, ':', '')  
    SET @filename = REPLACE(@filename, '-', '')  
    SET @filename = REPLACE(@filename, ' ', '')  
    SET @filename = @filename + '_' + CONVERT (VARCHAR(3), DATEPART(ms, GETDATE()))  
      + N'.' + @extension_name  
    OPEN tmp_Cur;  
    FETCH NEXT FROM tmp_Cur INTO @dbname;  
    WHILE @@FETCH_STATUS = 0   
      BEGIN  
        -- 得到完整目标文件,数据库将备份到这个文件中  
        SET @backupfile = @path + @dbname + @filename  
        --SELECT @backupfile  
        SET @backdesc =@dbname + N'-完整 数据库 备份'  
        -- 开始备份, COMPRESSION 参数表示压缩,可节省磁盘空间  
        BACKUP DATABASE @dbname TO DISK = @backupfile WITH NOFORMAT, NOINIT, NAME = @backdesc, SKIP, NOREWIND, NOUNLOAD, STATS = 10, COMPRESSION  
        FETCH NEXT FROM tmp_Cur INTO @dbname  
      END  
    CLOSE tmp_Cur;  
    DEALLOCATE tmp_Cur;  
    -- 删除3天前的备份文件  
    DECLARE @olddate DATETIME  
    SELECT @olddate = DATEADD(d, -3, GETDATE())  
    -- 执行删除 (SQL 2008 具备)  
    EXECUTE master.dbo.xp_delete_file 0, @path, @extension_name, @olddate, 1 
    [sql] view plain copy print?
    --作业定时压缩脚本支持多库 
    DECLARE @DatabaseName NVARCHAR(50) 
    DECLARE @ExecuteSql NVARCHAR(MAX) 
    SET @ExecuteSql='' 
    DECLARE name_cursor CURSOR 
    FOR 
      SELECT name FROM master..sysdatabases WHERE name NOT IN ( 'master', 'model', 'msdb', 'tempdb',  
      'northwind','pubs','AgentSys','ydttimedtask','YiDianTongV2' )  
    OPEN name_cursor;  
    FETCH NEXT FROM name_cursor INTO @DatabaseName;  
    WHILE @@FETCH_STATUS = 0 
      BEGIN   
        SET @ExecuteSql ='' 
        SET @ExecuteSql +=' 
          USE ['+@DatabaseName+']; 
          DECLARE @Error INT 
          SET @Error=(SELECT TOP 1 size/128.0 - CAST(FILEPROPERTY([NAME], ''SpaceUsed'') AS int)/128.0 AS AvailableSpaceInMB FROM sys.database_files ORDER BY [NAME] DESC) 
          --PRINT @Error 
          IF(@Error>1) 
            BEGIN 
              ALTER DATABASE ['+@DatabaseName+']  --数据库名字 
              SET RECOVERY SIMPLE;  --设置简单恢复模式 
              DBCC SHRINKFILE ([YiDianTongV2], 1);  --(M)不能小于1M, 
              DBCC SHRINKFILE ([YiDianTongV2_log], 1);  --(M)不能小于1M 
              ALTER DATABASE ['+@DatabaseName+'] 
              SET RECOVERY FULL;  --恢复为原来完整模式 
            END 
        ' 
        PRINT @ExecuteSql; --打印 
        EXEC(@ExecuteSql) --执行 
        FETCH NEXT FROM name_cursor INTO @DatabaseName;  
      END;  
    CLOSE name_cursor;  
    DEALLOCATE name_cursor; 
    
  • 总结

    以上所述是小编给大家介绍的SqlServer批量备份多个数据库且删除3天前的备份,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对开心学习网网站的支持!

    您可能感兴趣