Moving SQL Server databases - For Database Administrators and System Administrators

This is an advanced topic and requires advanced computer knowledge.

Please print these instructions before continuing.

If you are reading this help topic, you are either getting ready to move a SQL database - or your first attempt was not successful.

Moving a SQL database is one of the most challenging tasks one can perform, as it involves 'hitting a moving target'. Typically, you will be:

• detaching and reattaching a database

• upgrading the SQL database version

• upgrading the Treasury Software version

Did your first attempt fail?

Below are some steps to help minimize the pain. Please note that we only use SQL Server Management Studio's GUI.

Please do not perform these steps from the command line.

Note: If you have already performed these steps, we suggest repeating the steps below and:

• temporarily granting the user sa rights on the target SQL Server

• when attaching the SQL database to the target SQL Server - perform this under the user's ID (temporarily elevated to sa).

On the Source SQL Server

1. Detach the database

   Using SQL Server Management Studio...

   
   

   It will then open the next window - uncheck Keep Full Text Catalogs, and click OK.

   



2. Copy the database and log files to the Target Server

   Using Windows Explorer, copy the database and log file from the Source Server to the Target Server.

   




On the Target SQL Server

3. Attach the database

   Note: If an earlier attempt failed - please make sure that you are now logged in as the primary user with elevated rights.

   Using SQL Server Management Studio

   
   
   

   then click 'Add'

   
   
   
   

   Review your work and click OK.

   



4. Confirm the connection

   Click on the database you just attached. Click on tables. Right click on tblmasterboth and 'Select Top x Rows'. Note: If you have a custom application, select another table you know should be populated.

   



5. Continue with the remainder of Moving SQL Server instructions

   This includes adding and upgrading the database within Treasury Software while logged in as the primary user with elevated rights.

   To return to Moving SQL Server

   
   

   Done!

   
   

   Alternate approaches:

   Export data to a pass thru file and then re-import it into the target database.

   Note: Contact Treasury Software first for the appropriate tables to migrate - not all tables are copied.

   See http://www.treasurysoftware.com/support2-SQL-Import-Export.html