SQL Server backup file management (automatic deletion and expiration)

ForSQL ServerThe management of backup files is very exquisite, especially when the disk space is notLarge orDBIt is really difficult for administrators with too many servers. At the same time, it is also important, because if the disk is cleared from time to time, it will certainly be full, and it will also lead to the failure of new backup. Because. Of course, it is best to manually organize backup files, but it is not intelligent. No way, only throughProgramDone.

 

1.ExploitationManagement Plan(Maintenance plans)Backup File expiration Policy and cleanup maintenance in to manage backup files

 

A. Set backup expiration details:

 

 

B. Automatically overwrite the deleted maintenance (Maintenance cleanup) in the expired backup (maintenance plans) based on the disk space ))

 

 

For more information about the maintenance plans (Maintenance Plan), see: Maintenance plans (Maintenance Plan)Details 

 

2. ExploitationJobAndT-SQLTo delete backup files

This is a tragedy. Why? Because the above methodJobBanned, I also said, why can't I delete the file because I cannot find the cause? I am not serious about it. However, this search method can be quite helpful.

First,GoogleHowever, it is found that all the information about deleting the backup file isSQL:

--Delete expired backup files

 

 

 Declare @ STR varchar (  100  ), @ Dir varchar (  100 ), @ Filename varchar (  30  )

Set @ dir  =  '  Del D: \ dbtext \ jgj \ dbabak \'  
  
Set @ filename  =  Left (replace (convert (varchar, getdate ()  -  15  ,  20  ),  ' -  '  ,  ''  ),  '     '  ,  ''  ),  '  :  '  ,  ''  ),  8 )

Set @ Str  =  @ Dir  +  '  Fullbak  '  +  @ Filename  +  '  *. Bak  '  

  Exec  Xp_cmdshell @ Str

Set @ filename  = Left (replace (convert (varchar, getdate ()  -  8  ,  20  ),  '  -  '  ,  ''  ),  '     '  ,  ''  ), '  :  '  ,  ''  ),  8  )

Set @ Str  =  @ Dir  +  '  Diffbak  '  +  @ Filename  +  ' *. Diff  '  

  Exec  Xp_cmdshell @ Str

Set @ filename  =  Left (replace (convert (varchar, getdate ()  -  8  ,  20  ),  '  -  '  ,  '' ),  '     '  ,  ''  ),  '  :  '  ,  ''  ),  8  )

Set @ Str  =  @ Dir  + '  Logbak  '  +  @ Filename  +  '  *. TRN  '  

  Exec  Xp_cmdshell @ Str 

AlthoughSQLIt can achieve the effect, but it is very mentally retarded and not intelligent.

1. This is shortSQLYou need to manually enter the disk address of the backup file. You need to set parameters and parameters. The versatility is very poor. For differentDB, Must be manually changed again, very unintelligent!

2. You cannot intelligently obtain the backup that has expired.

 

ThenSQLThe statement is transformed into a more general and intelligent deletion of backup files:

 --  Use the cursor to delete the expire Bak  
  
  -- Also cocould not user the cursor if you only want to delete the top 1 oldest Bak  
  
  --  @ Filepath: The expire Bak's path  
  
  Declare  Filecursor  Cursor     For   

  Select     *    From  

(

  Select     Top     3  B. physical_device_name

  From     [  MSDB  ]  .  [  DBO ]  .  [  Backupset  ]  A,  [  MSDB  ]  .  [  DBO  ]  .  [  Backupmediafamily  ]  B

 Where  A. media_set_id  =  B. media_set_id  And     [  Expiration_date  ]  <  Getdate  ()

  Order     By     [ Expiration_date  ]     ASC  

)

  As  Filetable

  Declare     @ Filepath     Varchar  (  100  )

  Open Filecursor

  Fetch     Next     From  Filecursor  Into     @ Filepath  

  While     @ Fetch_status  =  0 

  Begin  

  Declare     @ Delcmd     Varchar  (  100  )

  Set     @ Delcmd     =  ( '  Del  '  +  @ Filepath  )

  --  User xp_role shell to delete the Bak  
  
  Exec  Xp_mongoshell  @ Delcmd  

  Fetch     Next    From  Filecursor  Into     @ Filepath   

  End  

  Close  Filecursor

  Deallocate  Filecursor 

Although this method looks very high-tech, we recommend that you use the first method unless the SQL Server version is too old and does not support maintenance plans. The second method needs to be enabled.Xp_restart shell, which requires restarting the SQL Server service

 

In addition, I shared an SQL statement and found that many of my colleagues would not use it, but I don't want to explain it. I hope it will help you:

 

 With  # Pager  As  
(
  Select  Followid,  Count  (Followid)  As  Num

  From  Table 2

  Where  Followtype  = 2     And  Followid  >  0  

  Group     By  (Followid)
)

  Update  Table1  Set  Followcount  = Followcount  +  Num
  From  # Pager  As  P, table3 C
  Where  P. followid  =  C. ID