Hi,

I'm trying to programmatically backup a database (SQL 2005).  I get a
"Device not found" error when I specify a full user given path in the
backup, but when I just specify a filename it works, putting the backup into
the SQL folder: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup.
What I want to do is have the backup written to the local user data folder.
My code looks like this (VB.NET):


theServer = New Server(m_Source.Server)

m_Backup = New Backup

m_Backup.Action = BackupActionType.Database
m_Backup.BackupSetDescription = "Backup of zzz"
m_Backup.BackupSetName = "zzz Backup"
m_Backup.Database = m_Source.Catalog

tempPath = Application.LocalUserAppDataPath + "\backup_zzz.bat"

theDeviceItem = New BackupDeviceItem(tempPath, DeviceType.File)

m_Backup.Devices.Add(theDeviceItem)
m_Backup.Checksum = True
m_Backup.ContinueAfterError = True
m_Backup.Incremental = False
m_Backup.ExpirationDate = New Date(2006, 10, 5)
m_Backup.LogTruncation = BackupTruncateLogType.Truncate
m_Backup.MediaDescription = "file backup"
m_Backup.Initialize = True
m_Backup.PercentCompleteNotification = 10
m_Backup.Restart = True
m_Backup.RetainDays = 5


m_Backup.SqlBackup(theServer)


Any thoughts?


Thanks


Robin

Let's clarify this a bit.  Is this code being run on, say, WORKSTATIONA and
the SQL Server is on SERVERB?  If so, the way to write the backup to
WORKSTATIONA is:

1)    Have SQL Server on SERVERB running under a domain account.
2)    Have a share on WORKSTATIONA
3)    Grant read/write privileges to the share for the account in #1 above.
4)    Specify the full UNC name to the backup file on WORKSTATIONA when you
give it the path: \\WORKSTATIONA\MyShare\MyFile.bak

I'm trying to programmatically backup a database (SQL 2005).  I get a
"Device not found" error when I specify a full user given path in the
backup, but when I just specify a filename it works, putting the backup into
the SQL folder: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup.
What I want to do is have the backup written to the local user data folder.
My code looks like this (VB.NET):


theServer = New Server(m_Source.Server)

m_Backup = New Backup

m_Backup.Action = BackupActionType.Database
m_Backup.BackupSetDescription = "Backup of zzz"
m_Backup.BackupSetName = "zzz Backup"
m_Backup.Database = m_Source.Catalog

tempPath = Application.LocalUserAppDataPath + "\backup_zzz.bat"

theDeviceItem = New BackupDeviceItem(tempPath, DeviceType.File)

m_Backup.Devices.Add(theDeviceItem)
m_Backup.Checksum = True
m_Backup.ContinueAfterError = True
m_Backup.Incremental = False
m_Backup.ExpirationDate = New Date(2006, 10, 5)
m_Backup.LogTruncation = BackupTruncateLogType.Truncate
m_Backup.MediaDescription = "file backup"
m_Backup.Initialize = True
m_Backup.PercentCompleteNotification = 10
m_Backup.Restart = True
m_Backup.RetainDays = 5


m_Backup.SqlBackup(theServer)


Any thoughts?


Thanks


Robin

Show quote Hide quote Hi, sorry I should have put it into context.  This is on a single developer
machine at present.   It seems SQLMO backup only wants to write to the
default backup location and only allows me to specify a filename, not a full
file path.  So I'm using the Server.Settings object to find that default
location and then move it across to some user defined location.  I was
rather hoping to avoid the move and just write it out there in the first
instance.

Check out page 7 of this:

http://download.microsoft.com/documents/australia/teched2005/hol/HOL068.pdf

It's bare metal but it works. Try cloning from it as a starting point.

HTH

Show quoteHide quoteHi, sorry I should have put it into context.  This is on a single developer
machine at present.   It seems SQLMO backup only wants to write to the
default backup location and only allows me to specify a filename, not a full
file path.  So I'm using the Server.Settings object to find that default
location and then move it across to some user defined location.  I was
rather hoping to avoid the move and just write it out there in the first
instance.

Thanks for that, but it's not much more use than the existing documentation
on MSDN to be honest.  :/

So changing the file and path in the example code didn't work? What error
did it give you?

Show quoteHide quoteThanks for that, but it's not much more use than the existing documentation
on MSDN to be honest.  :/

Hi Tom,

It must be some kind of permissions error, even though I'm a local
administrator (this is a stand-alone developer machine in any case), because
I'm getting the same error in SQL Management Studio when I try to perform a
backup (and SMS uses SQL-MO anyway), unless the directory I specify for the
location of the backup media is the MSSQL backup directory in Program Files.
The error information from management studio looks like this (pasted dialog
text below).  Now the strange thing is, I'm getting an error "not found",
but I know it's there because I selected it from the folder browser dialog
Management Studio presented to me.  I've tried various other locations too,
with no joy.

Robin







TITLE: Microsoft SQL Server Management Studio Express
------------------------------

Backup failed for Server 'ROBS\SQLEXPRESS'.
(Microsoft.SqlServer.Express.Smo)

For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Backup+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: Cannot open backup device 'd:\robs.bak'.
Operating system error 5(error not found). (Microsoft.SqlServer.Express.Smo)

For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

Under what account is SQL Server running?  Have you tried creating a domain
account and running SQL under that - after granting read/write  permissions
on drive D:?

Hi Tom,

It must be some kind of permissions error, even though I'm a local
administrator (this is a stand-alone developer machine in any case), because
I'm getting the same error in SQL Management Studio when I try to perform a
backup (and SMS uses SQL-MO anyway), unless the directory I specify for the
location of the backup media is the MSSQL backup directory in Program Files.
The error information from management studio looks like this (pasted dialog
text below).  Now the strange thing is, I'm getting an error "not found",
but I know it's there because I selected it from the folder browser dialog
Management Studio presented to me.  I've tried various other locations too,
with no joy.

Robin







TITLE: Microsoft SQL Server Management Studio Express
------------------------------

Backup failed for Server 'ROBS\SQLEXPRESS'.
(Microsoft.SqlServer.Express.Smo)

For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Backup+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: Cannot open backup device 'd:\robs.bak'.
Operating system error 5(error not found). (Microsoft.SqlServer.Express.Smo)

For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

Where do I find out which account 2005 is running under?

Anyway, I can't write a backup to C drive either (i.e. to my desktop) - I'm
not on a domain and I am running as administrator on this PC.  I've
implemented a Restore database dialog also using SMO, and it let me pick up
backups from anywhere.

Robin

Use SQL Server Configuration Manager.  I'd still create a user account on
your PC, grant it read/write privileges to the target folder and re-try.

It doesn't matter that you are an admin.  It matters what privileges SQL
has.

Where do I find out which account 2005 is running under?

Anyway, I can't write a backup to C drive either (i.e. to my desktop) - I'm
not on a domain and I am running as administrator on this PC.  I've
implemented a Restore database dialog also using SMO, and it let me pick up
backups from anywhere.

Robin

Best way to store dataset to disk???
Query results to file
Transparent BackGround
Newb looking for data binding help
Datasets and Adapter Updates
how to translate from c# this instruction
Deleteing all files from target folder as a presetup task
Webbrowser control - buffering - audio/video playback problem...
Using VB and ADO.NET
Building my own "SMTP" mail - control ?