Sql-server – Moved Model Database and now the SQL Server doesn’t work

sql serversql-server-2012

I moved my Model data and log files:

ALTER DATABASE Model
MODIFY FILE (NAME = 'modeldev', FILENAME = 'D:\Data\model.mdf')

ALTER DATABASE Model
MODIFY FILE (NAME = 'modellog', FILENAME = 'L:\Log_Files\modellog.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.

Thanks.

Best 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_path\os_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 Service.

  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 DATABASE (Transact-SQL).

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.

Related Question