none
Alter Database Trying to move system files RRS feed

  • Question

  • I'm trying to move the system datafiles, using the following:

    database tempdb
    modify file(name='tempdev',Filename='L:\Program Files\Microsoft SQL Server\PRMSQL4\MSSQL12.SP2013\tempdb.mdf')
    
    alter database tempdb
    modify file(name='templog',Filename='M:\Program Files\Microsoft SQL Server\PRMSQL4\MSSQL12.SP2013\templog.ldf')
    
    
    
    alter database model
    modify file(name='modeldev',Filename='L:\Program Files\Microsoft SQL Server\PRMSQL4\MSSQL12.SP2013\modeldev.mdf')
    
    alter database Model
    modify file(name='modellog',Filename='M:\Program Files\Microsoft SQL Server\PRMSQL4\MSSQL12.SP2013\modellog.ldf')
    
    
    
    alter database msdb
    modify file(name='msdbdata',Filename='L:\Program Files\Microsoft SQL Server\PRMSQL4\MSSQL12.SP2013\msdbdata.mdf')
    
    alter database msdb
    modify file(name='msdblog',Filename='M:\Program Files\Microsoft SQL Server\PRMSQL4\MSSQL12.SP2013\msdblog.ldf')
    

    Message returned

    The file "msdblog" has been modified in the system catalog. The new path will be used the next time the database is started.

    etc...

    I've moved the files, and try to start the database instance again, and the database will not start.

    the logs state:

    Unable to open the Server service performance object. The first four bytes (DWORD) of the Data section contains the status code.

    How do I either revert back or move forward ?

    Thanks!


    John

    Friday, February 19, 2016 10:16 PM

Answers

  • >And then when it tells you that it can't open the files due to Operating System Error 5: Access Denied,

    >then open the folders and set the same ACLs you see on the old folder locations.

    "FCB::Open failed: Could not open file L:\Program Files\Microsoft SQL Server\PRMSQL4\MSSQL12.SP2013\model.mdf for file number 1.  OS error: 5(Access is denied.)."

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Proposed as answer by Eric__ZhangModerator Tuesday, February 23, 2016 8:19 AM
    • Marked as answer by vsla Wednesday, February 24, 2016 6:19 PM
    Monday, February 22, 2016 6:05 PM

