Powershell SQL Server 2008 Database Backup Remotely

Question

I am trying to backup a remote database on the LAN to a network shared drive. I tested using the same approach using .NET and it did work but i am planning to use this PowerShell script withing out Jenkins Continuous Integration tool.

$dbserver = "dbserver"
$location = "\\otherserver\Temp\"
$user = "user"
$pwd = "password"

$timestamp=((get-date).toString("yyyy_MM_dd_hh_mm"))
$file = $location + "jira_" + $timestamp + ".bak"

[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

$connection = New-Object "Microsoft.SqlServer.Management.Common.ServerConnection" 
$connection.ServerInstance =$dbserver
$connection.LoginSecure = $false
$connection.Login = $user 
$connection.Password = $pwd

$server = New-Object "Microsoft.SqlServer.Management.Smo.Server" $connection
$backup = New-Object "Microsoft.SqlServer.Management.Smo.backup"
$backup.Action = 'Database'

$device = New-Object ('Microsoft.SqlServer.Management.Smo.BackupDeviceItem') ($file, 'File')
#$device.DeviceType = 'File'
#$device.Name = $file

$backup.MediaDescription = "Disk"
$backup.Database= "jira"
$backup.Devices.Add($device)
$backup.SqlBackup

I don't get any exceptions but also I don't get any information that can help me troubleshoot my code. Using all available throw, catch, try I can only get.

MemberType          : Method
OverloadDefinitions : {System.Void SqlBackup(Microsoft.SqlServer.Management.Smo.Server srv)}
TypeNameOfValue     : System.Management.Automation.PSMethod
Value               : System.Void SqlBackup(Microsoft.SqlServer.Management.Smo.Server srv)
Name                : SqlBackup
IsInstance          : True

My main question would be: does the user / account that executes this script have access to that remote location? Could you attempt to just copy a file to that location and see if that works? Also: does this backup script work if you target a local drive on the SQL Server machine?? — marc_s
– marc_s, Commented Nov 29, 2012 at 21:47

Chad Miller · Accepted Answer · 2014-11-18 18:48:45Z

2

SqlBackup is a method. When you call a method you need to include opening and closing parenthesis:

$backup.sqlbackup($server)

Omitting the parens will output the method signature instead of calling the method. Omitting the method signature is not an error hence no error.

edited Nov 18, 2014 at 18:48

user2629998

answered Nov 30, 2012 at 3:39

Chad Miller

42.4k4 gold badges33 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Maverick Over a year ago

Thanks i do know so many scripting for linux that learning ps is a little complicated. Thanks!

Collectives™ on Stack Overflow

Powershell SQL Server 2008 Database Backup Remotely

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related