Link to home
Start Free TrialLog in
Avatar of marrowyung
marrowyung

asked on

restore production DB with replication setup to a test platform

hi all,

I just found out script to help on refresh DB from production to test platform where replication is set on production, we have script to:

1) restore the DB from production subscriber to test platform.
2) exec sp_removedbreplication to remove replication from restored test platform.
3) run sp_change_users_login to remove orphaned-users from tested platform as the user right of production and test platform is not going to be the same.

but by this link:
1) http://www.sqlservercentral.com/Forums/Topic1129289-291-1.aspx
2)https://ask.sqlservercentral.com/questions/106838/how-to-delete-a-publication-after-distributor-is-d-1.html

the order seems to be :

1)Backup and restore the publisher DB on our dev/test environments. If we have replication setup in these environments we first drop the existing replication setup before doing this. When we drop the existing replication setup we take care of removing everything - all publications/subscriptions are removed, distribution DB is dropped and distributor is removed.

2)Run sp_removedbreplication to get rid of all the replication related settings on the restored publisher DB

3) Configure replication in our dev/test environments using the restored publisher DB as the publisher.

and the detail steps on remove the replication setup are :
a) Run sp_droppullsubscription on each of the subscribers for each of the subscriptions (for pull subscriptions - this is not needed for a push subscription)
b) Run sp_dropsubscription on the publisher for each of the subscribers and each of the publications
c) Run sp_droppublication on the publisher for each of the publications
d) Run sp_replicationdboption on the publisher to set the publish option to false
e) Remove the distributor setup - run sp_dropdistributor, sp_dropdistributiondb and sp_dropdistpublisher on the distributor

so the order should be
1) restore the subscriber DB to the TEST DB server first.
2) run exec sp_removedbreplication on the TEST DB server, which is the restored subscriber

Am I right? but the links above seems remove the replication before restore ,which seems doesn't make any REAL sense at all, as it will kill production replication which is running, right?

So just one command : exec sp_removedbreplication, on restored test DB can do all removal ?

how about we need to create the same thing back to the test platform, I mean setup the same replication?
Avatar of marrowyung
marrowyung

ASKER

please note that we are using merge replication but not transaction replication, might be this comes the diff ?
Avatar of Ryan McCauley
You wouldn't remove replication in production - you're right that would cause an issue with your active replication configuration. To do this, I'd take the backup, restore it in your test environment, and then remove and reconfigure replication on that database.

Are you running into an error, or just asking how it should be done before you try it?
"To do this, I'd take the backup, restore it in your test environment, and then remove and reconfigure replication on that database.
"

on test platform, we don't have replication, so we should not recreate the replication on test platform. so what should be that ?

"Are you running into an error, or just asking how it should be done before you try it? "

how it should be done before I try it.
anyway to script out the user permission before we refresh the TEST DB From production ?

this one is not working :

http://www.sqlservercentral.com/scripts/Security/71562/

when executing it, it said:

Msg 207, Level 16, State 1, Line 63
Invalid column name 'authentication_type'.
Msg 207, Level 16, State 1, Line 63
Invalid column name 'authentication_type'.
ASKER CERTIFIED SOLUTION
Avatar of Ryan McCauley
Ryan McCauley
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
"It looks like that field was added in SQL Server 2012, so if you're using 2008, you don't have it in the system table. "

it said  "It seems to work on SQL 2005-2014," WOWOW it doesn't work.

is that means if I change as according to your suggestion and later on change back to SQL 2012/2014, it doesn't work again ?

" To correct the script, modify lines 67-69 from the following:"

so the whole thing will be this:

SELECT '-- [-- DB USERS --] --' AS [-- SQL STATEMENTS --],
3 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT 
('IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] = ' + SPACE(1) + '''' + [name] + '''' + ') BEGIN CREATE USER ' + SPACE(1) + QUOTENAME([name]) + ' FOR LOGIN ' + QUOTENAME(suser_sname([sid])) + ' WITH DEFAULT_SCHEMA = ' + QUOTENAME(ISNULL([default_schema_name], 'dbo')) + SPACE(1) + 'END; ') 
AS [-- SQL STATEMENTS --],
3.1 AS [-- RESULT ORDER HOLDER --]
FROM sys.database_principals AS rm
WHERE [type] IN ('U', 'S', 'G') -- windows users, sql users, windows groups

Open in new window


now the output seems ok.

one thing, what script you are using handy/internally for this kind of task ?
it seems don't take care role permission, am I right?
We don't actively synchronize our logins between production and our testlab, so we don't have a script we use for this. This appears that it should work as expected, though.

This script will handle role membership, but not permissions. However, the permissions for each database will be moved with the databases themselves, so there's no need to refresh these independently - as long as the user and role SIDs match (which this would handle), then you'll be fine.
"as long as the user and role SIDs match (which this would handle), then you'll be fine. "

this is good and that's why sometimes only this one is ok:

sp_change_users_login, it map diff SQL login once restored.

"We don't actively synchronize our logins between production and our testlab"

not login  but the whole DB from production to test, then fix the permission diff between pro and test
The database permissions are contained within the database itself, so permissions will automatically be migrated when you move things into test (if you're moving a database backup). If a user with SID 0x3 had ddl_admin membership when you backed the database up, that same user will have ddl_admin when you restore the database.

I think you're good to go here.
"The database permissions are contained within the database itself, so permissions will automatically be migrated when you move things into test (if you're moving a database backup)."

first of all, this is not a migration, I think this is true as long as the master DB MIGRATE to the TEST server too but it is not, agree?

for migration point of view you are right, as I also did so many time and as long as the production user DB as well as production master DB all goes to the TEST server, then there will be ok.

What if the TEST server's login and permission always diff from production one, then there will be a problem.

we just have a problems here and before I come problem and everything the time production DB refresh to TEST, TEST SQL server will have a lot of TSET SQL user can't login/permission changed.

because the MASTER DB of TEST DB hasn't change but user DB keep refreshing from production.
this one fit our need. tks for help on fixing thisl