Moving BizTalk and system databases

After installation of BizTalk and SQL Server I found that the SQL Server data files were stored on the installation drive, directory D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\. The client had explicitly indicated that the data files should be stored on the G drive and the log files on the H drive. I found that I had to apply some different procedures for moving the data files. Below the SQL queries you will have to execute:

Moving the BizTalk Databases (except MessageBoxDb):

ALTER DATABASE BAMArchive SET OFFLINE;

–Physically move data files
–FileSystem/Security: Give Users group Full Control
ALTER DATABASE BAMArchive MODIFY FILE ( NAME = ‘BAMArchive’, FILENAME = ‘G:\MSSQLSERVER\MSSQL\DATA\BAMArchive.mdf’ );
ALTER DATABASE BAMArchive MODIFY FILE ( NAME = ‘BAMArchive_log’, FILENAME = ‘H:\MSSQLSERVER\MSSQL\LOG\BAMArchive_log.ldf’ );

ALTER DATABASE BAMArchive SET ONLINE;

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N’BAMArchive’);

Moving the MessageBox database:

ALTER DATABASE BizTalkMsgBoxDb MODIFY FILE ( NAME = ‘BizTalkMsgBoxDb’, FILENAME = ‘G:\MSSQLSERVER\MSSQL\DATA\BizTalkMsgBoxDb.mdf’ );
ALTER DATABASE BizTalkMsgBoxDb MODIFY FILE ( NAME = ‘BizTalkMsgBoxDb_log’, FILENAME = ‘H:\MSSQLSERVER\MSSQL\LOG\BizTalkMsgBoxDb_log.ldf’ );

–Stop SQL Server
–Physically move data files
–Start SQL Server

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N’BizTalkMsgBoxDb’);

Moving the master Database:

To move the master database, follow these steps.
1. From the Start menu, point to All Programs, point to Microsoft SQL Server, point to Configuration Tools, and then click SQL Server Configuration Manager.
2. In the SQL Server Services node, right-click the instance of SQL Server (for example, SQL Server (MSSQLSERVER)) and choose Properties.
3. In the SQL Server (instance_name) Properties dialog box, click the Startup Parameters tab.
4. In the Existing parameters box, select the –d parameter to move the master data file. Click Update to save the change.
In the Specify a startup parameter box, change the parameter to the new path of the master database.
5. In the Existing parameters box, select the –l parameter to move the master log file. Click Update to save the change.
In the Specify a startup parameter box, change the parameter to the new path of the master database.
The parameter value for the data file must follow the -d parameter and the value for the log file must follow the -l parameter. The following example shows the parameter values for the default location of the master data file.
-dC:\Program Files\Microsoft SQL Server\MSSQL<version>.MSSQLSERVER\MSSQL\DATA\master.mdf
-lC:\Program Files\Microsoft SQL Server\MSSQL<version>.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
If the planned relocation for the master data file is E:\SQLData, the parameter values would be changed as follows:
-dE:\SQLData\master.mdf
-lE:\SQLData\mastlog.ldf
6. Stop the instance of SQL Server by right-clicking the instance name and choosing Stop.
7. Move the master.mdf and mastlog.ldf files to the new location.
8. Restart the instance of SQL Server.
9. Verify the file change for the master database by running the following query:
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(‘master’);

Moving the msdb and model databases:

ALTER DATABASE msdb MODIFY FILE ( NAME = ‘MSDBData’, FILENAME = ‘G:\MSSQLSERVER\MSSQL\DATA\MSDBData.mdf’ );
ALTER DATABASE msdb MODIFY FILE ( NAME = ‘MSDBLog’, FILENAME = ‘H:\MSSQLSERVER\MSSQL\LOG\MSDBLog.ldf’ );

–Stop SQL Server
–Physically move data files
–Start SQL Server

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N’msdb’);
Moving TempDb:

TempDb is automatically recreated when you restart SQL Server. Therefore datafiles don’t have to be moved, but simply can be deleted from the original location.

ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = ‘G:\MSSQLSERVER\MSSQL\DATA\tempdb.mdf’);
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = ‘H:\MSSQLSERVER\MSSQL\LOG\templog.ldf’);

–Stop SQL Server
–Physically remove data files
–Start SQL Server

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N’TempDb’);

Leave a Reply

Your email address will not be published. Required fields are marked *