Hello,

I'm having a problem putting together a script to backup all
databases on a server. I tried something like this, it will work for
the first pass. But then fail on the second. I realize that it has to
do with the creation of another
Microsoft.SqlServer.Management.Smo.BackupDeviceItem but I'm not sure
how to address this?



[System.Reflection.Assembly]::LoadFile("C:\Program Files\Microsoft SQL
Server\90\SDK\Assemblies\Microsoft.SqlServer.Smo.dll") | out-null
[System.IO.Directory]::CreateDirectory("C:\temp") | out-null
$srv=New-Object "Microsoft.SqlServer.Management.Smo.Server"
"575ma3123581"
$bck=new-object "Microsoft.SqlServer.Management.Smo.Backup"
$bck.Action = 'Database'

foreach([Microsoft.SqlServer.Management.Smo.Server.Database]$db in
$srv.Databases )
{

$fil=new-object "Microsoft.SqlServer.Management.Smo.BackupDeviceItem"
$fil.DeviceType='File'
$fil.Name=[System.IO.Path]::Combine("\\AFileServer\sql
\DatabaseServerHostName\DataBackup", $db.Name +".bak")

$bck.Devices.Add($fil)
$bck.Database= $db.Name
$bck.SqlBackup($srv)

}

write-host "Backup of MyDatabase done"

dba wrote:

> Hello,
>
> I'm having a problem putting together a script to backup all
> databases on a server. I tried something like this, it will work for
> the first pass. But then fail on the second. I realize that it has to
> do with the creation of another
> Microsoft.SqlServer.Management.Smo.BackupDeviceItem but I'm not sure
> how to address this?

Actually, your code looks good. I can test it tomorrow. You use
new-object in each run, which should create a new instance just like you
need.

Marco

I think the problem is that on the second pass you are trying to create a
backup device that already exists. This is how I've done backups using SMO

## load SMO assemblies
## use $null to prevent display of assembly load information
$null =
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
$null =
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum")
$null = [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

clear-host

## set SMO variable
$Smo = "Microsoft.SqlServer.Management.Smo."

$server = new-object ($Smo + "Server") "SQL1"

## databse to back up
$bkup = new-object ($smo + "Backup")
$bkup.Database = "AdventureWorks"

## set backup device and type

$date = get-date

$date = $date -replace "/", "-" # replace / symbols in date part
$date = $date -replace ":", "-" # replace : symbols in time part
$date = $date -replace " ", "--Time-" # show time part
$file = "C:\Backups\AdventureWorks" + "--" + $date + ".bak"

$bkup.Devices.AddDevice($file,
[Microsoft.SqlServer.Management.Smo.DeviceType]::File)
$bkup.Action = [Microsoft.SqlServer.Management.Smo.BackupActionType]:atabase

## invoke backup
$bkup.SqlBackup($server)


--
Richard Siddaway
All scripts are supplied "as is" and with no warranty
PowerShell MVP
Blog: http://richardsiddaway.spaces.live.com/
PowerShell User Group: http://www.get-psuguk.org.uk


"Marco Shaw [MVP]" wrote:

> dba wrote:

> > Hello,
> >
> > I'm having a problem putting together a script to backup all
> > databases on a server. I tried something like this, it will work for
> > the first pass. But then fail on the second. I realize that it has to
> > do with the creation of another
> > Microsoft.SqlServer.Management.Smo.BackupDeviceItem but I'm not sure
> > how to address this?

>
> Actually, your code looks good. I can test it tomorrow. You use
> new-object in each run, which should create a new instance just like you
> need.
>
> Marco
>