Moving MS databases from one server to another

less than 1 minute read

Deployed a new test server to replace an aging development server. Aside from the set up and other technical requirements, we needed to move the data from the old MSSQL server to the new MSSQL server. Quick steps to do so:

1. Make note of current location of database files.

select_properties

properties_panel

2. Detach database from old server. Ensure that you select option to disconnect active connections.

detach

3. Move the files to the new server. Make note of the location of the files after moving them to the new server. This will required when attaching the database to the new server. You may also need to manually select the location of the log file for the database.

attach

attach_select

4. Once the database is attached to the new server, check for any orphaned users.

sp_change_users_login 'report'

5. If there are any users listed in the report above, you can fix them with the following command. Replace %user% with the name of your user listed in the above report. Make note to keep the single quotations.

sp_change_users_login 'auto_fix', '<>'

Done!

Leave a Comment

Your email address will not be published. Required fields are marked *

Loading...