Database Backup via T-SQL

Question

Database Backup via T-SQL

vinceknockaert

SSC Rookie

Points: 27
More actions
March 15, 2012 at 3:21 am

#253057

Hi,
We want our software users to be able to click some button or select some menu-option in our application, that enables the user to take a backup from the SQL Server database and getting that BAK file local, without having to grant that user specific access to folders on the SQL Server backup directory.
A way to do this, is using the BACKUP DATABASE command. This creates the BAK file in the SQL Server Backup folder. (the client does not even need to have permissions on that folder)
With the OPENROWSET(BULK..SINGLE BLOB ) command, it's possible to insert that BAK file in a varbinary field in the database.
After that, with a simple SELECT statement, the client can read the entire backup from the varbinary field and write it locally.
For the OPENROWSET command, SQL Server demands that the requesting user has access to that backup folder. (due to security impersonation). That is, only when the user connects to SQL Server with Windows Authentication.
When the user uses a SQL Server user to login (which has Bulkadmin rights), the OPENROWSET bulk import works fine.
Problem : we only work with Windows Authentication.
Are there any other workarounds to get the BAK file at the client without having to grant him specific access rights ?
Thanks in advance,
Vincent

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ken McKelvey SSCoach Points: 18882 More actions · Answer 1

I am not sure if this process this is a good idea. (Data security etc)

If you really want to do it, you could look at creating a job on the server, with a user having adequate rights, to copy the file to the varbinary column. You could then start the job, poll the column until it was available, and then download the data to a file on the local machine.

CapnHector SSCoach Points: 16143 More actions · Answer 2

I wonder why he would need the backup. Would not an SQL select statement to.csv or similar work better. Unless the ENTIRE database is needed for some reason? Why I can't really know. I mean that can be a nightmare for security reasons because the entire database would be available to the end-user.

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

Jeff Moden SSC Guru Points: 1004534 More actions · Answer 3

Create a backup job and give the users the privs to run the job.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

vikingDBA SSCertifiable Points: 5356 More actions · Answer 4

You could use a UNC path in the backup script:

BACKUP DATABASE MyDatabase TO DISK='\\somepc\c$\SQLBackups\MyDatabase_backup_201203011300.bak'

As long as the user has access to that path (through their Windows Authentication), it should work. Of course, that user should also have rights to back up a database.

I just tried it. It worked fine. Put it right on my PC.

This is normally not a good idea (connectivity, network speed, etc.), but in your case it may be the only possibility.

Hopefully not a large database.