Migrating multiple SQL Server 2005 databases to SQL Server 2008

Question

I have a development server running SQL Server 2005 with 75+ databases on it. I will be installing a new SQL Server 2008 and want to migrate all the databases over to this server.

Does anyone have any suggestions as to the best approach in doing this? Normally I would just backup the database, restore it on the new server, then re-create the SQL login for that database. I was wondering if there was a more stream-lined approach so that this wont take me forever.

I already searched and found this article but I'm not sure it would apply: Attach multiple databases using T-SQL

Thank you!!

possible duplicate of Best way to copy a database (SQL Server 2008) — Pondlife
– Pondlife, Commented May 6, 2013 at 22:26

Community · Accepted Answer · 2017-05-23 11:45:52Z

1

I found the following article which helped out tremendously - Best way to copy a database (SQL Server 2008)

The specific part of the post that helped me had to do with the orphaned users. I used the following code and completed the job successfully.

List orphaned users:

EXEC sp_change_users_login 'Report'

If you already have a login id and password for this user, fix it by doing:

EXEC sp_change_users_login 'Auto_Fix', 'user

If you want to create a new login id and password for this user, fix it by doing:

EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'

edited May 23, 2017 at 11:45

CommunityBot

11 silver badge

answered May 6, 2013 at 21:55

Joe Raio

1,8051 gold badge13 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Migrating multiple SQL Server 2005 databases to SQL Server 2008

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related