Backup and Restore to Different Database Servers

I need to programmatically (C# w/ smo) backup a database on one SQL server
and restore the backup on a different SQL server. The problem I have is that
I cannot guarantee that either SQL Server is running as a domain user (either
one may be running as Local System). This results in "Access Denied" errors
when attempting to backup to (or restore from) a spot one of the servers
cannot see. In this situation, is there any way to programmatically backup
and restore an entire database from one SQL server to another?
Thanks.The backup file has to "land" somewhere. I.e., produced by one instance and read by the other
instance. And those instance need permissions to write/read the backup file.
Of course, you can have an intermediate party pick up the file written by source instance and
copying it to somewhere else where then the destination instance need permissions. Now the
intermediate party need permissions on both these locations.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"stevep" <stevep@.discussions.microsoft.com> wrote in message
news:5907BBED-870F-4928-9B92-842E2A99DA17@.microsoft.com...
>I need to programmatically (C# w/ smo) backup a database on one SQL server
> and restore the backup on a different SQL server. The problem I have is that
> I cannot guarantee that either SQL Server is running as a domain user (either
> one may be running as Local System). This results in "Access Denied" errors
> when attempting to backup to (or restore from) a spot one of the servers
> cannot see. In this situation, is there any way to programmatically backup
> and restore an entire database from one SQL server to another?
> Thanks.

Backup and Restore to Different Database Servers

I need to programmatically (C# w/ smo) backup a database on one SQL server
and restore the backup on a different SQL server. The problem I have is tha
t
I cannot guarantee that either SQL Server is running as a domain user (eithe
r
one may be running as Local System). This results in "Access Denied" errors
when attempting to backup to (or restore from) a spot one of the servers
cannot see. In this situation, is there any way to programmatically backup
and restore an entire database from one SQL server to another?
Thanks.The backup file has to "land" somewhere. I.e., produced by one instance and
read by the other
instance. And those instance need permissions to write/read the backup file.
Of course, you can have an intermediate party pick up the file written by so
urce instance and
copying it to somewhere else where then the destination instance need permis
sions. Now the
intermediate party need permissions on both these locations.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"stevep" <stevep@.discussions.microsoft.com> wrote in message
news:5907BBED-870F-4928-9B92-842E2A99DA17@.microsoft.com...
>I need to programmatically (C# w/ smo) backup a database on one SQL server
> and restore the backup on a different SQL server. The problem I have is t
hat
> I cannot guarantee that either SQL Server is running as a domain user (eit
her
> one may be running as Local System). This results in "Access Denied" erro
rs
> when attempting to backup to (or restore from) a spot one of the servers
> cannot see. In this situation, is there any way to programmatically backup
> and restore an entire database from one SQL server to another?
> Thanks.

Backup / Restore script

One of my coworkers needs to programmatically ask a bunch of different SQL
Servers for the default location of where they are configured to put .BAK
files.
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave MustaneMike,
Use the BackupDirectory key.
HTH
Jerry
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:ebE3t010FHA.404@.TK2MSFTNGP09.phx.gbl...
> One of my coworkers needs to programmatically ask a bunch of different SQL
> Servers for the default location of where they are configured to put .BAK
> files.
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "When you kill a man, you're a murderer.
> Kill many, and you're a conqueror.
> Kill them all and you're a god." -- Dave Mustane
>|||> Use the BackupDirectory key.
I'm not too sure what you mean, but if you mean a Windows Registry Key,
that's no good in this example. He's writing an automated backup utility
that will tell many different SQL Servers via ODBC to backup their
databases. Since his application does not run on the actual SQL servers, he
doesn't have access to their registries.
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane|||Mike,
Ok...try the SQL-DMO Backup Directory Property (in SQL BOL).
HTH
Jerry
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:OeYlsB20FHA.2888@.TK2MSFTNGP10.phx.gbl...
> I'm not too sure what you mean, but if you mean a Windows Registry Key,
> that's no good in this example. He's writing an automated backup utility
> that will tell many different SQL Servers via ODBC to backup their
> databases. Since his application does not run on the actual SQL servers,
> he doesn't have access to their registries.
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "When you kill a man, you're a murderer.
> Kill many, and you're a conqueror.
> Kill them all and you're a god." -- Dave Mustane
>|||I agree that using DMO is the way to go. In case you absolutely can't do tha
t, you can use the
*unsupported and undocumented* xp_instance_regread extended proc. All usual
warnings apply:
http://www.sqldev.net/misc/SQLLocationFunctions.htm
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23aVzyR20FHA.2964@.TK2MSFTNGP10.phx.gbl...
> Mike,
> Ok...try the SQL-DMO Backup Directory Property (in SQL BOL).
> HTH
> Jerry
> "Mike Labosh" <mlabosh@.hotmail.com> wrote in message news:OeYlsB20FHA.2888
@.TK2MSFTNGP10.phx.gbl...
>