Continued from page 1
Attaching Database
1. Right-click database, select 'All Tasks', and then click 'Attach Database…'. 2. Navigate to data file by clicking button that has 3 dots on it (…). 3. Once you've selected database .mdf file, you'll see data file path (which should be verified for both data and transaction log file) and have option to choose database name and owner. Attach it as database name, and ensure owner name is 'sa'. 4. Verify path for transaction log file is correct. If path isn’t correct, update it to appropriate directory. 5. Click ‘OK’.
Note: If you didn't move transaction log file to new server with data file, you'll be prompted to create a new one. If you choose to create a new one, it will be done for you automatically.
Orphaned Users
If database user doesn't exist on new server, create it now. When creating it, you may receive an error that it already exists, but that's ok, work that needs completed by SQL Server to add user account is done. After you receive error that user already exists, press cancel and continue on to next user.
Moving database to a new server will result in orphaned users. You will need to run a stored procedure that will map user in database to a user on server. This stored procedure will change relationship between a Microsoft SQL Server login and a SQL Server user in current database. Basically, it changes SID in database to match one generated by local server, which allows all custom permissions to be retained - it remembers access and permissions.
1. Open Query Analyzer. 2. Select database in drop-down box on tool bar if it isn't already selected. 3. For each database user, we'll need to execute command: "sp_change_users_login 'update_one', 'db_username', 'db_username'*" (without double-quotes).
*change db_username to actual database username in both places.
Now update any connection strings to point to new SQL server (this could have been done while database was moving between servers to utilize all available time) and test it well. Also, check any DTS jobs, Full-Text Indexes and Replication configurations to ensure that they are set up on new server as they won't fully move during this process. Now you should be set. Test it well and sit back, relax and reflect on a job well done.
Sources: http://vyaskn.tripod.com/moving_sql_server.htm http://www.databasejournal.com/features/mssql/article.php/2224361 http://vyaskn.tripod.com/troubleshooting_orphan_users.htm
Desirée Harris is a support specialist with ORCS Web - a company that provides managed hosting services for clients who develop and deploy their applications on Microsoft Windows platforms.
Desirée Harris is a support specialist with ORCS Web - a company that provides managed hosting services for clients who develop and deploy their applications on Microsoft Windows platforms.