Sql Server: Move System Databases

In the previous post we noted how simple it is to move user databases. Now we’ll look at moving System database.

Moving system databases is as simple as moving user databases (with a couple of extra steps). But if done incorrectly, could leave the Sql Server instance in a unstable and unresponsive state. So I strongly recommend that you try this only on a test instance before you actually run this on Production (been there, done that :().

In a typical Sql Server installation, we have the following system databases:

• Master
• Model
• Msdb
• TempDB
• Resource   (not listed in SSMS or catalog views)

Following steps must be followed in the order listed here for a successful implementation.

Step 1: Make sure all the other Sql services are stopped except SqlServer database engine service (sqlservr.exe) and Sql Browser service (sqlbrowser.exe).

Stop these services, either from Services Console or SCM (Sql Server Configuration Manager)

• Integration (MsDtsSrvr.exe)
• Analysis (msmdsrv.exe)
• Reports (ReportingServicesService.exe)
• Sql Agent (SQLAGENT.EXE)
• Full Text (fdlauncher.exe)

Also make sure there are no other services running that request database connection i.e. web server, app server, mid-tier services.

This step is not required, but I would recommend it as I ran into problems in the past.

Step 2: Move Model, Msdb, TempDB files

Run the following script for each database file for these databases.

USE MASTER
GO
--  MSDB Database
ALTER DATABASE [msdb]
MODIFY FILE (NAME = 'MSDBData', FILENAME = 'E:\MDF\MSDBData.MDF')
GO
ALTER DATABASE [msdb]
MODIFY FILE ( NAME = 'MSDBLog', FILENAME = 'E:\LDF\MSDBLog.LDF')
GO
--  Model Database
ALTER DATABASE [model]
MODIFY FILE (NAME = 'modeldev', FILENAME = 'E:\MDF\model.MDF')
GO
ALTER DATABASE [model]
MODIFY FILE (NAME = 'modellog', FILENAME = 'E:\LDF\modellog.LDF')
GO
--  TempDB Database
ALTER DATABASE [tempdb]
MODIFY FILE (NAME = 'tempdev', FILENAME = 'E:\MDF\tempdb.MDF')
GO
ALTER DATABASE [tempdb]
MODIFY FILE (NAME = 'templog', FILENAME = 'E:\LDF\templog.LDF')
GO


Step 3: Stop the Sql Server instance.

Step 4: Move database files to the new location.

Step 5: Start the Sql Server instance. (only the database engine)

Step 6: Confirm the success for these three system databases. Also run some sample queries to confirm the status of these system databases.

USE MASTER
GO
SELECT DB_NAME(database_id) AS [Database Name]
, name AS [Logical_Name]
, Physical_Name AS [Physical Name]
FROM sys.master_files
WHERE database_id IN (DB_ID('model'), DB_ID('msdb'), DB_ID('tempdb'))
GO


Step 7: If you are using DBMail, make sure Service Broker is still enabled after the move. Also send a Test email to be sure.

USE MASTER
GO
SELECT is_broker_enabled
FROM sys.databases
WHERE name = N'msdb'
GO


Step 8: Now its time for Master and Resource databases.

It is important that Master and Resource data files must remain in the same location.

• Step 8.1: Go to SCM (Sql Server Configuration Manager) and identify relevant Sql Server instance (MSSQLSERVER or named instance) and go to properties.
• Step 8.2: In properties window, go to Advanced tab and look for Startup Parameters in the list.
• Step 8.3: The start up parameters  -d, -l, -e specify where the master data file, master log file and folder location for ERROR LOGS respectively. Lets say, the new location is E:\MDF for data, E:\LDF for log and E:\Log\ERRORLOG for log files.

—
-dE:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf;-lE:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf;-eE:\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG

Change it to:

-dE:\MDF\master.mdf;-lE:\LDF\mastlog.ldf;-eE:\Log\ERRORLOG
—

• Step 8.4: Make sure there is no other service running other than Sql Server database engine. Now, stop the service.
• Step 8.5: Move master.mdf and mastlog.ldf to the new location (E:\MDF, E:\LDF).
• Step 8.6: Now start Sql Server database service with a special start up parameter.  Go to command prompt and run the following command. This parameter allows us to limit the recovery operations just to master database that way we could perform some operations on other databases (on Resource database).

• NET START MSSQLSERVER /f /T3608 or
• NET START MSSQL$instancename /f /T3608

• Step 8.7: Now open a new connection to the database and run the following command to indicate move of Resource database files.

ALTER DATABASE [MSSqlSystemResource]
MODIFY FILE (NAME = 'data', FILENAME = 'E:\MDF\mssqlsystemresource.MDF')
GO
ALTER DATABASE [MSSqlSystemResource]
MODIFY FILE (NAME = 'log', FILENAME = 'E:\LDF\mssqlsystemresource.LDF')
GO


• Step 8.8: Move mssqlsystemresource.mdf and mssqlsystemresource.ldf files to the new location.
• Step 8.9: Now set Resource database to read-only mode.ALTER DATABASE MSSqlSystemResource
SET READ_ONLY;
• Step 8.10: Exit database connection and restart the Sql instance.
• Step 8.11: Now confirm the success of the move. Please note Resource database metadata is not available from the system catalog views.

USE MASTER
GO
SELECT DB_NAME(database_id) AS [Database Name]
, name AS [Logical_Name]
, Physical_Name AS [Physical Name]
FROM sys.master_files
WHERE database_id = DB_ID('master')
GO

Hope this helps,

_SqlTimes

Read Full Post »

Sql Server: Move User databases

There are several ways to move ‘User’ database file location.

1. Attach/Detach
2. Backup/Restore
3. ALTER DATABASE MODIFY FILE

1 (Attach/Detach) and 2 (Backup/Restore) are straight forward and are more popular that the 3rd option, which is what I cover in this post (Please note that there are other ways to do this too).

Following script allows to perform move on User databases databases to a different location.

Note: Please make sure you try it on a test server before you implement on a Production machine.

Step 1: First, identify the current location of the database files.

USE MASTER
GO
SELECT DB_NAME(database_id) AS [Database Name]
, name AS [Logical_Name]
, Physical_Name AS [Physical Name]
FROM sys.master_files
WHERE database_id = DB_ID('DBA')
GO


Step 2: For each database file, run the following command to indicate to Sql Server that you are making meta data change to a database file.

ALTER DATABASE [DBA]
MODIFY FILE
(
NAME = 'DBA_Primary_Data'
, FILENAME = 'E:\MSSQL_2K5\MDF\DBA_Primary.MDF'
)
GO
ALTER DATABASE [DBA]
MODIFY FILE
(
NAME = 'DBA_Log'
, FILENAME = 'E:\MSSQL_2K5\LDF\DBA_Log.LDF'
)
GO


After running the above script you will see the following message.

Step 3: Stop the Sql Server instance

Step 4: Move the database files to the new location.

Step 5: Restart the Sql Server instance for the user database to start at the new location.

Step 6: Verify the success of the move using the following query.

USE MASTER
GO
SELECT DB_NAME(database_id) AS [Database Name]
, name AS [Logical_Name]
, Physical_Name AS [Physical Name]
FROM sys.master_files
WHERE database_id = DB_ID('DBA')
GO


Moving system databases is also similar with a couple of extra steps. We will look at that in the next post.

 

Hope this helps,

_SqlTimes

Read Full Post »