Microsoft SQL Serverデータベースのアーカイブ

勤務中、MSSQLデータベースサーバーに遭遇します。 多くの場合、データベースのアーカイブ、テストサーバー、および運用環境で迅速に構成する必要があります。 さらに、ネットワークでは、多くの異なる単音節のソース、アーカイブする方法またはアーカイブしない方法を見つけることができますが、多かれ少なかれ普遍的な既製のソリューションはどこにもありません。 新しい職場で、この問題に再び遭遇しました。 特定の理由により、会社のすべてのデータベースは（これまで）単純な復旧モデルのモードになっています。テキストで示されたソリューションは完全ではありませんが、フォーラム、初心者、これらのタスクから遠く離れた開発者や管理者の質問から判断すると、非常に適切な方法です解決策ですが、その過程で、誰もが自分でそれを補足できます。



このスクリプトは、指定されたデータベースをアーカイブします。これには、完全なコピーを作成する曜日、最後の完全アーカイブおよび差分アーカイブの保存量が考慮されます。 単純な復旧モデルでデータベースをアーカイブするために設計されており、ログもアーカイブする必要がある場合は、類推によりスクリプトを完全に補完できます。 おそらく必要なときに、このスクリプトを追加します。 バックアップの最後に、古いデータベースアーカイブがディスクから削除されます。



このコードは、割り当てられたタスクにすぐに追加して、1日1回またはそれ以上の頻度で実行できます。必要に応じて、いくつかのパラメーターを設定しながら、スクリプトに十分なコメントを付けます。



アーカイブを保存するパスを指定します;アーカイブの開始時に、各アーカイブデータベースのサブフォルダーがこれらのフォルダーに作成されます。

--    declare @FullPath varchar(500) = 'D:\Work\Full' declare @DiffPath varchar(500) = 'D:\Work\Diff'
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

アーカイブのために@IncludeBaseで特定のデータベースのリストを指定しない場合、すべてのデータベースが取得され、@ ExcludeBaseで指定されたデータベースはそれらから除外されます。

 --       declare @IncludeBase varchar(500) = '' --   ,     ,       declare @ExcludeBase varchar(500) = 'master, model, tempdb'
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

ここでは、完全なアーカイブの曜日をコンマで区切って示します。

 --      declare @FullDay varchar(13) = '7'
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

ディスクに残すアーカイブの各タイプの最近のコピーの数：

 --     declare @MinFull int = 3 declare @MinDiff int = 3
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

サーバーでスクリプトを実行すると、アーカイブの圧縮とxp_cmdshellプロシージャを実行する機能が有効になります。 サービスには、ディレクトリをアーカイブするための読み取り/書き込み/削除権限が必要です。



