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:

• Make a backup of the databases

• 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. Backup databases

   Using SQL Server Management Studio...

   1. Expand the Databases direcotry

   2. Right click on the database you want to move > select Tasks > Backup

   3. Next click Add > provide a path to place the backup > Ok

   Note: We recommend placing the backup in the following location

   C:\Users\Public\Treasury Software\Data

   
   



2. Copy the database backups to the Target Server

   Using Windows Explorer, copy the database backups from the Source Server to the Target Server.





On the Target SQL Server

3. Restore 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

   Right click on the Databases directory > Restore Database

   Select Device > click on the grey box with 3 black dots > Add > navigate to the location of the database backups

   Click OK

   
   
   

   Lastly click 'OK'

   
   
   

   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