Backup to a remote server

Question

Post reply

Backup to a remote server

TriggerMe

SSChampion

Points: 11752
More actions
February 23, 2004 at 6:28 pm

#82325

Is it possible to backup a Database to a remote server using SQL-DMO? I know I can do it with T-SQL but my task at the moment is to do it via SQL-DMO. Below is my code, I keep getting an error of "Cannot open device"

DIM oSQLServer, oDatabase, oBackup, oLogin, oPassword, StrBackupDestination
On Error Resume Next
oLogin = "sa"
oPassword = "xxxxx"
oDatabase = "Northwind"
StrBackupDestination = "C:\Program Files\Microsoft SQL Server\MSSQL\Backups\NorthwindFileBackup"
SET oSQLServer = CreateObject("SQLDMO.SQLServer")
SET oBackup = CreateObject("SQLDMO.Backup")
oSQLServer.Login = oLogin
oSQLServer.Password = oPassword
oSQLServer.Connect("xxxxxxxx")
oBackup.Action = SQLDMOBackup_Database
oBackup.Database = oDatabase
oBackup.Files = StrBackupDestination
oBackup.Initialize = True
oBackup.BackupSetName = "Northwind24022004"
oBackup.SQLBackup oSQLServer
IF Err.Number <> 0 Then
   MsgBox(Err.Number & " " & Err.Description)
   WScript.Quit
   oSQLServer.Disconnect
   SET oSQLServer = "Nothing"
   SET oBackup = "Nothing"
   SET oApp = "Nothing"
End If
oSQLServer.Disconnect
MsgBox(oDatabase & " Backup Successfully Completed")
SET oSQLServer = "Nothing"
SET oBackup = "Nothing"
Kindest Regards,

Viewing 7 posts - 1 through 6 (of 6 total)

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

dharper3 SSCrazy Points: 2391 More actions · Answer 1

Another simple way is scheduling it via a job and maint plan specifying destination of backup as UNC path

cliffb SSCarpal Tunnel Points: 4598 More actions · Answer 2

I believe the message means it cannot find a backup device named "Northwind24022004".

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 3

Does the SQLServer account have the rights to write that file? You start off saying you are trying to backup to a remote server, but your example shows writing to a local drive. If you want to backup to a remote server, the SQLServer service must run under a domain account, and have rights to create/write files at the destination (as any network account must).

Also (if memory serves correctly), I had a problem when trying to create a backup of the same backup filename that existed. I named the file with datetime, just like maintenance plan does. Here is a snippet of my vbscript that I used:

 ' Create a Backup object and set action and source database properties.
sDtStr = CStr(Year(Now))
If Month(Now()) < 10 then sDtStr = sDtStr & "0"
sDtStr = sDtStr & CStr(Month(Now))
If Day(Now()) < 10 then sDtStr = sDtStr & "0"
sDtStr = sDtStr & CStr(Day(Now))
If Hour(Now()) < 10 then sDtStr = sDtStr & "0"
sDtStr = sDtStr & CStr(Hour(Now))
If Minute(Now()) < 10 then sDtStr = sDtStr & "0"
sDtStr = sDtStr & CStr(Minute(Now))

Dim oBackup 
Set oBackup = CreateObject("SQLDMO.Backup")
oBackup.Action = SQLDMOBackup_Database
oBackup.Database = sDBName
oBackup.Files = sDBBkpPath & sDBName & "_" & sDtStr & ".bak"
oBackup.MediaName = sDBName & ".bak " & Date & " " & Time
oBackup.BackupSetName = sDBName
oBackup.BackupSetDescription = "Full backup of Yada DB before Updates."
oBackup.SQLBackup oServer

Mark

TriggerMe SSChampion Points: 11752 More actions · Answer 4

mharr,

I'm using MSDE on my desktop. I 'm trying to emulate a remote server by specifying the local hard drive as oppossed to specifying a device that knows where it is automatically. I'm running the SQL Server Services using the local Adminsitrator account on my desktop. Will it work or does it have to be a domain account?

Kindest Regards,

cliffb SSCarpal Tunnel Points: 4598 More actions · Answer 5

I may be wrong, but I beleive you have to have a backup device defined before running a backup through DMO. I have not worked with DMO however so I may just be blowing smoke. I'd try adding a backup device though, and then rerunning your code.

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 6

Since you are using a local path during your test, I would suspect that the SQL Service running under local Admin account would work OK (unless that account had its file permissions removed from that directory). However, when you change that path to a remote location, the local Admin will have no possible way to have rights on the remote server, and you will need to run the SQL Service under a domain account to have Windows handle the file permissions.

When you say "specifying a device", I assume you mean writing a backup to a backup device, instead of a file. You do not need to define a device for a remote backup, you can perform a backup using a UNC filename (e.g. \\servername\share\backup\mydata.bak). But you will need the SQL Service to be running under a domain account, as Windows will use that account for accessing the remote server (so that the remote server knows which account to check for file permissions).

Mark