スクリプトテキスト：

 --    declare @FullPath varchar(500) = 'D:\Work\Full' declare @DiffPath varchar(500) = 'D:\Work\Diff' --       declare @IncludeBase varchar(500) = '' --   ,     ,       declare @ExcludeBase varchar(500) = 'model, tempdb' --      declare @FullDay varchar(13) = '7' --     declare @MinFull int = 3 declare @MinDiff int = 3 --   EXEC sp_configure 'show advanced options', 1; EXEC sp_configure 'backup compression default', 1; RECONFIGURE WITH OVERRIDE; --  xp_cmdshell EXEC sp_configure 'show advanced options', 1; EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE WITH OVERRIDE; set datefirst 1 declare @tempcmd varchar(500) ='' declare @tempname varchar(500) ='' --   set @tempcmd= 'md '+@FullPath exec xp_cmdshell @tempcmd, no_output set @tempcmd= 'md '+@DiffPath exec xp_cmdshell @tempcmd, no_output --      declare @BaseListIncl table (name varchar(200)) declare @BaseListExcl table (name varchar(200)) if @IncludeBase='' insert into @BaseListIncl select name from sys.databases where state_desc='ONLINE' else while len(@IncludeBase)>0 begin if CHARINDEX (',',@IncludeBase)>0 begin insert into @BaseListIncl select name from sys.databases where state_desc='ONLINE' and name = SUBSTRING(@IncludeBase,1, CHARINDEX (',',@IncludeBase)-1) set @IncludeBase=LTRIM(RTRIM(SUBSTRING(@IncludeBase,CHARINDEX (',',@IncludeBase)+1, LEN(@IncludeBase)))) end else begin insert into @BaseListIncl select name from sys.databases where state_desc='ONLINE' and name = @IncludeBase set @IncludeBase='' end end if @ExcludeBase='' insert into @BaseListIncl select name from sys.databases where state_desc='ONLINE' else while len(@ExcludeBase)>0 begin if CHARINDEX (',',@ExcludeBase)>0 begin insert into @BaseListExcl select name from sys.databases where state_desc='ONLINE' and name = SUBSTRING(@ExcludeBase,1, CHARINDEX (',',@ExcludeBase)-1) set @ExcludeBase=LTRIM(RTRIM(SUBSTRING(@ExcludeBase,CHARINDEX (',',@ExcludeBase)+1, LEN(@ExcludeBase)))) end else begin insert into @BaseListExcl select name from sys.databases where state_desc='ONLINE' and name = @ExcludeBase set @ExcludeBase='' end end --      delete from @BaseListIncl where name in (select name from @BaseListExcl) declare BaseList cursor for select name from @BaseListIncl declare @BaseName varchar(500) ='' -- ,      declare @type bit = 0 declare @notexistfull bit = 0 if CHARINDEX(CAST(DATEPART(weekday,getdate()) as varchar(1)),@FullDay)>0 set @type=1 open BaseList fetch next from BaseList into @BaseName while @@FETCH_STATUS = 0 begin -- ,         if EXISTS (SELECT * FROM msdb.dbo.backupset s INNER JOIN msdb.dbo.backupmediafamily mf ON s.media_set_id = mf.media_set_id WHERE s.database_name=@BaseName and s.TYPE='D' ) set @notexistfull=0 else set @notexistfull=1 --    if @type=1 OR @BaseName='master' OR @notexistfull=1 set @tempcmd= 'md '+@FullPath+'\'+@BaseName else set @tempcmd= 'md '+@DiffPath+'\'+@BaseName exec xp_cmdshell @tempcmd, no_output if @type=1 OR @BaseName='master' OR @notexistfull=1 begin -- full backup set @tempname = @FullPath+'\'+@BaseName+'\'+@BaseName+'_'+CONVERT(varchar(8), GETDATE(), 112)+ '-' + REPLACE(CONVERT(varchar, GETDATE(),114),':','') +'.FULL' backup database @BaseName to disk = @tempname end else begin -- diff backup set @tempname = @DiffPath+'\'+@BaseName+'\'+@BaseName+'_'+CONVERT(varchar(8), GETDATE(), 112)+ '-' + REPLACE(CONVERT(varchar, GETDATE(),114),':','') +'.DIFF' backup database @BaseName to disk = @tempname with differential end --    declare @delpath varchar(500)='' declare delbackup cursor for SELECT mf.physical_device_name FROM msdb.dbo.backupset s INNER JOIN msdb.dbo.backupmediafamily mf ON s.media_set_id = mf.media_set_id WHERE s.database_name=@BaseName and s.TYPE='D' and not s.backup_set_id in ( SELECT TOP (@MinFull) s.backup_set_id FROM msdb.dbo.backupset s INNER JOIN msdb.dbo.backupmediafamily mf ON s.media_set_id = mf.media_set_id WHERE s.database_name=@BaseName and s.TYPE='D' ORDER BY s.backup_finish_date desc ) union all SELECT mf.physical_device_name FROM msdb.dbo.backupset s INNER JOIN msdb.dbo.backupmediafamily mf ON s.media_set_id = mf.media_set_id WHERE s.database_name=@BaseName and s.TYPE='I' and not s.backup_set_id in ( SELECT TOP (@MinDiff) s.backup_set_id FROM msdb.dbo.backupset s INNER JOIN msdb.dbo.backupmediafamily mf ON s.media_set_id = mf.media_set_id WHERE s.database_name=@BaseName and s.TYPE='I' ORDER BY s.backup_finish_date desc ) open delbackup fetch next from delbackup into @delpath while @@FETCH_STATUS = 0 begin set @tempcmd= 'del /f /q '+QUOTENAME(@delpath,'"') exec xp_cmdshell @tempcmd, no_output fetch next from delbackup into @delpath end close delbackup deallocate delbackup fetch next from BaseList into @BaseName end close BaseList deallocate BaseList --   MSDB     ( 120 ) declare @oldest DATETIME SET @oldest = DATEADD(DAY, -120, GETDATE()) EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = @oldest
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

このスクリプトは特定のタスク用に作成されています。本番などで議論することは不可能です。意味がないと思います。準備ができていない人が既製のソリューションを取得できるようにするために公開されます。



ユーザーideatumのコメントから、xp_cmdshellプロシージャはセキュリティ上の理由からMicrosoft SQL Serverでデフォルトで無効になっていると付け加えます。



更新

スクリプトが修正され、起動時にデータベースの完全バックアップが作成されるようになりました。データベースが欠落している場合は、たとえ今日がデータベースを作成する日でなくても



更新2

データベースステータスのチェックを追加（オンライン）