Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
3  
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 Answer 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
share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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