Backup specific MS SQL databases

PowerShell Help
1

by Tadziz at 2013-02-13 08:20:28

This is my code. I want to backup specific databases. in this example model and master, but it only backup model database. if i change master to msdb it will backup msdb and skip model. I dont understand.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null

#------- MS SQL BACKUP -------###
#get date for backup name
$dt = get-date -format yyyyMMdd-HHmmss

$s = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) "pvt-2012"

#path where to backup database
$path = 'C:\Install'

$dbs = $s.Databases

foreach ($db in $dbs) {

#PROBLEM HERE
if (@("master", "model") -contains $db.name) {

$dbname = $db.Name
#Create a Backup object instance with the Microsoft.SqlServer.Management.Smo.Backup namespace
$dbBackup = new-object ("Microsoft.SqlServer.Management.Smo.Backup")

$dbBackup.Database = $dbname
$copyDB = ($dbname + "" + $dt + ".bak")

#diff backups
#$smoBackup.Incremental = $true

#Specify the Action property to generate a FULL backup ( database, file, log )
$dbBackup.Action="DATABASE"

#Enable Backup type "COPY ONly"
#$dbBackup.CopyOnly = $TRUE

#Need to TEST
#$dbBackup.RetainDays = "7"

#Enable MS SQL Compression set to 1 disable 0
$dbBackup.CompressionOption = "1"

#Add the backup file to the Devices collection and specify File as the backup type
$dbBackup.Devices.AddDevice($path + $dbname + "" + $dt + ".bak", "File")

#Call the SqlBackup method to generate the backup
$dbBackup.SqlBackup($s)
}
}
#specify where to copy backups
$dirs = '\tan01\share'

#get new backup file name
$extension = "*.bak"
$file = Get-ChildItem $path -Include $extension -Recurse | sort LastWriteTime | select Name |Select-Object -Last 2
$file = $file -replace "@{Name=", ""
$file = $file -replace "}", ""


foreach ($files in $file) {

foreach($dir in $dirs) {
cd C:\Install\ESEUTIL
.\eseutil.exe /y $path$copyDB /d $dir$files

}

}


Thanks,
Tadas
by sqlchow at 2013-02-13 12:22:57
The Database.Name property is of type string. So, I am a little flummoxed why it does not work.

Can you try something like the below and see if that works?
if(@("master", "model") -contains "$($db.name)")
by Tadziz at 2013-02-13 23:14:54
Hi, thanks, it worked. Solved :slight_smile: