Move Lync SQL Databases with a SQL Statement

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\rtcad.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.

Tagged as: alter, database, Script