script Powershell simple to recovery mode

Question

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 ?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

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

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

joujousagem2006 1602 SSCommitted Points: 1779 More actions · Answer 2

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 4

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/