0

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

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.