backup remote database to local hard drive or local database?

3 Jul 2006 5:02 AM

Mark Huebner

Are there any tools that would allow me to make a backup of a remote SQL
Server database and save it to my local computer's hard drive or my local
computer's SQL Server 2000/2005 Express database? I also want to be able to
restore a remote SQL Server database from a backup file/database on my local
computer. I currently have SQL Server Management Studio Express installed
on my local computer. Although it allows me to view and edit my remote
database, it doesn't appear to be capable of copying the remote database to
my local computer for backup purposes.

3 Jul 2006 10:34 AM

Andrea Montanari

hi Mark,
Mark Huebner wrote:

> Are there any tools that would allow me to make a backup of a remote
> SQL Server database and save it to my local computer's hard drive or
> my local computer's SQL Server 2000/2005 Express database? I also
> want to be able to restore a remote SQL Server database from a backup
> file/database on my local computer. I currently have SQL Server
> Management Studio Express installed on my local computer. Although
> it allows me to view and edit my remote database, it doesn't appear
> to be capable of copying the remote database to my local computer for
> backup purposes.

the "browse for destination" dialog in the backup dialog task only show
"local" resources (where local resources stay for resources local to the
server file system), thus you can not select a folder out of the server's
file system... but you can "force" the destination editing by hand the
backup-file, say "\\otherpc\share\db.bak" , or you can perform the very same
task with a Transact-SQL script..
but, the account running the SQL Server services must be able to access the
remote share, so you have to grant him both share and NTFS permissions on
that share... SQL Server Express usually runs under "LocalSystem" or
"NetworkService" account, and those accounts are usually never granted this
kind of permissions.. once you set the account running SQL Server (SQL
Express does not provide the SQL Server Agent) with enought permissions, you
can then backup your remote database to your local share not in the server's
file system...
obviously do not use a domain adiministrator account, but use a windows or
domain account with the least privileges as possibile that meet your
requirements .. http://msdn2.microsoft.com/en-us/library/ms143504.aspx

--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.19.0 - DbaMgr ver 0.63.0 and further SQL Tools
--------- remove DMO to reply

3 Jul 2006 6:55 PM

Mark Huebner

The backup task in my SQL Server Management Studio Express does not have a
"browse for destination" dialog. Which software utility are you referring
to? I'm trying to backup the SQL Server 2000 database for my DotNetNuke web
site that is hosted by www.gate.com.

Show quoteHide quote

"Andrea Montanari" <andrea.sql***@virgilio.it> wrote in message
news:4gsa5jF1onqvdU1@individual.net...
>
> the "browse for destination" dialog in the backup dialog task only show
> "local" resources (where local resources stay for resources local to the
> server file system), thus you can not select a folder out of the server's
> file system... but you can "force" the destination editing by hand the
> backup-file, say "\\otherpc\share\db.bak" , or you can perform the very
> same task with a Transact-SQL script..
> but, the account running the SQL Server services must be able to access
> the remote share, so you have to grant him both share and NTFS permissions
> on that share... SQL Server Express usually runs under "LocalSystem" or
> "NetworkService" account, and those accounts are usually never granted
> this kind of permissions.. once you set the account running SQL Server
> (SQL Express does not provide the SQL Server Agent) with enought
> permissions, you can then backup your remote database to your local share
> not in the server's file system...
> obviously do not use a domain adiministrator account, but use a windows or
> domain account with the least privileges as possibile that meet your
> requirements .. http://msdn2.microsoft.com/en-us/library/ms143504.aspx
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz http://italy.mvps.org
> DbaMgr2k ver 0.19.0 - DbaMgr ver 0.63.0 and further SQL Tools
> --------- remove DMO to reply
>

3 Jul 2006 11:35 PM

Andrea Montanari

hi Mark,
Mark Huebner wrote:

> The backup task in my SQL Server Management Studio Express does not
> have a "browse for destination" dialog. Which software utility are
> you referring to? I'm trying to backup the SQL Server 2000 database
> for my DotNetNuke web site that is hosted by www.gate.com.

