A Self-Serve System that Allows Developers to Refresh Databases (Part 1)

Question

A Self-Serve System that Allows Developers to Refresh Databases (Part 1)

bkubicek

SSChampion

Points: 10948
More actions
May 2, 2019 at 12:00 am

#3515336

Comments posted to this topic are about the item A Self-Serve System that Allows Developers to Refresh Databases (Part 1)

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

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

Barry G Freeman SSC Eights! Points: 942 More actions · Answer 1

I had a similar problem some time ago. The Business was not happy to allow the Extended SP's to be enabled.

I use this to get the latest backup for a given database rather than folder scanning. It give the physical location that you can plug into the restore statement.

SELECT TOP (1)
       bs.backup_finish_date
     , bmf.physical_device_name
     , cast(bs.backup_size / 1024 / 1024 / 1024 AS decimal(8, 2))            AS Size
     , cast(bs.compressed_backup_size / 1024 / 1024 / 1024 AS decimal(8, 2)) AS [Size on Disk]
FROM msdb.dbo.backupset             AS bs
    JOIN msdb.dbo.backupmediafamily AS bmf
        ON bmf.media_set_id = bs.media_set_id
WHERE bs.database_name = 'MyDatabase'
      AND bs.type = 'D'
ORDER BY bs.backup_finish_date DESC;

DBA (Dogsbody with Bad Attitude)

bkubicek SSChampion Points: 10948 More actions · Answer 2

Thanks Barry for sharing that code. I might be able to use that in the future.

Ben

krypto69 SSChampion Points: 13604 More actions · Answer 3

I don't think you guys should publish a 'stay tuned' article. Wait till it's done, then put the whole thing up. What good are the six queries if they are only part of this post???. Dumb IMO.

This does no one any good...teasers suk.

bkubicek SSChampion Points: 10948 More actions · Answer 4

Actually, all three articles are done and I submitted them all at the same time. I think the next one is getting published next week.

Ben

simmonssql Old Hand Points: 301 More actions · Answer 5

I'll be interested to read the rest of the process. Coincidentally, today I've been writing an Azure Automation Runbook to do something similar! 🙂

bkubicek SSChampion Points: 10948 More actions · Answer 6

That is great you are doing something similar for Azure. Pretty sure Part 2 comes out next week on Thursday and Part 3 the Thursday after that. Glad you are enjoying the series.

Ben

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 7

Heh... there must be a disturbance in the force. I've been asked to do something similar about 2 weeks ago. Because I have to kill a bunch of data and make it so not only will there be a refresh from prod but have to allow the devs to drop and restore a given point in time so they can do comparisons between a copy with old methods on it (current production methods) and the new methods they want to test, I'm going to have to do things a bit differently.

We're also looking at a cloning feature for the refresh from prod that's built into our SAN.

Just a thought for killing all connections... I don't recommend the "Kill All Spids" method. Instead, I recommend setting the database to single user with the ROLLBACK IMMEDIATE option... nothing is missed that way and nothing that should be killed is.

Anyway, thanks for writing this series.

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

bkubicek SSChampion Points: 10948 More actions · Answer 8

Hi Jeff,

Thanks for the suggestion about not killing all the spids. In my situation we have services that are running that are keeping the test ERP systems in sync with plant floor systems. I have had quite a few issues getting restores working when these services keep trying to connect and see if there is anything to do.

Ben

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 9

I had similar issues and the single user thing seemed to handle that. You DO have to be careful to not lose the single user connection or those service logins will definitely grab it and life becomes painful. From the sounds of it, though, I'm preaching to the choir there. 😀

With that in mind, I normally do the single user thing and follow that immediately with going back to multi-user followed immediately by the restore. I've not yet had a service login get in the middle of that and, of course, they can't log into the database that's being restored.

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

Barry G Freeman SSC Eights! Points: 942 More actions · Answer 10

After warning users, I tend to just set the database offline with rollback and then online. Sure the database might refuse to come back online, but in that case it probably has serious issues anyway. I've found it more reliable than set single user, and doesn't have the disadvantage of leaving the database inaccessible.

In the case of an app constantly trying to re-connect, I just make sure my two alter database statements are run immediately before the restore statement so in all likelihood it'll grab exclusive access before some persistent app.

DBA (Dogsbody with Bad Attitude)

John Waclawski SSCommitted Points: 1505 More actions · Answer 11

I've been writing a webapp for years that monitors current backup\restores of all our production\development environments. Below is a screenshot of what that screen looks like:

backup-restore screen

Something like what this article describes could be incorporated into my webapp.

Also our "Database Holds" tab could be incorporated somehow too:

Database_holds

I look forward to the rest of this article.

This reply was modified 5 years, 6 months ago by John Waclawski. Reason: Fixed my signature which was broken

bkubicek SSChampion Points: 10948 More actions · Answer 12

Thanks John for sharing the screen shots. The next article should get published this Thursday and the final one a week after that. I am afraid my website example is very bare bones and not all that pretty, but it does work. Thanks for your comment.

Ben

John Waclawski SSCommitted Points: 1505 More actions · Answer 13

No problem. Your code could very well be what I'm looking for to get started on a self-help database restore section of my app.

Thanks again!!

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 14

I tend to set the database to the RESTRICTED_USER mode, I think it avoids the problem with SINGLE_USER mode since there are far fewer people who have SYSADMIN, DB_OWNER, or DB_CREATOR membership.