Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
LOOKUP_BI
Constraint Violating Yak Guru
295 Posts |
 Posted - 2011-08-31 : 17:39:36
|
| There is always a need to sync the non production environment with the production environment before any migration (code deployment) in non production environment. Example we need to restore db from Prod to Dev.I know how to script the sql users in dev database before the database gets refreshed, but how do I map the permissions back as it used to be in Dev after the db restore from Prod and drop any login's that were not in Dev before the restore.How can I do this, as Im spending a lot of time each time there is database restore request to manually ensure all permission are the same before and after the restore. But there has to be a better way of doing this. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-01 : 03:08:44
|
| In case relevant we do this differently - i.e. we start from a different point, rather than restoring PRODUCTION to NON-PRODUCTION:The deployment to a new client starts with a TEST database / system. Our staff and Client's staff test it / configure it / put in their test data.When it is all tested and ready to "go live" we copy the TEST database to PRODUCTION (e.g. Backup & Restore) and then:Adjust the permissions on the PRODUCTION databases (remove our staff perhaps, add more Client staff)Remove test data (such as Customers and Orders) but not other data - such as Products that the client had set up on TEST.When we launch a new version we:Copy all "useful" data from Production to Test - in our case this would be Products etc. so that the test database had the latest product data. We do NOT copy Customer / Order data as this is a) sensitive and not suitable for our DEVs to see and b) runs the risk that someone does an EMail-run which sends out to real customers - which would be embarrassing! We have a script for this (mechanically generated) which only includes the appropriate tables, and UPDATEs any rows that are different, INSERTs new rows and DELETEs stale rows.The Test Database then contains useful / current data for testing against the new version of our application. We take a backup of the test database at this point (before upgrading test) for use later in Staging.Next we upgrade the Test database - applying our latest schema changes, Sprocs, meta-data, application, etc. and perform configuration for the Client (e.g. CMS, and set up Client config for any new features that have been added etc. - e.g. we may ahve added new Carriage methods, and that needs new client-specific carriage rules setting up)Our staff and Client test the TEST databases. Once that is signed off:We then restore the backup made earlier to a STAGING database. (If the testing period has been more than a few days we re-copy the Product etc data from PRODUCTION to STAGING [the database schemas are the same at this point]) We apply our new version scripts, and scripts we have made to copy Client's config data, changes to data for new features, etc. for TEST to STAGING. This is to prove our rollout process.The Client then performs a sign-off on STAGING. We require that they visit every page, and approve it, in the website (we have monitoring within the application to detect which pages they have not yet approved)Once that is done we apply the same scripts to deploy to the PRODUCTION database.In terms of permissions - because we do not restore PRODUCTION to NON-PRODUCTION we don't have to re-create permissions, we are maintaining the NON-PRODUCTION database for that purpose (and freshening it up with any data it needs from PRODUCTION in order to make it "current"). Client can use the TEST / NON-PRODUCTION database at any time for ad-hoc testing - e.g. a new carriage rate business rule - before they then create that "live" on the Production system. |
 |
|
|
|
|
|
|
|
Restore sql user and permission after Db restore