November 17, 2011 at 9:47 am
My question is: Can I execute a BACKUP DATABASE TO DISK command to a folder on some server that is not local to the SQL Server?
My situation is this: I have multiple databases that are hosted in a shared environment along with my web hosting account. I can connect to these databases thru SSMS but I can only backup and restore each database manually thru the hosting company's control panel. I would like to write a Windows Service that runs locally, to automate my backups so that the backups are scheduled (ie daily) for all databases and the so the backups are maintained off that sql server. I can do everything except execute the BACKUP command. My hosting company will not allow me to execute a BACKUP command to the sql server's local disk. Because I can't provide a valid path, my attempts always throw this error:
Operating system error 3(The system cannot find the path specified.).
I've seen this question posted before, but I've never seen a solution. I'm on 2008R2.
Thanks.
November 18, 2011 at 6:07 am
You can do network backups. As long as the the SQL Server service account can see the location that you are backing up to, you're golden.
However, be sure of the connectivity of the network. Backups across a network have a reputation for being somewhat problematic unless the network is fast & reliable.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 18, 2011 at 6:18 am
the syntax you want is
backup database dbname to disk = '\\servername\sharename'
or
backup database dbname to disk = '\\servername\drive$\full path to file'
+ whatever other clauses you are using
---------------------------------------------------------------------
November 18, 2011 at 9:48 am
Thanks for your replies. I think I've got the backup command syntax. The part I don't understand is giving permission to the "SQL Server service account". How do I do that? I have complete control of the destination file server, and I have the connection string parameters of the SQL server. Is that enough?
November 18, 2011 at 10:11 am
the error you are getting is actually saying the path does not exist rather than it does not have permissions to it (that would be error 5).
however, the SQL service account can be determined from SQL configuration manager and needs to be a domain account so it has access across the network. The destination folder would then ideally be set up as a share and the SQL service account granted at least modify over the share (right click directory - security)
---------------------------------------------------------------------
November 18, 2011 at 10:24 am
Right. I understand the error -it's because I was resolving a server.mappath to some e:\... folder which is on the web server, but not local to the sql server.
Since I'm dealing with a web hosting provider, I can't just open up Configuration Manager on that SQL Server. Am I out of luck?
November 18, 2011 at 10:39 am
presuming you have access to SSMS try this:
declare @account varchar(50)
print 'MSSQLServer service account '
exec xp_regread 'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\Services\MSSQLServer', 'Objectname', @account output
print @account
...failing that, ask them 🙂
---------------------------------------------------------------------
November 18, 2011 at 12:02 pm
I'm getting permission denied to exec that. Would that be a bad security exposure for a hosting company to provide that service account name to a customer? If I can get it, what would I do to give that server permission to write to my file server?
November 18, 2011 at 12:43 pm
joe-1142036 (11/18/2011)
I'm getting permission denied to exec that. Would that be a bad security exposure for a hosting company to provide that service account name to a customer? If I can get it, what would I do to give that server permission to write to my file server?
thats up to the hosting company, or your management to agree with them whether you should know it. I would hope the hosting company have automated backups.
see previous posting for how to grant it access (presuming there is a trust between the domains)
---------------------------------------------------------------------
November 18, 2011 at 1:29 pm
my hosting company, for example, will do a backup and place it in the proper folder on my FTP site on demand; it's typically just an email away;
I'm fairlly certain you will have to do the same, that's the price of shared hosting.,not quite full access to the server.
ihostasp.net gave me dbo rights to my database, so i can do a backup, but once the backup is on some folder on the server, i cannot get to it from the webserver with my web account/ftp access anyway.
depending on the database size, you could put something together to do it the hardway and script out the objects and data...i'd just throw them an email.
Lowell
November 18, 2011 at 2:26 pm
Thanks everyone for your help. I'm going to conclude that it can't be done with this particular hosting company. As pointed out, that's the drawback of shared hosting. I'll get started on looking for a hosting solution where I can have backups automatically run for me, or where I'm on a dedicated server and have control.
November 18, 2011 at 2:29 pm
joe-1142036 (11/18/2011)
Thanks everyone for your help. I'm going to conclude that it can't be done with this particular hosting company. As pointed out, that's the drawback of shared hosting. I'll get started on looking for a hosting solution where I can have backups automatically run for me, or where I'm on a dedicated server and have control.
don't throw your hands up! whether you are with goDaddy or someone else, ANY hosting company will do your backup for you and give you access to it, so you can work with them to get a local copy of it.
Lowell
November 22, 2011 at 6:20 am
i had the same issue the other week, our hosting provider would only restore a DB back to the DB server as a DB, they wont place the BAK file on a web server so I can pull it down to do a development database refresh. Which I kicked off and said it was my data, but thats the limitations of their environment apparently.
So I posted a topic on here as they are not in a domain in the hosting provider but linked through a private network, so it was a combination of using xp_cmdshell and a few other commands, I will try and find the script I used and upload it.
[edit]
Found it, may not be complete as I have had to create it again
sp_configure 'xp_cmdshell',1
go
reconfigure
go
exec master.dbo.xp_cmdshell 'NET USE G: \\SERVER\Share /user:Machine/Domian\username password'
GO
backup database DBName to Disk = 'G:\PAth to bak'
GO
exec master.dbo.xp_cmdshell 'NET USE G: /DELETE
go
sp_configure 'xp_cmdshell',0
go
reconfigure
go
Where \\SERVER\Share is the UNC path to where you want to backup Machine/Domain\Username is the username of the machine or domain you want to connect as and PASSWORD is the password of the said account, dont have to use G: can use any available drive letter on the SQL server
For a local machine account
NET USE G: \\COMPUTER2\SQLBackup /USER:COMPUTER2\Administrator Password1
For a domain account
NET USE G: \\COMPUTER2\SQLBackup /USER:DOMAIN\Administrator Password1
[/edit]
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply