Home > IT Answers > Database > Moving SQL 2005 databases with no downtime between servers
Theitman
25 pts.
 Moving SQL 2005 databases with no downtime between servers
Database mirroring, Microsoft SQL Server Management Studio, SQL Server 2005, SSMS
We have 2 SQL 2005 servers and would like to move the database and the logins and passwords from server to server. I ran backup and restored the full database with no recovery mode which allow me to copy the transaction logs then I transfered the logons and ran sp_change_users_login to associate a server login with database user login. Now I need to turn on mirror and since the new server database is not in restore mode, I cannot run the mirror between the 2 servers. Any solutions please?

Software/Hardware used:
ASKED: September 10, 2008  10:14 PM
UPDATED: September 11, 2008  2:03 PM
RELATED QUESTIONS
Answer Wiki:
You'll need to drop the database, and restore the database again to get mirroring working. Drop all the logins as well before restoring the database. Go to MSKB <a href="http://support.microsoft.com/kb/918992">918992</a> and install sp_help_revlogin on the current system. Then run sp_help_revlogin. This will give you the SQL Scripts needed to create the logins using the same SIDs as were used on the original system. You can then restore the database, and ship the logs from the source to the new server, then setup mirroring as normal.
Last Wiki Answer Submitted:  September 10, 2008  10:18 pm  by  Denny Cherry   64,520 pts.
All Answer Wiki Contributors:  Denny Cherry   64,520 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

Check out my SQL Server blog “SQL Server with Mr Denny” for more SQL Server information.

Denny Cherry
 64,520 pts.