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?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

marrowyungSenior Technical architecture (Data)Author Commented:
please note that we are using merge replication but not transaction replication, might be this comes the diff ?
0
Ryan McCauleyData and Analytics ManagerCommented:
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?
0
marrowyungSenior Technical architecture (Data)Author Commented:
"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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

marrowyungSenior Technical architecture (Data)Author Commented:
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'.
0
Ryan McCauleyData and Analytics ManagerCommented:
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. To correct the script, modify lines 67-69 from the following:

CASE WHEN rm.authentication_type IN (2, 0) /* 2=contained database user with password, 0 =user without login; create users without logins*/ THEN 
     ('IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] = ' + SPACE(1) + '''' + [name] + '''' + ') BEGIN CREATE USER ' + SPACE(1) + QUOTENAME([name]) + ' WITHOUT LOGIN WITH DEFAULT_SCHEMA = ' + QUOTENAME([default_schema_name]) + SPACE(1) + ', SID = ' + CONVERT(varchar(1000), sid) + SPACE(1) + ' END; ')
ELSE ('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; ') 
END AS [-- SQL STATEMENTS --],

Open in new window


To instead be this:

('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 --],

Open in new window


I'm just removing the check for the type of login to create and having it create a matching one in all cases, so that should work.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marrowyungSenior Technical architecture (Data)Author Commented:
"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 ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
it seems don't take care role permission, am I right?
0
Ryan McCauleyData and Analytics ManagerCommented:
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.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"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
0
Ryan McCauleyData and Analytics ManagerCommented:
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.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"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.
0
marrowyungSenior Technical architecture (Data)Author Commented:
this one fit our need. tks for help on fixing thisl
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.