Move Lync SQL Databases with a SQL Statement

September 28, 2011

in Lync, Microsoft

Moving SQL user database with the GUI can be cumbersome and time consuming.  Using this procedure you can reconfigure SQL quickly and easily.  Modify for your needs.

These scripts assume the following:

  • Lync Front Ends are Enterprise edition and are using an external SQL Server.
  • The default Lync paths are being re-used.
  • Monitoring/Archiving databases are not included.  I will update the script later to include these databases.

For information on SQL Server Data and Log file placement for Lync click here…

To move a data or log file as part of a planned relocation, follow these steps:

Reference: http://msdn.microsoft.com/en-us/library/ms345483.aspx

  1. Stop all Lync services on the Front End servers.
  2. On a computer that is running Microsoft SQL Server, open SQL Server Management Studio and connect to the Default database instance.
  3. Click New Query.
  4. Run the following statement.
    ALTER DATABASE xds SET OFFLINE;
    ALTER DATABASE lis SET OFFLINE;
    ALTER DATABASE rtc SET OFFLINE;
    ALTER DATABASE rtcdyn SET OFFLINE;
    ALTER DATABASE rtcab SET OFFLINE;
    ALTER DATABASE rtcab1 SET OFFLINE;
    ALTER DATABASE cpsdyn SET OFFLINE;
    ALTER DATABASE rgsconfig SET OFFLINE;
    ALTER DATABASE rgsdyn SET OFFLINE;
  5. Move the files to the new location.
  6. After the files are successfully moved, run the following statement. (Replace the path with the drive letter and directory of the new destination)
    ALTER DATABASE rtc MODIFY FILE ( NAME = rtc_data, FILENAME = '?:\csdata\backendstore\(default)\dbpath\rtc.mdf' );
    ALTER DATABASE rtc MODIFY FILE ( NAME = rtc_log, FILENAME = '?:\csdata\backendstore\(default)\logpath\rtc.ldf' );
    
    ALTER DATABASE rtcdyn MODIFY FILE ( NAME = rtcdyn_data, FILENAME = '?:\csdata\backendstore\(default)\dyndbpath\rtcdyn.mdf' );
    ALTER DATABASE rtcdyn MODIFY FILE ( NAME = rtcdyn_log, FILENAME = '?:\csdata\backendstore\(default)\dynlogpath\rtcdyn.ldf' );
    
    ALTER DATABASE xds MODIFY FILE ( NAME = xds_data, FILENAME = '?:\csdata\centralmgmtstore\(default)\dbpath\xds.mdf' );
    ALTER DATABASE xds MODIFY FILE ( NAME = xds_log, FILENAME = '?:\csdata\centralmgmtstore\(default)\logpath\xds.ldf' );
    
    ALTER DATABASE lis MODIFY FILE ( NAME = lis_data, FILENAME = '?:\csdata\centralmgmtstore\(default)\dbpath\lis.mdf' );
    ALTER DATABASE lis MODIFY FILE ( NAME = lis_log, FILENAME = '?:\csdata\centralmgmtstore\(default)\logpath\lis.ldf' );
    
    ALTER DATABASE rtcab MODIFY FILE ( NAME = rtcab_data, FILENAME = '?:\csdata\absstore\(default)\dbpath\rtcab.mdf' );
    ALTER DATABASE rtcab MODIFY FILE ( NAME = rtcab_log, FILENAME = '?:\csdata\absstore\(default)\logpath\rtcab.ldf' );
    
    ALTER DATABASE rtcab1 MODIFY FILE ( NAME = rtcab1_data, FILENAME = '?:\csdata\absstore\(default)\dbpath\rtcab1.mdf' );
    ALTER DATABASE rtcab1 MODIFY FILE ( NAME = rtcab1_log, FILENAME = '?:\csdata\absstore\(default)\logpath\rtcab1.ldf' );
    
    ALTER DATABASE cpsdyn MODIFY FILE ( NAME = cpsdyn_data, FILENAME = '?:\csdata\applicationstore\(default)\dbpath\cpsdyn.mdf' );
    ALTER DATABASE cpsdyn MODIFY FILE ( NAME = cpsdyn_log, FILENAME = '?:\csdata\applicationstore\(default)\logpath\cpsdyn.ldf' );
    
    ALTER DATABASE rgsconfig MODIFY FILE ( NAME = rgsconfig_data, FILENAME = '?:\csdata\applicationstore\(default)\dbpath\rgsconfig.mdf' );
    ALTER DATABASE rgsconfig MODIFY FILE ( NAME = rgsconfig_log, FILENAME = '?:\csdata\applicationstore\(default)\logpath\rgsconfig.ldf' );
    
    ALTER DATABASE rgsdyn MODIFY FILE ( NAME = rgsdyn_data, FILENAME = '?:\csdata\applicationstore\(default)\dbpath\rgsdyn.mdf' );
    ALTER DATABASE rgsdyn MODIFY FILE ( NAME = rgsdyn_log, FILENAME = '?:\csdata\applicationstore\(default)\logpath\rgsdyn.ldf' );
  7. Run the following statement.
    ALTER DATABASE xds SET ONLINE;
    ALTER DATABASE lis SET ONLINE;
    ALTER DATABASE rtc SET ONLINE;
    ALTER DATABASE rtcdyn SET ONLINE;
    ALTER DATABASE rtcab SET ONLINE;
    ALTER DATABASE rtcab1 SET ONLINE;
    ALTER DATABASE cpsdyn SET ONLINE;
    ALTER DATABASE rgsconfig SET ONLINE;
    ALTER DATABASE rgsdyn SET ONLINE;

