August 19, 2013 at 3:51 pm
Restoring Database From Prod to UAT and Not Restoring USER,ROLES, SCHEMAS
what methods and scripts is available to do that.
is there away to delete these users, roles and schemas before restoring into UAT server?
how to fix user roles mismatch when orphan user scripts does not fix everything
we want to keep UAT Users and Roles and not replace them with the new Database users and Roles from Prod
thanks
I appreciate guys
August 20, 2013 at 7:07 am
A backup is a page by page copy of the database. A restore is therefore also a page by page copy of the database. There is no way to modify the internals of a backup prior to running a restore. If you only want parts of a database then you're better off running an export of those parts and then importing them into a second database instead of running a restore.
For scripts to fix orphaned users, just Google 'sql server fixing orphaned user' and there are hundreds of examples.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 20, 2013 at 8:58 am
You need schemas. Otherwise how are objects structured? You need roles, in that security should be tested. If you want different security in UAT, then have a script to set that.
For users, if they're production users, what do you care? You can fix the orphans if you need to, and that's easy, but if you set up new users for testing, just script them and run that.
As Grant mentioned, a backup is a backup of everything. You don't get to choose what's restored. If you need to change things after the restore, then have a script ready to do that.
August 20, 2013 at 9:33 am
You could:
1) restore the db, and immediately issue a USE <restored_db> command and set the db to single user mode;
2) delete all the users, schemas, etc., that you don't want in the final db;
3) set the db back to multi-user and make it available to others.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply