How do I migrate my database to a new SQL server?


To move from one SQL server to another, follow the instructions listed below.

Install and configure new SQL server per the Microsoft Installation instructions. (Note: Ensure that mixed authentication is enabled).


Step One: Creating the Intranet Connections SQL database
  1. Open the SQL Server Management Studio.
  2. Right click Databases and Select New Database...


  3. In the Database field name type in IntranetConnections
  4. Keep the rest of the servers default settings.


Step Two: Create the IntranetConnections Login
  1. Right click Logins (under security) and select new login.


  2. In the field name type in Intranet Connections
  3. Change the default windows authentication to SQL server authentication.
  4. Leave the password field blank.
  5. In the database field from the drop down box select IntranetConnections (see image below)


  6. 6. Now click on the User Mapping page
  7. Select the database IntranetConnections by clicking in the checkbox.
  8. Under Database roles make sure Public and db_owner are checked.
  9. Click OK to finish the login creation.


Step Three: Moving your old SQL content to your new SQL IntranetConnections database
  1. First create a backup of your old production IntranetConnections database.  You can do this via the SQL Admin interface (i.e. Management Studio).
  2. Right click the database name > Tasks (or All tasks) > backup the database.
  3. Back it up to a file in a temp location.
  4. Now go to your Newly created IntranetConnections database, right click and select Restore > point to the location of your backed up database file.
  5. Restore the database (you may have to check the option to force a restore and change the restore database files if they are different).
  6. Once the database has been restored, go to the properties of the of the SQL IntranetConnections LOGIN and ensure that it has DB_Owner rights as described above.
  7. Right-click the database and click 'New Query'. Execute the following SQL statement:

    exec sp_change_users_login 'auto_fix','intranetconnections';


Step Four: Create/Modify your IntranetConnections SQL Datasource in Coldfusion

See: Coldfusion > Creating an SQL datasource or Railo > Creating an SQL datasource

Have more questions? Submit a request


Article is closed for comments.