SQL Server SMO is restored locally from a remote server

Question

My question is straightforward, how can I program SQL SMO on a local backup by connecting to a remote server. I want to save a bak file on a local computer that connects to a remote server. I also want users with certain privileges to be able to back up locally.

Answer 1

You cannot - period. SQL Server backups can only be stored on a local drive - locally for SQL Server itself.

You cannot, with any tricks or tools, backup a remote SQL Server to a local hard drive. I just can’t do it.

Answer 2

EDIT: re-reading the question and marc_s answer to my answer will only work if you are talking about a server on your network somewhere on a remote server. If you are talking about hosted SQL Server in another domain somewhere, marc_s is correct, and my answer is useless . I will leave it here anyway if you are talking about a server in your domain. Edit endings

After setting the share in my local directory C: \ tmp, this Powershell bit backs up.

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')  | out-null
# may need this instead [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SmoExtended') | out-null

$datePart = Get-Date -Format "yyyyMMdd_hhmm" 
$targetDir = '\\LocalMachineName\tmp\'  # change to fit your specs
$dbname = "DatabaseNameToBackUp"

$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') 'remoteSqlServer'
$bckfile = $targetDir + $dbname + "_" + $datePart + ".bak"
$dbbk = new-object ('Microsoft.SqlServer.Management.Smo.Backup')
$bdi = new-object ('Microsoft.SqlServer.Management.Smo.BackupDeviceItem') ($bckfile, 'File')
$dbbk.Action = 'Database'
$dbbk.BackupSetDescription = "Full backup of " + $dbname
$dbbk.BackupSetName = $dbname + " Backup"
$dbbk.Database = $dbname
$dbbk.MediaDescription = "Disk"
$dbbk.Devices.Add($bdi)
$dbbk.SqlBackup($s)
$dbbk.Devices.Remove($bdi) |out-null
$bckfile = $null

, Powershell, .net, , . , .net.