Backing up with SQL MO

R

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
 
T

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
..
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
 
R

Robinson

Tom Moreau said:
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
Click to expand...

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.
 
T

Tom Moreau

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

Tom Moreau said:
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
Click to expand...

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.
 
T

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

Tom Moreau said:
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
Click to expand...
Thanks for that, but it's not much more use than the existing documentation
on MSDN to be honest. :/
 
R

Robinson

Tom Moreau said:
So changing the file and path in the example code didn't work? What error
did it give you?
Click to expand...

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?Prod...xceptionText&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
 
T

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

Tom Moreau said:
So changing the file and path in the example code didn't work? What error
did it give you?
Click to expand...

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?Prod...xceptionText&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
 
R

Robinson

Tom Moreau said:
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:?
Click to expand...

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
 
T

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

Tom Moreau said:
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:?
Click to expand...

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
 
Top