SQL with Manoj

Have you also moved your MASTER DATABASE by using “ALTER DATABASE” statement just like you did for other system databases like MSDB, MODEL, TEMPDB & other databases?

If YES, then you are same nerdy DBA like me.

For quite some time I was observing very bad performance in one of our DEV servers. So today I thought to check it, I found that the C: Drive is almost full. Don’t know why do the DBA guys installed SQL Server on C: drive and put all system databases here to make it even worse. To get some room on C: drive I thought to move all four system databases (i.e. MASTER, MODEL, MSDB & TEMPDB) to another drive.

So, I created normal “ALTER DATABASE” scripts for all the 4 databases and executed them, as follows:

ALTER DATABASE master MODIFY FILE (
	NAME = 'tempdev' ,
	FILENAME = 'D:\MSSQL10_50\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\tempdb.mdf' )
ALTER DATABASE master MODIFY FILE (
	NAME = 'templog' ,
	FILENAME = 'D:\MSSQL10_50\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\templog.ldf' )

ALTER DATABASE master MODIFY FILE (
	NAME = 'modeldev' ,
	FILENAME = 'D:\MSSQL10_50\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\modeldev.mdf' )
ALTER DATABASE master MODIFY FILE (
	NAME = 'modellog' ,
	FILENAME = 'D:\MSSQL10_50\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\modellog.ldf' )

ALTER DATABASE master MODIFY FILE (
	NAME = 'MSDBData' ,
	FILENAME = 'D:\MSSQL10_50\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MSDBData.mdf' )
ALTER DATABASE master MODIFY FILE (
	NAME = 'MSDBLog' ,
	FILENAME = 'D:\MSSQL10_50\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MSDBLog.ldf' )

-- !!!! BEWARE DON'T RUN THIS !!!!
ALTER DATABASE master MODIFY FILE (
	NAME = 'master' ,
	FILENAME = 'D:\MSSQL10_50\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf' )
ALTER DATABASE master MODIFY FILE (
	NAME = 'mastlog' ,
	FILENAME = 'D:\MSSQL10_50\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf' )
-- !!!! BEWARE DON'T RUN THIS !!!!

-> Happily I Stopped the SQL Server service.

-> Now, to move the databases physically I moved the MDF & LDF files to the new location I used in “ALTER DATABASE” statements above.

-> After moving DB files I tried to Start the “SQL Server” service, but the service didn’t start and I was getting following error:
“The SQL Server service on [SERVER_NAME] started and then stopped. blah blah blah…”

I immediately thought that I’ve done something wrong, checked MS BOL, and found that I should not have moved the MASTER database by using “ALTER DATABASE” statement.

–> WORKAROUND:
Now when the wrong scripts are executed and there is no way to undo it, there should be some way to fix it.

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.

-> Check the new location by issuing either of following 2 SQL queries:

select * from sys.sysdatabases
-- OR --
select * from sys.master_files

Not only this is a workaround to fix this issue, but you can also use this tool to move your MASTER database to a different Drive.