Moving SQL 2005 databases with no downtime between servers

25 pts.
Tags:
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?
ASKED: September 10, 2008  10:14 PM
UPDATED: September 11, 2008  2:03 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following