All replies

  • This looks pertinent:

    https://msdn.microsoft.com/en-us/library/ms345408(v=sql.120).aspx#Follow

    Especially:

    If you have moved all of the system databases to a new drive or volume or to another server with a different drive letter, make the following updates.

        • Change the SQL Server Agent log path. If you do not update this path, SQL Server Agent will fail to start.

        • Change the database default location. Creating a new database may fail if the drive letter and path specified as the default location do not exist.

        Change the SQL Server Agent Log Path

        1. From SQL Server Management Studio, in Object Explorer, expand SQL Server Agent.

        2. Right-click Error Logs and click Configure.

        3. In the Configure SQL Server Agent Error Logs dialog box, specify the new location of the SQLAGENT.OUT file. The default location is C:\Program Files\Microsoft SQL Server\MSSQL12.<instance_name>\MSSQL\Log\.

        Change the database default location

        1. From SQL Server Management Studio, in Object Explorer, right-click the SQL Server server and click Properties.

        2. In the Server Properties dialog box, select Database Settings.

        3. Under Database Default Locations, browse to the new location for both the data and log files.

    • Stop and start the SQL Server service to complete the change.


    John

    • Proposed as answer by pituachMVP, Moderator Saturday, February 20, 2016 4:36 AM
    • Unproposed as answer by vsla Saturday, February 20, 2016 5:49 PM
    Friday, February 19, 2016 11:58 PM

  • Unable to open the Server service performance object. The first four bytes (DWORD) of the Data section contains the status code.

    How do I either revert back or move forward ?

    Thanks!


    John

    The error is not related to SQL Server as such nor its is caused because you moved the system databases. This must be coming before you performed the movement operation have a look at below link

    https://technet.microsoft.com/en-us/library/cc727117%28WS.10%29.aspx


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Wiki Articles

    MVP

    Saturday, February 20, 2016 5:33 AM
  • So the better question and root question is:

    I have executed the following commands:

    alter database model

    modify file(name='modeldev',Filename='L:\Program Files\Microsoft SQL Server\PRMSQL4\MSSQL12.SP2013\model.mdf')

    alter database Model

    modify file(name='modellog',Filename='M:\Program Files\Microsoft SQL Server\PRMSQL4\MSSQL12.SP2013\modellog.ldf')

    Prior to the execution of the above, I was able to start and stop the SQL Database instance. After issuing the above commands and moving the Model database and log file, I am not able to start the database Instance.

    How do I undo the above command - since it only becomes effective AFTER the service is restarted. It seems the only way to revert back is to uninstall the Database Instance and reinstall it, and I'm still unable to move the model databases.


    John

    Saturday, February 20, 2016 5:44 PM
  • Could you post the contents of the SQL Server errorlog from your failed of start attempts, so that we get a clear understanding of what error message you get?

    Saturday, February 20, 2016 7:13 PM
  • And then when it tells you that it can't open the files due to Operating System Error 5: Access Denied, then open the folders and set the same ACLs you see on the old folder locations.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Saturday, February 20, 2016 8:02 PM
  • Thank you and Certainly.

    Below are the contents of the Event Log File:

    Log File Contents (Pertinent Items)

    Starting up database 'model'.

    FCB::Open failed: Could not open file L:\Program Files\Microsoft SQL Server\PRMSQL4\MSSQL12.SP2013\model.mdf for file number 1.  OS error: 5(Access is denied.).

    FCB::Open failed: Could not open file M:\Program Files\Microsoft SQL Server\PRMSQL4\MSSQL12.SP2013\modellog.ldf for file number 2.  OS error: 5(Access is denied.).

    SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

    The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/PRMSQL4.prmech.com:SP2013 ] for the SQL Server service. Windows return code: 0x200b, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.

    The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/PRMSQL4.prmech.com:49644 ] for the SQL Server service. Windows return code: 0x200b, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.

    Microsoft SQL Server 2014 - 12.0.4100.1 (X64)

                    Apr 20 2015 17:29:27

                    Copyright (c) Microsoft Corporation

                    Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

    Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL12.SP2013\MSSQL\Log\ERRORLOG'.

    The service account is 'NT Service\MSSQL$SP2013'. This is an informational message; no user action is required.

    Registry startup parameters:

                     -d C:\Program Files\Microsoft SQL Server\MSSQL12.SP2013\MSSQL\DATA\master.mdf

                     -e C:\Program Files\Microsoft SQL Server\MSSQL12.SP2013\MSSQL\Log\ERRORLOG

                     -l C:\Program Files\Microsoft SQL Server\MSSQL12.SP2013\MSSQL\DATA\mastlog.ldf

    Command Line Startup Parameters:

                     -s "SP2013"

                     -f

                     -T 3608

    Warning: The server instance was started using minimal configuration startup option (-f). Starting an instance of SQL Server with minimal configuration places the server in single-user mode automatically.  After the server has been started with minimal configuration, you should change the appropriate server option value or values, stop, and then restart the server.

    SQL Server detected 6 sockets with 1 cores per socket and 1 logical processors per socket, 6 total logical processors; using 6 logical processors based on SQL Server licensing. This is an informational message; no user action is required.

    Perfmon counters for resource governor pools and groups failed to initialize and are disabled.

    The maximum number of dedicated administrator connections for this instance is '1'

    Support for distributed transactions was not enabled for this instance of the Database Engine because it was started using the minimal configuration option. This is an informational message only. No user action is required.

    Warning ******************

    SQL Server started in single-user mode. This an informational message only. No user action is required.

    Recovering only master database because traceflag 3608 was specified. This is an informational message only. No user action is required.

    SQL Server is attempting to register a Service Principal Name (SPN) for the SQL Server service. Kerberos authentication will not be possible until a SPN is registered for the SQL Server service. This is an informational message. No user action is required.

    SQL Server is now ready for client connections. This is an informational message; no user action is required.

    The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/PRMSQL4.prmech.com:SP2013 ] for the SQL Server service. Windows return code: 0x200b, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.

    The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/PRMSQL4.prmech.com:49644 ] for the SQL Server service. Windows return code: 0x200b, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.

    Starting up database 'msdb'.

    Snapshot isolation or read committed snapshot is not available in database 'msdb' because SQL Server was started with one or more undocumented trace flags that prevent enabling database for versioning. Transaction started with snapshot isolation will fail and a query running under read committed snapshot will succeed but will resort back to lock based read committed.

    Starting up database 'mssqlsystemresource'.

    Clearing tempdb database.

    Starting up database 'model'.

    FCB::Open failed: Could not open file L:\Program Files\Microsoft SQL Server\PRMSQL4\MSSQL12.SP2013\model.mdf for file number 1.  OS error: 5(Access is denied.).

    FCB::Open failed: Could not open file M:\Program Files\Microsoft SQL Server\PRMSQL4\MSSQL12.SP2013\modellog.ldf for file number 2.  OS error: 5(Access is denied.).

    A file activation error occurred. The physical file name 'M:\Program Files\Microsoft SQL Server\PRMSQL4\MSSQL12.SP2013\modellog.ldf' may be incorrect. Diagnose and correct additional errors, and retry the operation.

    Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.


    John

    Monday, February 22, 2016 5:30 PM
  • >And then when it tells you that it can't open the files due to Operating System Error 5: Access Denied,

    >then open the folders and set the same ACLs you see on the old folder locations.

    "FCB::Open failed: Could not open file L:\Program Files\Microsoft SQL Server\PRMSQL4\MSSQL12.SP2013\model.mdf for file number 1.  OS error: 5(Access is denied.)."

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Proposed as answer by Eric__ZhangModerator Tuesday, February 23, 2016 8:19 AM
    • Marked as answer by vsla Wednesday, February 24, 2016 6:19 PM
    Monday, February 22, 2016 6:05 PM