Restore pre-live database with live database

I'm in the process of restoring a pre-live database (containing 6 databases) with the live databases. All I need is the live data. However I'm guessing other things might be affected like like Replication, Jobs, Users, Roles and perhaps other components.

I would like to know how I would go about this process so that users, Replication and Jobs are not affected, only the data. I'm guessing the following:

Before Restoring

  • Generate a script that backs up the current users and roles (how?)
  • Stop Replication and Jobs.
  • Backup the pre-live databases,
  • Backup Replication (generate script) and Jobs (how?) - This may not be necessary

After Restoring
Run the script to restore the users and roles
Restart Jobs and Replication

I read somewhere to use EXEC sp_change_users_login 'Auto_Fix', 'someUser' to connect an orphaned user to the login.

Hi Paulus,

Before Restoring

  • Generate a script that backs up the current users and roles (how?)
    -------- Select Logins tab under security and press F7 to display all te logins under object explorer(right side)
    -------- Select all the logins and right click select script login as --> create --> option to create the Login script for all the ----------logins under the server

  • Stop Replication and Jobs.

What type of replication based on that jobs will be created under jobs folder in sql server Agent, right click on the jobs and select stop option

  • Backup the pre-live databases,
    -- Suggested to stop the traffic or connections to these databases before taking the backup because while connections are running if you will take the DB backup uncommited data will not backed up.
    --Also Please stop the replication as suggested above
  • Backup Replication (generate script) and Jobs (how?) - This may not be necessary
    -- No need to backup the replication jobs because when you are running replication scripts jobs will automatically created.

To backe up the Replication please select the publications under replication folder in SQL server and script the publications
Perform the same way for subscribers as well.

Once you are done with these things you cn drop the replication and rrstore the database.

Hi, Thanks for your reply.

Would I really need to make a backup of the Logins? Surely this would remain as I'm only restoring the databases, not the database server. Also I think the users and roles would persist.

How best to go about restoring replication and jobs...we have 13 replication and 34 jobs

Can you confirm these are the steps for me to take? Thanks
Nb 'Prod' replicates to 'web'

  1. Stop traffic or connections to pre-live 'Prod' databases
  2. Stop replication and jobs on pre-live 'Prod'
  3. Stop replication on 'web' pre-live (prod replicates to web)
  4. Backup pre live databases
  5. Backup replication publication by gerating scripts
  6. Backup jobs. There are many jobs. Would replication create jobs? I can script them all
  7. Drop replication
  8. Restore the databases
  9. recreate replication
    9 recreate the important logins (EXEC sp_change_users_login 'Auto_Fix', 'aUser' )

Paulus,
look up from the below:

  1. Stop traffic or connections to pre-live 'Prod' databases --Yes
  2. Stop replication and jobs on pre-live 'Prod'

when you drop the replication for related Databases which you are restoring , sql jobs also deleted when you drop the replication.

  1. Stop replication on 'web' pre-live (prod replicates to web) -- Drop the replication means traffic also would be stopped
  2. Backup pre live databases --YES
  3. Backup replication publication by gerating scripts -- Before you drop the replication only you have to backup(script)
  4. Backup jobs. There are many jobs. Would replication create jobs? I can script them all -- No need to backup the replication jobs as they are depending on replication creation and drop the replication.
  5. Drop replication -- Yes
  6. Restore the databases --Yes
  7. recreate replication -- Yes run the replication scripts to create
    9 recreate the important logins (EXEC sp_change_users_login 'Auto_Fix', 'aUser' ) -- It is may not be mandatory but suggested to run this script to resolve orphand users issues if any exist.
1 Like