Moving system databases in SQL Server 2008…

…has become a lot easier.

In SQL Server 2008, the MSDB & Model databases can be moved in the same way as the tempdb, by using simple alter database commands to initiate the move followed by a physical move and server stop start. see my post on How to move system databases in SQL 2005 to see the difference.

To check the current location of files in a database:

SELECT name, physical_name AS CurrentLocation
FROM sys.master_files WHERE database_id = DB_ID(N'msdb'); GO

To move the model, msdb and tempdb databases:

Single user mode is no longer necessary, neither is the 3608 trace flag. Now its a simple alter command:

--Move Model Database 
USE master; 
GO 
ALTER DATABASE model
MODIFY FILE (NAME = modeldev,FILENAME = 'E:\DATA\model.mdf'); 
GO 
ALTER DATABASE model
MODIFY FILE (NAME = Modellog,FILENAME = 'E:\DATA\modelLog.ldf'); 
GO 

--Move MSDB Database 
USE master; 
GO 
ALTER DATABASE msdb
MODIFY FILE (NAME = MSDBData,FILENAME = 'E:\DATA\MSDBData.mdf'); 
GO 
ALTER DATABASE msdb
MODIFY FILE (NAME = MSDBLog, FILENAME = 'E:\DATA\MSDBLog.ldf'); 
GO 

--Move Tempdb Database 
USE master; 
GO 
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'E:\DATA\tempdb.mdf'); 
GO 
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'E:\DATA\tempLog.ldf'); 
GO

Stop the instance, physically move the model and msdb files to the new location, and restart the instance. Tempdb files do not need to be moved as they get recreated on startup.

To move the master database:

In SQL Server configuration manager, edit the advanced properties for the SQL Server Service.

Change the startup parameters to the new location of the files, –l flag refers to log destination and –d flag refers to data file destination:

-dE:\SQLData\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\LOG\ERRORLOG;-lE:\SQLData\mastlog.ldf

Stop the instance.

Physically move the files to the new location

Start the instance.

Moving the master database is a lot simpler because the instance no longer has to be opened in master recovery only mode, as we no longer have the ability to move the resource database. In SQL Server 2008, the location of the Resource database is <drive>:\Program Files\Microsoft SQL Server\MSSQL10.<instance_name>\MSSQL\Binn\. and the database cannot be moved.

Moving Error Logs

Got a request to add this into this blog, and makes sense to have it here so here it is:

To move the Error log location of the SQL Server Error log

1. Stop the SQL Server Service of the SQL Server you want to modify

2. Open SQ Server Configuration Manager. Right click the SQL Server Service and click Properties

3. Select the Advanced Tab

4. Modify the Startup Parameters Line and replace the contents of the –e flag with the location of the new error log

Eg: from:

-eC:\Program Files\Microsoft SQL Server\MSSQL10.INST1\MSSQL\Log\ERRORLOG;

To:

-eD:\MSSQL\Log\ERRORLOG;

5. Start the SQL Server Service

image

To move the SQL Server Agent error log, go to SQL Server Agent -> Error Logs –> <Right click> Configure

image

You can also change logging level in this location.

Advertisements

9 Responses to Moving system databases in SQL Server 2008…

  1. Buzz says:

    you have a mistake in your code that will not allow the model .ldf to be moved

    –Move Model Database
    USE master;
    GO
    ALTER DATABASE model
    MODIFY FILE (NAME = modeldev, FILENAME = ‘E:\DATA\modeldev.mdf’);
    GO

    ALTER DATABASE msdb (should be model)
    MODIFY FILE (NAME = Modellog, FILENAME = ‘E:\DATA\modelLog.ldf’);
    GO

  2. That Guy says:

    Worthy of note, your example code does not point to the default file names. Specifically, in my case, after moving the files to the new location SQL could not find my model.mdf file, because it is now looking for modeldev.mdf!!! (Also, instead of tempdb.mdf the code points to tempdev.mdf; not critical as this file is created when SQL starts up.)

    That having been said, it appears to be working correctly now.
    Thanks for simplifying the instructions.

    • That Guy says:

      Meant to say that to fix the bad pointer to the model database (error code 1814), just rename the file.

      • dbamohsin says:

        Hi,

        Ive updated it to be the default file names to make it easier for people.

        However, always run the below sql first before moving any database files to get the correct logical and physical names so you dont get caught out by differences.

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

        Thanks for you input.. please continue to comment if you see anything out of place.

        Mohsin

  3. AK says:

    Easy indeed. Might want to mention moving things like log file while at it: -eE:\SQLData\LOG\ERRORLOG

  4. Kevin Dailey says:

    Thanks for the thorough directions this was easy to complete.

  5. Larry Ellis says:

    WAY better than Microsoft’s instructions for this! Thanks!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: