How do I make a database backup and have it stored locally?

Question

So here's the challenge. I need to use C# to back up a database locally from a remote database server and the only share we have is \\computer\c$.

If the answer is to back up to a location on the remote database server and then copy that local, assume there is not a share on the remote location. Just b/c I have access to the database does not mean I have access to the file system.

This Q/A looks close to what I want, but remember the regular account in the database will not have access to the network. Sample sql script to zip and transfer database backup file

With all of that in mind...anyone done something like this and care to share? Or have an idea of how to do this?

UPDATE: I was being vague on purpose. The context here is this:

1. I do not have access to files on the server where the database is.
2. I am running the C# from the client under the client's credentials, so I have full control of the client computer and can do anything in this domain.

I think I actually have figured out what we are going to do though:

1. Create a special folder in the AppData area of the current user.
2. Create a fileshare to that folder
3. Add rights to Everyone for that particular share and folder.
4. Process the backup based on that share.
5. Remove the share and reset the permissions.

In case anyone is curious what this question refers to: RoundhousE

Answer 1

think I actually have figured out what we are going to do:

1. Create a special folder in the AppData area of the current user.
2. Create a fileshare to that folder
3. Add rights to Everyone for that particular share and folder.
4. Process the backup based on that share.
5. Remove the share and reset the permissions.

In case anyone is curious what this question refers to: RoundhousE

Answer 2

If you want/need to back up a database, then in general, for the purpose of guaranteed database consistency, the database server will need to be either completely shut down, or at least be completely in read-only mode.

Your question sounds like your "challengers" have taken you to task to back up a remote server while none of those conditions are satisfied. The only sensible answer to such a challenge is that it cannot be done.

Answer 3

Context? Oracle? MySQL? SQL Server? Db Size? Backup time? Availability requirements? Encryption requirements? Rowcount validation?

I'm guessing you're talking about SQL Server, based on the TSQL example- to which I'd recommend checking out DMO objects. Here's a primer- http://www.codersource.net/csharp_sqldmo_sqlserver.aspx.

Truthfully- the vague nature of your question- and the fact that it's context free gives me some heebie jeebies. Be sure to test in a test envrionment and not run on production data, ok?

Answer 4

The backup system of SQL Server runs the entire backup procedure on the server, which basically means that the file has to be put somewhere the database server can put it and has access to.

In other words, the file will be written from the server.

If you:

1. Cannot access files directly on the server, from the client, in any way
2. Cannot download files from the server, to the client, in any way
3. Cannot write files from the server, to a common place, that is reachable by the client in any way

Then you are left with one way: Reimplement backup.

If your actual question is this:

With all of that in mind...anyone done something like this and care to share?

Then I'm going to hazard a guess that the answer to this question is: No.

Are you sure you cannot change the problem to one that is solvable?