I was using SQL Server Management Studio Express..
open the Backup dialog.. push the <Add> button and a "Select backup
destination" dialog will appear.. you have to manually edit the path (file
name property) you want to backup to.. if you push the <...> (with ellipses)
button a browsing dialog appears, named "Locate Database Files -
InstanceName"..

--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.19.0 - DbaMgr ver 0.63.0 and further SQL Tools
--------- remove DMO to reply

4 Jul 2006 5:15 AM

Mark Huebner

In SQL Server Management Studio Express when I right click on the database
and select Tasks\Back Up, I get a bunch of errors like "EXECUTE permission
denied on object 'xp_instance_regread', database 'master', owner 'dbo' ...
probably because the hosting company has not set me up with 'dbo'
privledges. The account I am logging in with does not have db_accessadmin,
db_backupoperator, db_denydatareader, db_denydatawriter, and db_owner role
membership. What do you recommend that I do? I'm pretty sure that the
hosting company does not want to give me db_owner privledges. Can this
problem be solved by the hosting company or would you recommend trying a
different backup/restore utility?

Show quoteHide quote

"Andrea Montanari" <andrea.sql***@virgilio.it> wrote in message
news:4gtnulF1om6frU1@individual.net...
> hi Mark,
> Mark Huebner wrote:
>> The backup task in my SQL Server Management Studio Express does not
>> have a "browse for destination" dialog. Which software utility are
>> you referring to? I'm trying to backup the SQL Server 2000 database
>> for my DotNetNuke web site that is hosted by www.gate.com.
>
> I was using SQL Server Management Studio Express..
> open the Backup dialog.. push the <Add> button and a "Select backup
> destination" dialog will appear.. you have to manually edit the path (file
> name property) you want to backup to.. if you push the <...> (with
> ellipses) button a browsing dialog appears, named "Locate Database Files -
> InstanceName"..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz http://italy.mvps.org
> DbaMgr2k ver 0.19.0 - DbaMgr ver 0.63.0 and further SQL Tools
> --------- remove DMO to reply
>

6 Jul 2006 4:03 PM

Andrea Montanari

hi Mark,
Mark Huebner wrote:

> In SQL Server Management Studio Express when I right click on the
> database and select Tasks\Back Up, I get a bunch of errors like
> "EXECUTE permission denied on object 'xp_instance_regread', database
> 'master', owner 'dbo' ... probably because the hosting company has
> not set me up with 'dbo' privledges. The account I am logging in
> with does not have db_accessadmin, db_backupoperator,
> db_denydatareader, db_denydatawriter, and db_owner role membership. What
> do you recommend that I do? I'm pretty sure that the hosting
> company does not want to give me db_owner privledges. Can this
> problem be solved by the hosting company or would you recommend
> trying a different backup/restore utility?

you have to "require" at least db_backuperator permissions, as you have to
be able to execute your own backup at point in time (say you want to "test"
something, you'd probably first backup, test and eventually revert [and here
db_owner database role or dbcreator server role membership is required])...
I guess they already defined scheduled backup their self, but if it's me,
I'd request db_owner membership as my db is "my" db, and I'd like to perform
whatever administrative task on that db :D
if you do not have backup permissions, you have no control on backup
strategies..

--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.19.0 - DbaMgr ver 0.63.0 and further SQL Tools
--------- remove DMO to reply

3 Jul 2006 3:32 PM

Geoff N. Hiten

HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/kb/555128

The article is detailed with steps for SQL 2000, but the concepts work for
SQL 2005.

--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP

Show quoteHide quote

"Mark Huebner" <m***@IntelligentSoftwareSystems.com> wrote in message
news:egwCN3lnGHA.1248@TK2MSFTNGP05.phx.gbl...
> Are there any tools that would allow me to make a backup of a remote SQL
> Server database and save it to my local computer's hard drive or my local
> computer's SQL Server 2000/2005 Express database? I also want to be able
> to restore a remote SQL Server database from a backup file/database on my
> local computer. I currently have SQL Server Management Studio Express
> installed on my local computer. Although it allows me to view and edit my
> remote database, it doesn't appear to be capable of copying the remote
> database to my local computer for backup purposes.
>