Full Database Maintenance with SQLPSX – SQL Server Powershell Extensions


 

 logo - SQLPSX 2 - 590x100

Pessoal,

A SQLPSX- SQL Server Powershell Extensions  conta agora com um novo módulo, SQLMaint, feito por mim (heheheheh) com ajuda do Chad Miller.

Neste módulo tem uma função chamada invoke-dbmaint.

Esta função contempla toda a manutenção de databases, sendo elas :

· Backups (DB, Log, Differential)

· Rebuild/Reorg indexes

· Statistics Full and Sample

· CheckDB

· Housekeeping MSDB after n days

· Clean Old Backups (.trn and .bak) after n days

· Clean Old Reports (.log) after n days

É muito simples de usar.

Estou finalizando o artigo pra SQL Server Standard Magazine que vai justamente mostrar como usar este módulo com exemplos detalhados, e coloco aqui para vocês. Este também sera o tema de minha palestra no Marilia TechDay 2010.

Mas segue alguns exemplos de como usar :

   1: #Backup full all databases, server R2D2 to c:\Temp with reports to c:\temp        
   2: Invoke-DBMaint -server R2D2 -Databases "ALL" -Action "BKP_DB" -BackupOn c:\Temp -ReportOn c:\Temp
   3:  
   4: #Backup full all system databases, server R2D2 to c:\Temp with reports to c:\temp
   5: Invoke-DBMaint -server R2D2 -Databases "SYSTEM" -Action "BKP_DB" -BackupOn c:\Temp -ReportOn c:\Temp
   6:  
   7: #Backup full all USER databases, server R2D2 to c:\Temp with reports to c:\temp
   8: Invoke-DBMaint -server R2D2 -Databases "USER" -Action "BKP_DB" -BackupOn c:\Temp -ReportOn c:\Temp
   9:  
  10: #Backup LOG all USER databases, server R2D2 to c:\Temp with reports to c:\temp        
  11: Invoke-DBMaint -server R2D2 -Databases "USER" -Action "BKP_LOG" -BackupOn c:\Temp -ReportOn c:\Temp
  12:  
  13: Rebuild/Reorganize all indexes in All User Databases (rule > 10% and <30 reorg, > 30 rebuild and pagecount > 1000) with reports to c:\temp            
  14: #Invoke-DBMaint -server R2D2 -Databases "USER" -Action "IDX_ALL"  -ReportOn c:\Temp
  15:  
  16: #Rebuild all indexes in All User Databases without rules with reports to c:\temp    
  17: Invoke-DBMaint -server R2D2 -Databases "USER" -Action "IDX_REBUILD"  -ReportOn c:\Temp
  18:  
  19: #Reorg all indexes in All User Databases without rules with reports to c:\temp    
  20: Invoke-DBMaint -server R2D2 -Databases "USER" -Action "IDX_REORG"  -ReportOn c:\Temp
  21:  
  22: #Statistics full all in All User Databases without rules with reports to c:\temp    
  23: Invoke-DBMaint -server R2D2 -Databases "USER" -Action "STATS_FULL"  -ReportOn c:\Temp
  24:  
  25: #Statistics Sample all in All User Databases without rules with reports to c:\temp    
  26: Invoke-DBMaint -server R2D2 -Databases "USER" -Action "STATS_SAMPLE"  -ReportOn c:\Temp
  27:  
  28: #Check DB all use databases with reports to c:\temp    
  29: Invoke-DBMaint -server R2D2 -Databases "USER" -Action "CHECK_DB"  -ReportOn c:\Temp
  30:  
  31: #Delete old files 10 days from C:\temp and MSDB 10 days too
  32: Invoke-DBMaint -server R2D2 -Databases "DELHIST" -Action "DEL_HIST"  -ReportOn c:\Temp -RemoveDataBackupsMSDB 10 -RemoveOldBackups 10
  33:  
  34: #CheCK Db in all user databases from servers.txt
  35: get-content c:\temp\servers.txt | Invoke-DBMaint -Databases "USER" -Action "CHECK_DB"  -ReportOn c:\Temp

Se vc quiser um help com exemplos pode digitar :

   1: help -Full Invoke-DBMaint

 

QQ coisa, ja sabem..email…etc..etc;;

Abraços  !!!!

POWERSHELL ROCKS !!!!

“Mustang Sally
Guess you better slow your Mustang down
Mustang Sally, baby
I guess you better slow your Mustang down
You been runnin’ all over town
Guess I’ll have to put your flat feet on the ground

All you wanna do is ride around Sally (Ride Sally Ride)
All you wanna do is ride around Sally (Ride Sally Ride)
All you wanna do is ride around Sally (Ride Sally Ride)
All you wanna do is ride around Sally (Ride Sally Ride)
One of these early mornings
I’m gonna be wipin’ your weepin’ eyes”

Mustang Sally

Andy Taylor

About Laerte Junior

Laerte Junior Laerte Junior is a SQL Server specialist and an active member of WW SQL Server and the Windows PowerShell community. He also is a huge Star Wars fan (yes, he has the Darth Vader´s Helmet with the voice changer). He has a passion for DC comics and living the simple life. "May The Force be with all of us"
This entry was posted in Powershell. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s