script Powershell simple to recovery mode

• joujousagem2006 1602

  SSCommitted

  Points: 1779

  More actions

  January 30, 2018 at 7:06 am

  #343168

  Hello
  I will need to set up an automatism
  that checks the recovery mode of the bases
   If base other than SIMPLE, force the transition to simple mode and shrink database 
  import-module sqlps -DisableNameChecking
#[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
Add-Type -AssemblyName "Microsoft.SqlServer.Smo"
#Force change psdrive from SQLserver to c:\ -> without UNC link not working
cd c:
$mode_recuperation='simple'
$Server='sqlprod'
$SMOserver = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $Server
$SMOserver.Databases | where {$_.IsSystemObject -eq $false} | select Name, RecoveryModel | Format-Table # Ce ci pour enlever les bases systéme
$SMOserver.Databases | where {$_.IsSystemObject -eq $false} | foreach {$_.RecoveryModel = [Microsoft.SqlServer.Management.Smo.RecoveryModel]::Simple; $_.Alter()}#Forcer le passage vers database simpel
$SMOserver.Databases | where {$_.IsSystemObject -eq $false} | select Name, RecoveryModel | Format-Table #simple affichage

  Who has an idea how I can modify my script to add the shrink part ?

• Phil Parkin

  SSC Guru

  Points: 247133

  More actions

  January 30, 2018 at 7:13 am

  #1977848

  Before you go ahead with this, are you aware of possible issues with shrinking databases?
  You may like to read here for more information.

  ---

• joujousagem2006 1602

  SSCommitted

  Points: 1779

  More actions

  January 30, 2018 at 7:28 am

  #1977852

  yes
  or it is an exceptional action
  I have to run it only if I have disk saturation

• Phil Parkin

  SSC Guru

  Points: 247133

  More actions

  January 30, 2018 at 7:31 am

  #1977854

  joujousagem2006 1602 - Tuesday, January 30, 2018 7:28 AM

  yes
  or it is an exceptional action
  I have to run it only if I have disk saturation

  Doesn't that mean that your database needs more disk space?
  Changing recovery mode to simple is not an exceptional action.

  ---

• Steve Jones - SSC Editor

  SSC Guru

  Points: 737608

  More actions

  January 30, 2018 at 9:10 am

  #1977878

  Not that I think this is a good idea, but once you have the db, or db files, you would use a PoSh command to execute dbcc shrinkdatabase or dbccshrinkfile.

  You would  be better off using placeholders to free up space when needed and then deal with the issue. https://voiceofthedba.com/2014/12/01/creating-placeholder-files/