July 6, 2010 at 7:36 am
Afternoon all,
I have a querry and possibly a slight problem, which I am hoping someone can aleviate for me.
I have a server with a SQL instance configured and a 3rd party application that uses a SQL authentication login.
On this server (SQL1 from this point on) I have database backups that run every evening as normal. I have also tested them restoring the database to a CLEAN (newly created) database and confirmed it works fine, on the same server instance.
Now the issue I have is this. I have been given a 2nd server for full DR and they have installed a SQL instance there. there are no configurations done on it, it is completely clean.
If i do the restore of the backup Dump onto that instance my 3rd part application can not see the database or access it, from the 3rd party applications side the new instance name was configured with its ports etc etc.
What I suspect (having not studied SQL merely learning it as I go along) is that the SQL authentication is not within the backup dump and thus not getting onto the new instance. Is there a way to backup those permissions as well with the backup dump? and how is it done?
Forgive me if this seems elementary to everyone here 😀 Im just very much a JNR as far as SQL goes 😎
Thanks for any and all assist.
July 6, 2010 at 8:27 am
google sp_help_revlogin, you need to bring across the login which is held in the master database, not the application database.
---------------------------------------------------------------------
July 6, 2010 at 8:41 am
OK cools that works. Glad to see I was probably right on this then 😛
I now need to somehow add this into the backups Im running then
the
EXEC sp_help_revlogin
and add it to the backup Dump file.
how do I do that???? if I can.
or even have a seperate plan that runs this statment and outputs the data to a file that can be used in conjunction with it then?????
Thanks for the help 😀
July 6, 2010 at 9:40 am
you cannot add it to the backup file.
use your plan B and output the results to a file then run that into SSMS on the DR server.
---------------------------------------------------------------------
July 6, 2010 at 10:02 am
Sweet 🙂
Thanks again for the assist 🙂
and now atleast have a better idea where the stuff comes from 🙂
July 6, 2010 at 10:07 am
note: edit the resultant script to remove the logins that already exist (builtiun\admins, the SQL2005 local accounts)
---------------------------------------------------------------------
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply