Automating backup/restore for a non DBA-user

Question

Automating backup/restore for a non DBA-user

chrisph

Hall of Fame

Points: 3701
More actions
October 14, 2015 at 2:15 pm

#321909

Guys, is there an automatic method a user could employ to specify which database they want backed up/restored from a one instance to another? Thinking something like a powershell script. The issue is the user would not be allowed sysadmin permissions on the production instance.

Viewing 15 posts - 1 through 14 (of 14 total)

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

you can create a stored procedure that does EXECUTE AS OWNER, which will backup, restore etc.

in my case, I have end users that can do excel or a web page as a max skill set, so powershell would be out of the question.

i created a simple web page that any person in the right domain group can go to to restore a database from the latest full backup; all it does is call my stored proc, which found the database and location form the msdb backup history and resotores it as a specific database.

i've also got another example, where i created a stored proc/w execute as owner, which calls a job.

the job has multiple steps to backup and restore.

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!

chrisph Hall of Fame Points: 3701 More actions · Answer 2

Could you please share the details of this stored proc?

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

chrisph (10/14/2015)
Guys, is there an automatic method a user could employ to specify which database they want backed up/restored from a one instance to another? Thinking something like a powershell script. The issue is the user would not be allowed sysadmin permissions on the production instance.

Why would you allow a user to do either? What is the criticality here?

--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)

chrisph Hall of Fame Points: 3701 More actions · Answer 4

Jeff I don't like the idea because of security and other implications of permissions but am exploring all options.

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 5

chrisph (10/14/2015)
Jeff I don't like the idea because of security and other implications of permissions but am exploring all options.

Understood but why is it necessary for the user to do either a backup or restore? I'm more curious than anything.

--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)

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 6

Jeff Moden (10/14/2015)
chrisph (10/14/2015)
Jeff I don't like the idea because of security and other implications of permissions but am exploring all options.
Understood but why is it necessary for the user to do either a backup or restore? I'm more curious than anything.

Jeff, you beat me to it. I can't think of a good reason I would allow a user to do this on a PROD system.

CEWII

Kristen-173977 SSCrazy Eights Points: 8687 More actions · Answer 7

Our accounts department (all of two people, both part time 🙂 ) uses an accounting package that requires the user to do backups / restores. Its definitely not using a SQL database of any flavour I know ... might even be ISAM.

Anyway, the accounts team do a backup when they finish a "batch" of work. To them its a logical work unit, and if they have to do a restore it would represent a "STOP AT" point in time to restore to.

They definitely do do restores - e.g. to check state of accounts as-at a previous month end etc. - whereafter they then re-restore the current working state, which they backed-up before they did the prior month-end restore.

They also have a Test Area, which they can restore to if they want to try out a new version of the software or whatever.

Very old-style ... I would expect a modern system to be able to report on a previous-month-end-state, if that was a requirement, without needing a database restore. I just mention it as an example of a use-case where user-controlled Backup/Restore might be worthwhile.

I would also expect a SQL-based APP to have scheduled, regular, backups ... perhaps with a user-controlled restore option (and possibly with a forced (and "named") LOG backup at the point of a "completely unit of work" to make a restore to that point easier for the user than having to remember the specific STOP AT time).

Just thinking out loud though ... we don't have anything remotely like that in production in our APPs 😎

Ed Wagner SSC Guru Points: 287024 More actions · Answer 8

I have to agree - I don't think I would ever want anyone other than a DBA doing a database restore in production. The thought of accidentally overwriting a production database that's in use makes me cringe in more ways than I can count.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Lowell SSC Guru Points: 323504 More actions · Answer 9

in my case, we have a "reporting" database the end users have access to; but sometimes they want the freshest data.

they don't have access to production, so my simple solution backs up production, restores elsewhere, and runs a script to add users and roles.

it's just automating a task that i would do manually otherwise.

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!

chrisph Hall of Fame Points: 3701 More actions · Answer 10

Thanks Lowell, I'm looking for the actual script itself though.

To clarify: the procedure would only backup from production, thinking only would need db_backupoperator permissions for the databases, and restore in a staging/test environment.

Ant-Green SSC Guru Points: 113445 More actions · Answer 11

Does it need to be a backup at the moment in time of Prod? Can it not be the last full/diff backup that you do as your daily/weekly routines?

chrisph Hall of Fame Points: 3701 More actions · Answer 12

Yes it needs to be at a moment in time, hopefully copy-only backups.

Lowell SSC Guru Points: 323504 More actions · Answer 13

well obviously, the backup has to be available between both machines.

you can simply start a backup job and script it.

you get results something like this:

BACKUP DATABASE [ProductionDb]

TO DISK = N'W:\SQLBackup\ProductionDb_MonthEnd.bak'

WITH COPY_ONLY,

NOFORMAT,

INIT,

NAME = N'ProductionDb-Full Database Backup',

SKIP,

NOREWIND,

NOUNLOAD,

COMPRESSION,

STATS = 10

now, i would kick off a job that does the restore on the the Test server via a linked server

EXECUTE( 'EXECUTE msdb.dbo.sp_start_job @job_name = 'Restore UserAnalysis DB') AT MyLinkedTestServer

and then that job just calls a tsql or procedure with the restore script and adding of users/roles.

you really need to script it , because you have to know the internal object names.

--drop it!

IF EXISTS(SELECT * FROM master.sys.databases WHERE name='UserAnalysis')

BEGIN

ALTER DATABASE [UserAnalysis] SET OFFLINE WITH ROLLBACK IMMEDIATE;

DROP DATABASE [UserAnalysis]

END

--restore it!

RESTORE DATABASE [UserAnalysis]

FROM DISK = '\\ProductionDb\w$\SQLBackup\ProductionDb_MonthEnd.bak'

WITH RECOVERY,

MOVE 'ProductionDb' TO 'W:\SQLData\UserAnalysis.mdf',

MOVE 'ProductionDb_log' TO 'W:\SQLLogs\UserAnalysis_1.ldf'

--add users!

EXEC('USE UserAnalysis; Create USER [ClarkKent] FOR LOGIN [ClarkKent] ')

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!

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 14

There are a whole bunch of unknowns here.

First, are there any clear text SSNs/TaxIDs or other personally identifiable information (PII) in the database that needs to be obfuscated? Of course, that would be a problem to begin with (it should be encrypted) but would be a much worse problem in having that info in a dev/reporting database. Do you want to read about yourself in the morning news after you get hacked or some pissed off developer/user runs off with your data?

What about user names in the restored database? Should they be modified or dropped? Are there users that should be added to the database? Is the fact that the db owner SID will likely change going to cause a problem for anything?

Are there any external references to other databases or servers in the form of 3 or 4 part naming? Any synonyms? Any "pass through views or functions"?

Last but not least, the fact that someone is actually asking for a script to do this tells me that there are a whole lot of things that haven't been considered and the task should probably not be done until the people that will need to support it in the future actually have the knowledge to support it. That's not a slam on anyone... it's just a hard fact of life.

--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)