0

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!!

Community
  • 1
  • 1
Joe Raio
  • 1,795
  • 1
  • 12
  • 17
  • possible duplicate of [Best way to copy a database (SQL Server 2008)](http://stackoverflow.com/questions/2043726/best-way-to-copy-a-database-sql-server-2008) – Pondlife May 06 '13 at 22:26

1 Answers1

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'
Community
  • 1
  • 1
Joe Raio
  • 1,795
  • 1
  • 12
  • 17