This week in my test environment I created to SQL servers and a MOSS front end server. I installed MOSS and just did a very basic configuration on the first SQL Server. Then I backed up all the databases, saved them to my web front end, and shut down SQL Server 1. I booted up SQL Server 2 with the same name, IP and SQL Server instance and restore my databases into this server. I performed an IIS reset on my MOSS front end and tried to hit the websites. I received an error message that the server could not connect to the databases. After thinking about it for a few minutes, I thought SQL Security, MOSS automatically sets up security on the databases when you do configuration. The databases have security set up from the restore…but those users don’t have logins set up in SQL. I open up SQL Management studio and added the domain logins I used to set up MOSS, performed another IIS reset on my MOSS front end, just to be sure, and hit my Central Admin, Shared Services and a Web Application I created. All of them came right up and worked beautifully.
Moral of the story…if you think you want to move your SharePoint databases, the best way is to get a new server, set it up with identical network and SQL information to the old one, put your databases on the new server and get rid of the old one.