To enable chaining of the RTC and RTCdyn databases

Reference: http://support.microsoft.com/kb/968100

  1. On a computer that is running Microsoft SQL Server, connect to the RTC database, and then run the following SQL command from SQL Server Management Studio:
    sp_dboption rtc, 'db chaining', TRUE
  2. Connect to the RTCdyn database, and then run the following SQL command from SQL Server Management Studio:
    sp_dboption rtcdyn, 'db chaining', TRUE
  3. Start the Front End services.
4,273 views
1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)
Loading ... Loading ...

{ 14 comments… read them below or add one }

tommy September 30, 2011 at 1:01 am

In this process do we not need to move CMS to a different pool first?

Reply

Matt September 30, 2011 at 9:41 am

No, this process is for moving the databases to different locations within the same SQL server. To move the CMS to a different pool altogether you’ll want to follow this process: http://technet.microsoft.com/en-us/library/gg195644.aspx.

Reply

Josh Lynch February 13, 2012 at 11:50 am

Matt,

Thanks for this article. I think this is what I need to move the dbs from the C drive to the D drive on my SQL server.
My question is with the “chaining”. Is this something I must do, or is this optional to do a move? I am not changing SQL servers or instances, just moving the location from the system drive to the Data drive.

Thanks,

Josh

Reply

Matt February 19, 2012 at 5:39 pm

Yes, whenever you move the databases either locally or between SQL servers you will need to re-enable cross-databse chaining..

Reply

Sathish June 14, 2012 at 1:29 pm

Is there a document to move the SQL database to another SQL server? Everywhere I am finding a document to move to another pool or to another Drive location.

I would be thankful, if someone provides steps to move the SQL database of LYNC to another SQL Server.

Advance Thanks ,Sathish

Reply

Matt July 10, 2012 at 9:32 am
Lync Installer July 10, 2012 at 9:01 am

great article… noticed you have a typo in your script.
logpath\rtcad.ldf’

Reply

Matt July 10, 2012 at 9:34 am

Thanks, updated the script.

Reply

Lync User August 13, 2012 at 7:34 pm

Do these steps also apply to moving the DBs to an alternate data drive in the same server in Lync 2010 Standard Edition?

Reply

Matt September 6, 2012 at 10:58 am

Standard Edition is based on SQL Express and I haven’t tested to verify if the script would work for that edition. Feel free to try it out.

Reply

Jessica February 19, 2013 at 5:05 pm

Hi, Do you have any document or information about to move Enterprise Edition Databases from SQL Server Standard to SQL Server Enterprise edition, in the same server?

Reply

Matt March 7, 2013 at 5:51 pm

It would go something like this:
1. Move CMS to another pool (if needed)
2. To backup Lync: http://technet.microsoft.com/en-us/library/hh202168(v=ocs.14).aspx
3. Shutdown all Lync services dependent on the SQL Server
4. Rebuild SQL Server
5. To perform a SQL Restore: http://technet.microsoft.com/en-us/library/hh202163(v=ocs.14).aspx
5. Startup Lync services

I would recommend that you review the materials and procedures found below before starting this. Technically this is not very difficult and you should be able to perform this however I would plan for at least a couple of hours downtime while you replace the Back End services.

http://technet.microsoft.com/en-us/library/hh202160(v=ocs.14).aspx

Reply

nordean March 4, 2013 at 4:18 am

hello is this proc is supported by Microsoft
Thank you

Reply

Matt March 7, 2013 at 5:42 pm

I cannot say what Microsoft will support however, these are common SQL commands used by many DBAs during the course of general administration. I would suspect that if you messed up your SQL Server during the course of a SQL change you would be subject to the same type of support regardless of your reasons for initiating the change.

Reply

Leave a Comment