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

Question

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.

• podiluska: Well move it back then!
• Hannah Vernon: @Djbril - what does the SQL Server Error Log say? mssqltips.com/sqlservertip/2506/…

Answer 1

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.

• Djbril: I got to point 4) The SQL instance didnt start.

Answer 2

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

Answer 3

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 Tools":

• 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 files.

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

• Manoj Pandey: thanks Jack, for editing my response. In future I'll make sure to add summary while posting my or external links.