January 31, 2017 at 3:37 am
Hi , I don't want to break my logged ship DB obviously I cannot use
exec sp_change_users_login 'Update_One', 'myuser', 'myuser' as it's in standby.
Can I do the following :
1. Stop jobs alert, copy and restore
2.
USE [master]
GO
ALTER DATABASE [mydb]SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
--Will recover the database after all log files applied
RESTORE DATABASE [mydb] WITH RECOVERY
GO
--User mapping
USE [mydb]
GO
exec sp_change_users_login 'Update_One', 'myuser', 'myuser'
How do I now get the DB back into a state where I can restart my log shipping jobs?
Many thanks
January 31, 2017 at 3:42 am
Edward-445599 - Tuesday, January 31, 2017 3:37 AMHi , I don't want to break my logged ship DB obviously I cannot use
exec sp_change_users_login 'Update_One', 'myuser', 'myuser' as it's in standby.
Can I do the following :
1. Stop jobs alert, copy and restore
2.
USE [master]
GO
ALTER DATABASE [mydb]SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
--Will recover the database after all log files applied
RESTORE DATABASE [mydb] WITH RECOVERY
GO
--User mapping
USE [mydb]
GO
exec sp_change_users_login 'Update_One', 'myuser', 'myuser'
How do I now get the DB back into a state where I can restart my log shipping jobs?
Many thanks
I don't think that this would work. If you modify anything in the database, you break the log shipping and you'll have to recreate it using a backup of the source database. I think that you have 2 options. The first is to create the login in the target server using the same SID as the one in the source server. Check this URL - https://support.microsoft.com/en-us/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server.
The second option that I have to admit that I never tried, but I think that it should work is to use contained databases.
Adi
8kY8m�
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 31, 2017 at 4:48 am
Adi Cohn-120898 - Tuesday, January 31, 2017 3:42 AM
Hi Adi to add complexity my target server has a number of DB's with the login already in use and has schemas associated with the user. So I would have to transfer the schema and delete the users then do your steps..as this is production this isn't a great option. contained databases arnt in 2008?
Any other ideas?
February 2, 2017 at 8:56 am
Does your target get its DB's from multiple sources?
If so do any of the sources share the same SQL Login names?
If not and its a 1 source to 1 target then using sp_help_revlogin would be the answer.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply