April 9, 2008 at 9:23 am
I have to reinstall our instances tomorrow due to problems with Reporting Services, my intention is to detatch the user databases (after a backup) and then uninstall the application. Is there anything else I need to do before reinstalling the software i.e. delete any folders, backup jobs etc?
Thanks
April 9, 2008 at 10:11 am
msdb you can restore from backup to get jobs, etc. Or you can script out jobs.
Script out logins. I'd search for sp_help_revlogin from MS and that will get you a script you can run after reinstalling.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 10, 2008 at 5:38 am
Would it be advisable to restore any other system databases? Also, once I have uninstalled the instance is it safe for me to delete the SQL Server folders before reinstalling?
Thanks
April 11, 2008 at 3:01 am
If you aer re-installing to the same folders, there is no need to delete them between the uninstall and the new install.
You should not restore any other system databases apart from msdb. Personally I would prefer to script out anything I wanted from the old msdb and script into the new one, but doing this compared to restoring msdb is probably just personal preference.
You definitely need to script out all logins. Be aware that if you use SQL logins they will get a different SID when you recreate them. This will cause the database users based on those logins to become orphans, and you will need to resolve this after re-attaching your user DBs. This problem does not occur if you use Windows logins.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
April 11, 2008 at 3:15 am
Well, I can say that all appeared to go well but a word of warning to others who may think of doing the same - the process of uninstalling and reinstalling took a lot longer than I had anticipated! It may have been down to my environment but it took a couple of hours!
Thanks for the helpful posts, scripting out the logins saved a lot of time because it would have been a nightmare to have to set all of those back up manually.
April 11, 2008 at 3:19 am
I would say that 2 hours to uninstall and reinstall SQL 2005 is good! I find that the uninstall can easily take 40 minutes, and the install along with SP and CU can take 90 minutes or more.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
April 11, 2008 at 3:22 am
Solve your orphaned user problem using the following script -
SELECT UserName = name
FROM sysusers
WHERE issqluser = 1
AND sid is not null
AND sid <> 0x0
AND suser_sname(sid) is null
ORDER BY name
EXEC sp_change_users_login 'update_one', 'user', 'login'
You can also write a stored procedure, but it is better to check it before you do it, and one at a time to avoid any adverse situation.
Chandrachurh Ghosh
DBA – MS SQL Server
Ericsson India Global Services Limited
Quality is not an act, it is a habit.
May 5, 2009 at 1:41 pm
Is there an sp_help_revlogin yet for 2008? Can someone provide a link? I can't find it on the Mickeysoft site.
May 6, 2009 at 8:49 am
I believe the sp_help_revlogin for 2005 generates the SID's for you, so you don't have to worry about orphaned user ids.
Tim White
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply