How to Move ( migrate ) a Retain Database in MS SQL

How do I move my database from one server to another using MS SQL? How can I restore my retain database if I have a backup of the MS SQL database files?

DISCLAIMER:

This knowledgebase (KB) article is provided for informational purposes only and as a courtesy service to you, our customer. GWAVA Technical Support does not have any database administration (DBA) expertise, nor does it provide DBA services or support. GWAVA is not responsible for the results of implementing any of the concepts contained in this KB article. Implementation of any of the concepts suggested in this KB article shall be done entirely at your own and sole risk, and GWAVA does not provide any kind of warranties whatsoever resulting from your decision of implementing any of the KB article’s concepts. It is up to you to do any research and to ensure yourself that any implementation and setup of any of the KB article’s concepts in your database system is correctly and properly executed. It is imperative that you have backups of your database system and storage directory before making any implementation. If you don’t have any DBA expertise, you should consult with a DBA expert before any implementation of the KB article’s concepts.  Under no circumstances, shall GWAVA, or any of its employees, be liable, in contract, tort, delict or otherwise, whether negligence is provable or not, for any direct, indirect, incidental, special, punitive, consequential or other damages, loss, cost or liability whatsoever that would result from or are related to the implementation of any of the concepts suggested in the KB article. To the extent permitted by applicable law, GWAVA shall not be liable to you for any special, consequential, direct, indirect or similar damages, including any loss of data, arising out from migrating any type of messages, attachments, database, metadata in your Retain system to another server and/or location.

There are a few ways to move the retain database to another server and insert it into a new instance of MS SQL. Microsoft makes it quite simple to perform this task. The first way is to detach the retain database, copy the the files and restore them. The second way is to copy the backup and restore it. This article will show you both ways how to do that. 

Detach and Move the Retain Database

When moving to another server, the retain database in MS SQL can be detached from the instance, migrated to a new server and attached to a new instance of MS SQL.

Note: In the case of the retain database, stop retain service before detaching or attaching the database. Having any open connections while detaching will error out and will not let you continue. 

To detach a database from MS SQL

1. Load MS SQL Management Studio.
2. Expand the databases and find the database you wish to detach. (Retain)
3. Right click then select Tasks, Detach. A Detach Database screen will appear.

4. Select the database, and click OK. 

It may take some time to detach the database depending on the size of the database. Do not exit while performing this operation.

Note: Detaching a database removes it from the current instance of the SQL server. It will disappear from the menus upon successfully being detached. The database and the transaction log files, however, are still left intact.

5. Navigate to the Microsoft SQL directory on the server and look for the retain.mdf and the retain_log.ldf files. Copy these files to the new server which has the new instance of MS SQL installed.

Restore/Attach Retain Database

If you have detached a database, say in moving it to another server, you will need the .mdf and .ldf files. The .mdf file is the database file, while the .ldf is the transaction log. Copy the .mdf and .ldf files to the new server where the new instance of MS SQL is installed. Also, it is important that the .mdf and .ldf files are in the same directory together as attaching the database will not work and the restoration will fail. From there, follow these steps to attach the database:

1. Open up MS SQL Management Studio
2. Right Click on Databases and click on Attach. The Attach Database window will appear
3. Click Add and Browse to the location of the .mdf and .ldf files.
   
4. Click OK to start attaching.

This will attach the database the instance of MS SQL. Once finished you will see it in the management studio. 

Note: After attaching the database, be sure to check the ownership and permissions of the user and assign it to the retain database as necessary. 

Backing up Database

Backing up a database makes a copy of the database and places that into the backup directory.  It will also copy the .mdf and .ldf files as well. The backup of the sql database will have a .bak file extension. You can transfer the .bak file to another server and restore it in a new instance if needed.

To backup a database, right click on the database, click on tasks, and click backup. The backup file (.bak) will be located in the backup directory in the Microsoft SQL directory.  

Restoring a Backup/Moving Retain Database

If you have backed up the retain database using the backup feature in MS SQL, you can copy the [retain].bak file to the new server and restore it using the new instance. 

1. Open up MS SQL Management Studio
2. Right Click on System databases and click on Restore
3. Select Device and click on the elipses to the right. A new window will appear. 
4. Click on Add. Select the [retain].bak file, and click OK. 
   
    
5. Click OK again. 
6. The next step is necessary to create the .mdf and .ldf files in the corect location for MS SQL. Click on File and Click the option Relocate all files to folder. They should automatically point to the MS SQL directory. Be sure this is a location that has permissions to write, based on the user, and that it is the main location where the .mdf and .ldf files.
   
   

Click on OK to restore. 

This article was originally published in the GWAVA knowledgebase as article ID 2864.