Creating a Script/Batch File for END USER to Backup

Question

Creating a Script/Batch File for END USER to Backup

chad-722159

SSC Rookie

Points: 44
More actions
September 1, 2009 at 9:37 am

#210156

**Relative Newbie Warning**
Monthly, in our organization, we do a financial "close." This requires a FULL backup of the ERP database to be run before and after.
This I typically do using SQL Server Management Studio, either while I'm here at the office, or I remote in and just launch it manually.
I'm looking to automate the process, and actually would like to create a batch file for the "closer" to run herself. I read the forums, I see the codes, and I'm frankly overwhelmed. While it seems there are a TON of useful tools, they aren't really **newbie** oriented. e.g., I need the question answered, "Ok, I see the code/script. Now what the heck do I do with it?" Do I copy and paste it into Notepad and save as .vbs, .sql ? Do I paste it directly into SSMS? How do I launch the script? Where do I specify the database name, etc?
Any help here is appreciated, even if you point me in the right direction for something I can read. Willing to do the work, but weaving through endless links isn't always productive.

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

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

Here's an example of how I've done it.

First, I made sure there is a specific role created that will let people do it; mine is very simple, because a web page on the intranet has a link that executes the process. in that case, the web page executes commands as the SQL user "webdev"

just like you've done, I let SSMS script out the appropriate command.

Then i put that command in a stored procedure.

the big thing is the stored procedure has the EXECUTE AS clause, as a normal user would not have permissions to do the backup. To make a procedure that has the EXECUTE AS, you needed to login as a sysadmin when you do the CREATE PROCEDURE command, i believe.

CREATE PROCEDURE BackUpProductionByNormalUsers

WITH EXECUTE AS OWNER

AS

BEGIN

BACKUP DATABASE [SandBox] TO DISK = N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\SandBox.bak'

WITH NOFORMAT, NOINIT, NAME = N'SandBox-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

END

after that I simply give EXECUTE permissions to the user or role that will be used to backup the database.

GRANT EXECUTE ON OBJECT::dbo.BackUpProductionByNormalUsers

TO YourRoleOrUser; --webdev in my case

so now the user webdev has permissions to backup the database... but only because of the EXECUTE AS command. now you can create a web page or a .bat file or some other processes, and all it has to do is connect as the user with permissions, and call the procedure.

I hope that helps.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

chad-722159 SSC Rookie Points: 44 More actions · Answer 2

Thanks Lowell !

I'll give that a try, sounds like a good way to do it without granting too much access to the database.

- Chad

Lowell SSC Guru Points: 323504 More actions · Answer 3

just following up;

you'll want to test this two ways; after granting permissions to the procedure, just like i showed, log into SSMS as one of those users and run the procedure...it should backup and display information like this:

11 percent processed.
22 percent processed.
31 percent processed.
42 percent processed.
51 percent processed.
62 percent processed.
71 percent processed.
82 percent processed.
91 percent processed.
Processed 280 pages for database 'SandBox', file 'SandBox' on file 2.
100 percent processed.
Processed 1 pages for database 'SandBox', file 'SandBox_log' on file 2.
BACKUP DATABASE successfully processed 281 pages in 0.158 seconds (14.569 MB/sec).

once you KNOW it works, it's easy to create, say , a simple web page to do the same work.

here's the body of an ASP page i made and tested:

<body>
<%
If Request.Form("BackupSandbox") = "Backup Sandbox" Then
   dim myconn 'AS Object
    dim rs  'AS Object
    dim sql 'as string
    dim results 'as string
    dim CurrentObject 'as string

    set myconn = server.createobject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")

    myconn.open "PROVIDER=SQLOLEDB;DATA SOURCE=D223\SQLEXPRESS;UID=webdev;PWD=NotaRealPassword;DATABASE=master;"
    sql = "exec BackUpProductionByNormalUsers"
    myconn.execute(sql)
  Response.Write("Backup Complete!")
Else
  Response.Write("Warning, Clicking this button will backup the database SandBox on the server D223\SQLExpress!")
%>
<form method=post action="">
<input type="submit"name="BackupSandbox" value="Backup Sandbox">
<%
End If
%>

</form>
</body>

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!