2

I have moved the model database by using following method

  1. For each file to be moved, run the following statement.

    ALTER DATABASE model
    MODIFY FILE ( NAME = modeldev , FILENAME = 'G:\model\model.mdf' )
    ALTER DATABASE model
    MODIFY FILE ( NAME = modellog , FILENAME = 'G:\model\modellog.ldf' )
    
  2. Stop the instance of SQL Server to perform maintenance.
  3. Move the file or files to the new location.
  4. Restart the instance of SQL Server or the server.

While restarting instance of server i am getting following error as shown below.

"The request failed or the service did not respond in a timely fashion. Consult the event log or other applicable error logs for details."

How to solve this error?

  • 4
    And what event log has about this error ? – JackLock Apr 8 '13 at 12:55
  • Did you try step 5) of the Microsoft documentation: msdn.microsoft.com/en-us/library/ms345408.aspx to see if the files were really changed ? – KookieMonster Apr 8 '13 at 12:58
  • You probably need to add appropriate permissions on the new location so the SQL Server database engine service account has access. I recorded a video of the whole process on my blog here. If you already did that, you're going to have to give us the error message(s) from the error log, because there's nothing else to go on in the question. – Jon Seigel Apr 8 '13 at 16:35
  • What's in the event log? – mrdenny Apr 8 '13 at 18:07
1

Follow below steps :

    a. start SQL Server in master-only recovery mode

     for Default instance : NET START MSSQLSERVER /f /T3608

     for Nameed Instance  : NET START MSSQL$instancename /f /T3608

    b. Check the file location 

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

    4. sp_detach_db 'model'
        go

    3. Move the Model.mdf and Modellog.ldf files from OLD_LOCATION to NEW_LOCATION
    4. Reattach the model database as follows:
    use master
    go
    sp_attach_db 'model','G:\model\model.mdf','G:\model\modellog.ldf'
    go
   5. Stop SQL Server : NET STOP MSSQLSERVER or NET STOP MSSQL$instancename
   6. Restart SQL Server Normally : NET START MSSQLSERVER or NET START MSSQL$instancename
   7. Verify the new location : sp_helpdb model
  • Although, this script is completely correct, the sp_detach_db / sp_attach_db stored procs are being depricated since 2005 (of course they are still there in 2012 :). Thought it was worth mentioning that the new syntax is as written in the first post. – Delux Apr 8 '13 at 19:56
  • 1
    @Delux: sp_detach_db/sp_attach_db don't do the same thing as ALTER DATABASE ... MODIFY FILE. Also, only sp_attach_db is deprecated, and the correct replacement is CREATE DATABASE ... FOR ATTACH. In any event, there's no reason to do that: ALTER DATABASE ... MODIFY FILE in the original steps works fine to accomplish the goal. – Jon Seigel Apr 9 '13 at 16:45
  • Note that this is not the way to do it now on sql 2017 it puts the database into a state where you have to re-build the master database (unless you have a backup) The correct thing to do now is follow these instructions docs.microsoft.com/en-us/sql/relational-databases/databases/… – Matt Vukomanovic Jul 16 '18 at 1:40
1

Note for SQL Server 2017 if you follow the accepted answer it will break your master database and you will need to manually re-create the logins and databases you currently have. At no point in sql server 2017 should you detach the model database

Instead you should follow the instructions here. https://docs.microsoft.com/en-us/sql/relational-databases/databases/move-system-databases?view=sql-server-2017

Note that is what was happening in the question.

So you would need to look at the log file to see why it isn't loading.

You could also run the sql server from the command line like

"c:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Binn\sqlservr.exe" -c

Which will show the log as it happens

Or to run it in single user admin mode with minimal configuration and not loading the model database

"c:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Binn\sqlservr.exe" -c -f -m -t 3608

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.