Advertisements
Home > DBA Stuff, SQL Tips > SQL DBA – Moved MASTER database by ALTER DATABASE statement? here’s the solution

SQL DBA – Moved MASTER database by ALTER DATABASE statement? here’s the solution


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.

Advertisements
  1. Frederic
    January 24, 2013 at 8:43 pm

    Hi
    i encounter the same problem (alter database master) but with the Sql 2008 R2 version, in configuration tools, there is no more “startup parameters” tab. There is an advanced tab with options startup parameters but even with the -d -l option the result is the same and the error pursue.
    I tried to start sql with NET START MSSQLSERVER /f /T3608 without success … if you have an idea of a way to make it works 🙂
    Thanks a lot
    Frederic.

    • Frederic
      January 24, 2013 at 9:49 pm

      Ok i found the problem in the error file (in the log folder) i needed to add a Sql group account rights (full access) on the new folder and that works.
      Thanks
      Frederic.

  1. January 18, 2016 at 6:41 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: