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

  • Comments posted to this topic are about the item A Self-Serve System that Allows Developers to Refresh Databases (Part 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)

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

    Ben

  • 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.

  • 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

  • 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! 🙂

  • 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

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • 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

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

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

  • 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 1 year, 6 months ago by  John Waclawski. Reason: Fixed my signature which was broken
  • 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

  • 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!!

  • 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.

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

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