Backing up with SQL MO

[Robinson]

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

[Tom Moreau]

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

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"Robinson" <bb*@bbb.comwrote in message
news:mO******************************@giganews.com ...
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

[Robinson]

"Tom Moreau" <to*@dont.spam.me.cips.cawrote in message
news:uh**************@TK2MSFTNGP05.phx.gbl...

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

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.

[Tom Moreau]

Check out page 7 of this:

http://download.microsoft.com/docume...hol/HOL068.pdf

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

HTH

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"Robinson" <bb*@bbb.comwrote in message
news:qa******************************@giganews.com ...

"Tom Moreau" <to*@dont.spam.me.cips.cawrote in message
news:uh**************@TK2MSFTNGP05.phx.gbl...

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

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.

[Robinson]

"Tom Moreau" <to*@dont.spam.me.cips.cawrote in message
news:eO**************@TK2MSFTNGP04.phx.gbl...

Check out page 7 of this:

http://download.microsoft.com/docume...hol/HOL068.pdf

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

HTH

--
Tom

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

[Tom Moreau]

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

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"Robinson" <bb*@bbb.comwrote in message
news:F6********************@giganews.com...

"Tom Moreau" <to*@dont.spam.me.cips.cawrote in message
news:eO**************@TK2MSFTNGP04.phx.gbl...

Check out page 7 of this:

http://download.microsoft.com/docume...hol/HOL068.pdf

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

HTH

--
Tom

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

[Robinson]

"Tom Moreau" <to*@dont.spam.me.cips.cawrote in message
news:%2****************@TK2MSFTNGP03.phx.gbl...

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

--
Tom

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?ProdN...r&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?ProdN...0&LinkId=20476

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

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

[Tom Moreau]

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:?

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"Robinson" <bb*@bbb.comwrote in message
news:5v******************************@giganews.com ...

"Tom Moreau" <to*@dont.spam.me.cips.cawrote in message
news:%2****************@TK2MSFTNGP03.phx.gbl...

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

--
Tom

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?ProdN...r&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?ProdN...0&LinkId=20476

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

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

[Robinson]

"Tom Moreau" <to*@dont.spam.me.cips.cawrote in message
news:eZ**************@TK2MSFTNGP04.phx.gbl...

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:?

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

[Tom Moreau]

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.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"Robinson" <bb*@bbb.comwrote in message
news:na********************@giganews.com...

"Tom Moreau" <to*@dont.spam.me.cips.cawrote in message
news:eZ**************@TK2MSFTNGP04.phx.gbl...

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:?

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