Sql-server – Moving MSDB database files fails

msdbsql serversql-server-2008-r2

I'm trying to relocate system databases files.
I managed to do that with the tempdb database, but I'm stuck with msdb.

When I run:

SELECT name, physical_name AS current_file_location
FROM sys.master_files
WHERE database_id IN DB_ID('msdb')

it shows that the logical names are 'MSDBData' and 'MSDBLog'. Then when I run below mention TSQL query

alter database tempdb modify file (name = MSDBData, filename = 'C:\Data\MSDBData.mdf')

I am getting the following error: MODIFY FILE failed. File 'MSDBData' does not exist.

MS Sql 2008 R2 server version: 10.50.1600

Best Answer

First, you should use:

... WHERE DB_NAME(database_id) IN ('msdb', 'xxx', ...)

or 

... WHERE database_id = DB_ID('msdb')

Syntax for ALTER DATABASE is: 

ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path\os_file_name' )

In your case values are as follow:

  • database_name = MSDB
  • logical_name = MSDBData (or MSDBLog)
  • 'new_path\os_file_name' = 'C:\Data\MSDBData.mdf'

Then you can follow this procedure for MSDB:

  • execute ALTER DATABASE MSDB MODIFY FILE ( NAME = MSDBData, FILENAME = '...new path' )
  • and/or execute ALTER DATABASE MSDB MODIFY FILE ( NAME = MSDBLog, FILENAME = '...new path' )
  • stop SQL Server service
  • Move MSDBData and/or MSDBLog to their new location
  • Start SQL Server service

  • Check new file location

    SELECT name, physical_name AS current_file_location
FROM sys.master_files
WHERE database_id = DB_ID('msdb')

You must replace value such as MSDB, file location, file name with the correct values for your environment.

Related Question