none
Incorrect syntax near the keyword 'FILE'

    Question

  • I am trying to move SQL Server 2008 system data bases to a new location.

    Here is the code I am using for the first step:

    USE master
    GO


    --ALTER temdb
    ALTER DATABASE
    MODIFY FILE (NAME = 'tempdev',FILENAME = 'Q:\MSSQL10_50.MSSQLSERVER-DATA\tempdb.mdf')

    ALTER DATABASE
    MODIFY FILE (NAME = 'templog',FILENAME = 'Q:\MSSQL10_50.MSSQLSERVER-DATA\templog.ldf')


    --ALTER model
    ALTER DATABASE
    MODIFY FILE (NAME = 'modeldev',FILENAME = 'Q:\MSSQL10_50.MSSQLSERVER-DATA\model.mdf')

    ALTER DATABASE
    MODIFY FILE (NAME = 'modellog',FILENAME = 'Q:\MSSQL10_50.MSSQLSERVER-DATA\modellog.ldf')


    --ALTER msdb
    ALTER DATABASE
    MODIFY FILE (NAME = 'MSDBData',FILENAME = 'Q:\MSSQL10_50.MSSQLSERVER-DATA\MSDBData.mdf')

    ALTER DATABASE
    MODIFY FILE (NAME = 'MSDBLog',FILENAME = 'Q:\MSSQL10_50.MSSQLSERVER-DATA\MSDBLog.ldf')

    I am getting the following errors:

    Msg 156, Level 15, State 1, Line 5
    Incorrect syntax near the keyword 'FILE'.
    Msg 156, Level 15, State 1, Line 8
    Incorrect syntax near the keyword 'FILE'.
    Msg 156, Level 15, State 1, Line 13
    Incorrect syntax near the keyword 'FILE'.
    Msg 156, Level 15, State 1, Line 16
    Incorrect syntax near the keyword 'FILE'.
    Msg 156, Level 15, State 1, Line 21
    Incorrect syntax near the keyword 'FILE'.
    Msg 156, Level 15, State 1, Line 24
    Incorrect syntax near the keyword 'FILE'.

    Can anyone tell me what I am doing wrong?

    Thanks!

    Sunday, July 14, 2013 8:24 PM

Answers

All replies

  • This link could be useful:

    Move System Databases

    You need something like this script:

    USE master;
    GO
    ALTER DATABASE tempdb 
    MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
    GO
    ALTER DATABASE tempdb 
    MODIFY FILE (NAME = templog, FILENAME = 'F:\SQLLog\templog.ldf');
    GO


    http://sqldevelop.wordpress.com/

    Sunday, July 14, 2013 8:42 PM
  • The pattern is:
    ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path\os_file_name' )
    Please write exactly the same, spaces etc.

    http://sqldevelop.wordpress.com/

    Sunday, July 14, 2013 8:47 PM
  • You need to give the database name in the command.  For example for tempdb instead of

    ALTER DATABASE MODIFY FILE ...

    you need

    ALTER DATABASE tempdb MODIFY FILE ...

    Also, since you are moving system databases, make sure you read http://msdn.microsoft.com/en-us/library/ms345408%28v=sql.100%29.aspx

    Tom

    • Proposed as answer by Kalman TothModerator Monday, July 15, 2013 12:59 AM
    • Marked as answer by Mei152 Monday, July 15, 2013 1:33 AM
    Sunday, July 14, 2013 8:48 PM
  • Thanks!

    Sometimes the obvious eludes me.

    Monday, July 15, 2013 1:32 AM