Backing up with SQL MO

**Tom Moreau** · Oct 21 '06, 10:25 AM

Re: Backing up with SQL MO

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" <bbb@bbb.comwro te in message
news:mOSdnVIlx_ AOb6TYnZ2dnUVZ8 tOdnZ2d@giganew s.com...
Hi,

I'm trying to programmaticall y 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 = BackupActionTyp e.Database
m_Backup.Backup SetDescription = "Backup of zzz"
m_Backup.Backup SetName = "zzz Backup"
m_Backup.Databa se = m_Source.Catalo g

tempPath = Application.Loc alUserAppDataPa th + "\backup_zzz.ba t"

theDeviceItem = New BackupDeviceIte m(tempPath, DeviceType.File )

m_Backup.Device s.Add(theDevice Item)
m_Backup.Checks um = True
m_Backup.Contin ueAfterError = True
m_Backup.Increm ental = False
m_Backup.Expira tionDate = New Date(2006, 10, 5)
m_Backup.LogTru ncation = BackupTruncateL ogType.Truncate
m_Backup.MediaD escription = "file backup"
m_Backup.Initia lize = True
m_Backup.Percen tCompleteNotifi cation = 10
m_Backup.Restar t = True
m_Backup.Retain Days = 5

m_Backup.SqlBac kup(theServer)

Any thoughts?

Thanks

Robin

**Robinson** · Oct 21 '06, 11:15 AM

Re: Backing up with SQL MO

"Tom Moreau" <tom@dont.spam. me.cips.cawrote in message
news:uh3R4zP9GH A.2268@TK2MSFTN GP05.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** · Oct 21 '06, 12:05 PM

Re: Backing up with SQL MO

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

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"Robinson" <bbb@bbb.comwro te in message
news:qa-dnS4a_ubzmKfYnZ 2dnUVZ8qidnZ2d@ giganews.com...

"Tom Moreau" <tom@dont.spam. me.cips.cawrote in message
news:uh3R4zP9GH A.2268@TK2MSFTN GP05.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** · Oct 21 '06, 08:25 PM

Re: Backing up with SQL MO

"Tom Moreau" <tom@dont.spam. me.cips.cawrote in message
news:eOAw9sQ9GH A.3264@TK2MSFTN GP04.phx.gbl...

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
>
--
Tom
>

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

**Tom Moreau** · Oct 21 '06, 09:45 PM

Re: Backing up with SQL MO

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" <bbb@bbb.comwro te in message
news:F6WdnTCw8I brGKfYRVnyrQ@gi ganews.com...

"Tom Moreau" <tom@dont.spam. me.cips.cawrote in message
news:eOAw9sQ9GH A.3264@TK2MSFTN GP04.phx.gbl...

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
>
--
Tom
>

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

**Robinson** · Oct 22 '06, 03:45 PM

Re: Backing up with SQL MO

"Tom Moreau" <tom@dont.spam. me.cips.cawrote in message
news:%23CaqhvV9 GHA.3344@TK2MSF TNGP03.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\SQLEXPRES S'.
(Microsoft.SqlS erver.Express.S mo)

For help, click:
http://go.microsoft.com/fwlink?ProdN...r&LinkId=20476

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

System.Data.Sql Client.SqlError : Cannot open backup device 'd:\robs.bak'.
Operating system error 5(error not found). (Microsoft.SqlS erver.Express.S mo)

For help, click:
http://go.microsoft.com/fwlink?ProdN...0&LinkId=20476

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

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

**Tom Moreau** · Oct 22 '06, 08:55 PM

Re: Backing up with SQL MO

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" <bbb@bbb.comwro te in message
news:5v2dndjnXf VQCKbYnZ2dnUVZ8 t2dnZ2d@giganew s.com...

"Tom Moreau" <tom@dont.spam. me.cips.cawrote in message
news:%23CaqhvV9 GHA.3344@TK2MSF TNGP03.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\SQLEXPRES S'.
(Microsoft.SqlS erver.Express.S mo)

For help, click:
http://go.microsoft.com/fwlink?ProdN...r&LinkId=20476

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

System.Data.Sql Client.SqlError : Cannot open backup device 'd:\robs.bak'.
Operating system error 5(error not found). (Microsoft.SqlS erver.Express.S mo)

For help, click:
http://go.microsoft.com/fwlink?ProdN...0&LinkId=20476

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

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

**Robinson** · Oct 23 '06, 08:05 AM

Re: Backing up with SQL MO

"Tom Moreau" <tom@dont.spam. me.cips.cawrote in message
news:eZ4wf4h9GH A.2316@TK2MSFTN GP04.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** · Oct 23 '06, 10:45 AM

Re: Backing up with SQL MO

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" <bbb@bbb.comwro te in message
news:naCdndX6_c P15qHYRVnysg@gi ganews.com...

"Tom Moreau" <tom@dont.spam. me.cips.cawrote in message
news:eZ4wf4h9GH A.2316@TK2MSFTN GP04.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

Backing up with SQL MO

Backing up with SQL MO

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment