Moving DBs to new Server
-
I have been tasked with moving all of the SQL Server databases from an old server to a new one. I was hoping to simply do a sp_detach, copy the mdf & ldf files to the new server, then reattach them. However, we are using mix-mode authentication and the handful of sql user logins we use fail to login on the new server during testing. So, I attempted to create the users under the security/logins section prior to attaching the database but, that did not work either. Is there an easy/fast way to migrate the database to the new server with out using DTS? Application Error Log File which probably isn't helpful: SQL Errors: SqlError: 1 Error #: 4060 Procedure: Line # 0 Server: TheSqlServer Source: .Net SqlClient Data Provider Message: Cannot open database requested in login 'TheDatabase'. Login fails. SqlError: 2 Error #: 18456 Procedure: Line # 0 Server: TheSqlServer Source: .Net SqlClient Data Provider Message: Login failed for user 'SqlServerLogin'. Thanks, Michael I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious. Vince Lombardi (1913-1970)
-
I have been tasked with moving all of the SQL Server databases from an old server to a new one. I was hoping to simply do a sp_detach, copy the mdf & ldf files to the new server, then reattach them. However, we are using mix-mode authentication and the handful of sql user logins we use fail to login on the new server during testing. So, I attempted to create the users under the security/logins section prior to attaching the database but, that did not work either. Is there an easy/fast way to migrate the database to the new server with out using DTS? Application Error Log File which probably isn't helpful: SQL Errors: SqlError: 1 Error #: 4060 Procedure: Line # 0 Server: TheSqlServer Source: .Net SqlClient Data Provider Message: Cannot open database requested in login 'TheDatabase'. Login fails. SqlError: 2 Error #: 18456 Procedure: Line # 0 Server: TheSqlServer Source: .Net SqlClient Data Provider Message: Login failed for user 'SqlServerLogin'. Thanks, Michael I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious. Vince Lombardi (1913-1970)
in the database list, right click -> all tasks -> generate sql script... With this, you can select that Enterprise Manager creates scripts for the users using the database, try it. daniero