Receiving Error: 18461 When Attempting to Move System Databases

I was attempting to move the master, tempdb, msdb, and model. After I added the –c option, -m option, and set trace flag 3608, and restarted, I got Error: 18461 when attempting to either connect via SSMS or sqlcmd in the consol. I had no idea what the problem was. Here is how it all played out;

I logged onto the Database Server remotely. In the SQL Server Configuration Manager, I added the –c option, the –m option, and set trace flag 3608 in Startup Parameters with (;-c;-m;-T3608).

I stopped all SQL Services (SSIS, FullText, Analysis Services, Reporting, SQL Server Browser, and SQL Agent) and restarted SQL Server (MSSQLSERVER).

When I attempted to connect via SSMS or sqlcmd in the consol, I receive the following error;

Login failed for user 'cmcdonald'. Reason: Server is in single user mode. Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461)

Then I attempted to login with Dedicated Administration Connection (DAC). I did this by opening SSMS window without connecting to a SQL Server. Then I clicked on the Database Engine Query button .
Next, I added ADMIN: and the name of my SQL Server Instance.

When I clicked Connect, I was connected to the instance. You can also connect with sqlcmd:

sqlcmd -A -d testDB -E -S \namedinstance

-A is for the DAC
-d is for the database
-E is for integrated security
-S is for the instance (in this example, a named instance)

Now I begin moving system databases starting with the model database.

1. Detach the model database by using the following commands:

use master
go
sp_detach_db 'model’
go

2. Move the Model.mdf and Modellog.ldf files from the D:\Mssql7\Data folder to the E:\Sqldata folder.

3. Reattach the model database by using the following commands:

use master
go
sp_attach_db 'model','E:\Sqldata\model.mdf','E:\Sqldata\modellog.ldf'
go

4. Remove -c -m -T3608 from the startup parameters in SQL Server Configuration Manager.

5. Stop and then restart SQL Server. You can verify the change in file locations by using the sp_helpfile stored procedure. For example, use the following command:

use model
go
sp_helpfile
go

Next, moved the msdb database.

1. Add -c -m -T3608(;-c;-m;-T3608) in the startup parameters in the SQL Server Configuration Manager and then restart SQL Server.

2. Make sure that the SQL Server Agent service is not currently running.

3. Log in with DAC and detach the msdb database as follows:

use master
go
sp_detach_db 'msdb'
go

Note: If your login fails, you may need to restart the windows server.

4. Move the Msdbdata.mdf and Msdblog.ldf files from the current location (D:\Mssql8\Data) to the new location (E:\Mssql8\Data).

5. Remove -c -m -T3608 from the startup parameters box in Enterprise Manager.

6. Then stop and restart SQL Server.

7. Reattach the msdb database as follows:

use master
go
sp_attach_db 'msdb','E:\Sqldata\msdbdata.mdf’,'E:\Sqldata\msdblog.ldf’
go

To moved the master database, do the following;

1. Change the path for the master data files and the master log files in SQL Server Enterprise Manager. Note: You may also change the location of the error log here.

2. Right-click the SQL Server in Enterprise Manager and then click Properties.

3. Click Startup Parameters to see the following entries:

-dD:\MSSQL7\data\master.mdf
-eD:\MSSQL7\log\ErrorLog
-lD:\MSSQL7\data\mastlog.ldf

-d is the fully qualified path for the master database data file.
-e is the fully qualified path for the error log file.
-l is the fully qualified path for the master database log file.

4. Change these values as follows:

a. Remove the current entries for the Master.mdf and Mastlog.ldf files.

b. Add new entries specifying the new location.

c.

-dE:\SQLDATA\master.mdf
-lE:\SQLDATA\mastlog.ldf

5. Stop SQL Server.
6. Copy the Master.mdf and Mastlog.ldf files to the new location (E:\Sqldata).
7. Restart SQL Server.

Move tempdb files by using the ALTER DATABASE statement.

1. Determine the logical file names for the tempdb database by using sp_helpfile as follows:

use tempdb
go
sp_helpfile
go

-- tempdev 1 G:\MSSQL\tempdb.mdf PRIMARY
-- templog 2 G:\MSSQL\templog.ldf NULL

The logical name for each file is contained in the name column. This example uses the default file names of tempdev and templog.

2. Use the ALTER DATABASE statement, specifying the logical file name as follows:

use master
go
Alter database tempdb modify file (name = tempdev, filename = ' E:\Sqldata\tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = ' E:\Sqldata\templog.ldf')
go

You should receive the following messages that confirm the change:

Message 1

File 'tempdev' modified in sysaltfiles. Delete old file after restarting SQL Server.

Message 2

File 'templog' modified in sysaltfiles. Delete old file after restarting SQL Server.

3. Using sp_helpfile in tempdb will not confirm these changes until you restart SQL Server.

4. Stop and then restart SQL Server.


Here are some helpful links;

Moving System Databases: http://support.microsoft.com/kb/224071
Using DAC: http://msdn.microsoft.com/en-us/library/ms178068.aspx
SQL Server Startup Options: http://msdn.microsoft.com/en-us/library/ms190737.aspx