Moved Model Database and now my SQL Server doesn’t work

Posted on

Question :

I moved my Model data and log files:

MODIFY FILE (NAME = 'modeldev', FILENAME = 'D:Datamodel.mdf')

MODIFY FILE (NAME = 'modellog', FILENAME = 'L:Log_Filesmodellog.ldf')

But it seems it has failed and now my SQL Server connection is gone and now SQL Server doesn’t seem to work and I would like to know who how I could rectify.

When I try to connect to SQL Server, the message is:

Cannot connect to X.

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (.Net SqlClient Data Provider).

I am using SQL Server 2012. Any help would be appreciated.


Answer :

Did you follow the steps in the documentation for doing this?

Move System Databases – SQL Server 2012

To move a system database data or log file as part of a planned
relocation or scheduled maintenance operation, follow these steps.
This procedure applies to all system databases except the master and
Resource databases.

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

    ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_pathos_file_name' )

  2. Stop the instance of SQL Server or shut down the system to perform maintenance. For more information, see Start, Stop, Pause, Resume,
    Restart the Database Engine, SQL Server Agent, or SQL Server Browser

  3. Move the file or files to the new location.

  4. Restart the instance of SQL Server or the server. For more information, see Start, Stop, Pause, Resume, Restart the Database
    Engine, SQL Server Agent, or SQL Server Browser Service.

  5. Verify the file change by running the following query.

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

If the msdb database is moved and the instance of SQL Server is
configured for Database Mail, complete these additional steps.

  1. Verify that Service Broker is enabled for the msdb database by running the following query.

    SELECT is_broker_enabled
    FROM sys.databases
    WHERE name = N'msdb';

For more information about enabling Service Broker, see ALTER

2. Verify that Database Mail is working by sending a test mail.

Critically, note that the command you ran doesn’t actually move the physical data files.

I managed to resolve the issue. The Service Account in Configuration Manager didn’t have a permission to view the file.

I was also in the same situation sometime back, where I moved the mdf/ldf files of my MASTER database to another drive. . I check over lot of database/sql server forums and found the issue and resolved it.

I’ve created a post on my blog for the same, summarized below:

SQL Server comes with a tool i.e. “SQL Server Configuration Manager”
to manage the services associated with SQL Server. Like, for this case
to configure startup options that will be used every time the Database
Engine starts in SQL Server.

Open this tool from “Program Files -> SQL Server -> Configuration

  • Select “SQL Server Services” on the left side navigation bar.

  • On the right side Right Click on SQL Server instance and select Properties.

  • On the Pop-Up select the “Startup Paramaters” tab. Here you can change the MASTER database’s MDF & LDF file’s location: —> Parameter
    starting with “-dD” is for DATA file (MDF). —> AND parameter starting
    with “-lD” is for LOG file (LDF).

  • Select both properties one by one and change the file location at the “Existing Parameters:” text box and click Update for both the

  • Now, Start the Services and yes it started without any issue.

Leave a Reply

Your email address will not be published. Required fields are marked *