none
Automatic process to backup and restore database security RRS feed

  • Question

  • Hello Community,

    I am working in a task to develop an automatic process to backup database from production and restore it in development, but I need to keep the database security of the development environment and drop the one that comes with the production backup

    For example, I have to dbTest, this would be the workflow that I need to follow:

    1. Backup production database.
    2. Backup development database security.
    3. Restore production database backup on development instance.
    4. Restore development database security.

    Basically, I need to backup and restore the security of the development database once the production backup finish restoring. I am using a SQL job since this will be executed daily.

    I know that there is the store procedure sp_help_revlogin but as far as I know this will script the security at an instance level, and I would only needed for dbTest.

    Please let me know you, suggestions or solutions about this.

    If you have a question just let me know


    • Edited by Asolano016 Tuesday, September 17, 2019 3:37 PM
    Tuesday, September 17, 2019 3:22 PM

Answers

All replies

  • Script out  your database users;

    restore your database;

    drop users from your restored database;

    recreate your database users from your script from your step one.

    You can find different ways to implement this process.

    It should be implemented automatically run with a scheduled job.

    Tuesday, September 17, 2019 3:53 PM
    Moderator
  • How do you script the databases users and the role assignment in the database? Do you have a template to do that?
    Tuesday, September 17, 2019 3:56 PM
  • For each step, you can search to find solutions.

    If I would to do today, I will give dbatools a try and use powershell to get a schedule job to run a few steps to restore the production database locally and replace all users in the database.

    Another way is to script out what you need for each step and use the script for each step you need to get the database refreshed with your requirements.

    you need to work on each step and if you have question any step, you can ask for help.

    Do you need someone to post a whole solution for you? I cannot share my working code with you for various reasons.


    Tuesday, September 17, 2019 4:05 PM
    Moderator
  • I just did the whole solution for the backup and restore of the database, I´m not asking for the solution for my problem, I´m asking if there is a template that I can use as a base to develop a solution to backup the security of the database using TSQL, in order to added to my SQL job execution. Guess I will try to create my own template.
    Tuesday, September 17, 2019 4:11 PM
  • Search your question key words and you find many samples.

    Step 1: Get script to script your current database users;

    step 2: restore your database;

    step 3:Get a script to drop your restored database users in the database;

    step 4: Use your script from step 1 to recreate your local database users.

    When all your scripts are tested, you can bundle together to set a job to run them.

    Tuesday, September 17, 2019 4:18 PM
    Moderator
  • Thanks for your reply, but I already know the workflow. I found this template that somebody maybe could use to do this possible.

    https://www.sqlservercentral.com/articles/automatically-scripting-out-permissions-for-cross-environment-restores

    • Marked as answer by Asolano016 Wednesday, September 18, 2019 1:06 PM
    Wednesday, September 18, 2019 1:06 PM